How to query JSONB, beginner sheet cheat

Let’s say we have to query a user table with a metadata JSONB column on a PostgreSQL 9.5+ database.

1. Select items by the value of a first level attribute (#1 way)

You can query with the @> operator on metadata. This operator can compare partial JSON strings against a JSONB column. It’s the containment operator. For this case you may need to add a GIN index on metadata column.

SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; 

2. Select items by the value of a first level attribute (#2 way)

The ->> operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on metadata->>'country'.

SELECT * FROM users WHERE metadata->>'country' = 'Peru';

3. Select item attribute value

Once again, the ->> operator gets a JSON object field as text. Just use directly it in the SELECT.

SELECT metadata->>'country' FROM users;

4. Select only items where a particular attribute is present

You can use the ->> operator with the classic operator you use on text: =, <>, IS NULL, etc. Do not forget to index metadata->>'country' with a B-tree index.

SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;

5. Select items by the value of a nested attribute

You can use both @> or ->>, just like for first level attribute. Add an index according to your choice.

SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";
SELECT * 
  FROM users 
  WHERE metadata @> '{"company":{"name": "Mozilla"}}';

6. Select items by the value of an attribute in an array

Remembering @> operator checks containment in a JSONB column, you can query on an array like {"x": ["a", "b", "c"]"} by just passing {"x":["a"]} to the WHERE clause:

SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';

7. IN operator on attributes

Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values.

SELECT * FROM users 
  WHERE metadata->>'countries' IN ('Chad', 'Japan');

8. Insert a whole object

Use UPDATE ... SET as usual and pass the whole object as JSON.

UPDATE users SET metadata = '{"country": "India"}';

9. Update or insert an attribute

Use the || operator to concatenate the actual data with the new data. It will update or insert the value.

UPDATE users SET metadata = metadata || '{"country": "Egypt"}';

10. Removing an attribute

The operator - removes a key from an object.

UPDATE users SET metadata = metadata - 'country';

Final note

Querying on JSONB objects is almost as simple as classic SQL queries. I posted only a few examples here, about what seems the most common use cases to me. It’s a note for me, I hope it could help other people too. You may dig in PostgreSQL docs, which has many more examples and more precise explanations :

• postgresql.org/docs/9.6/functions-json.html
• postgresql.org/docs/9.6/datatype-json.html

Feel free to comment with advices, feedback and criticism. I would be really happy to learn more.

Thanks to Emilien Schneider (once again) for his review.

Raphaël Huchet Mar 30, 2017