How to query JSONB, beginner sheet cheat

Raphaël Huchet
Mar 30, 2017 · 3 min read

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 :

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.

HackerNoon.com

how hackers start their afternoons.

Raphaël Huchet

Written by

Laravel enthusiast & Rust artisan.

HackerNoon.com

how hackers start their afternoons.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade