PostgreSQL jsonb ? operator alternative

May 6, 2015

PostgreSQL 9.4 has a great new jsonb data type. It functions very similarly to the old json type with some great new functions and operators. One of the operators is the question mark (?). This operator is used to query for the existence of a key. i.e. if I had two fields:

Table: people

id | data
---+---------------------------
1  | {"bob":true, "tom": false}
2  | {"gary":false}

We could do a query like below to select the row containing the key “bob”

SELECT * FROM people WHERE data ? "bob"

The Problem

The problem with this operator is that many ORM libraries i.e. gorm for Golang use the question mark for query values. i.e.

1 // Get the first matched record
2 db.Where("name = ?", "jinzhu").First(&user)

Unfortunately, most of these libraries such as the one above don’t yet include ways to escape the question mark.

The Solution

One of the other operators is the Get JSON object field by key operator: ->. We can use this operator, along with a logic test, to accomplish the same thing. i.e.

SELECT * FROM people WHERE (data -> "bob") IS NOT NULL

Using this query string should circumvent the question mark replacement issue in affected ORMs

Tags: golang postgresql