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