174,168 reads

How to query JSONB, beginner sheet cheat

by Raphaël HUCHETMarch 30th, 2017

Too Long; Didn't Read

Querying on JSONB objects is almost as simple as classic SQL queries. There are parallel variants of these operators for both the json and jsonb types. The field/element/path/path…www.postgresql.org.org is not possible for the. JSON types to be used in Postgresql 9.5+ database. The most common use of the operators is to select items by the value of a first level attribute in an array or by the number of possible values in an object.

featured image - 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

@>

->>

, 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: Documentation: 9.6: JSON Functions and Operators
Note: There are parallel variants of these operators for both the json and jsonb types. The field/element/path…www.postgresql.org PostgreSQL:

Documentation: 9.6: JSON Types
PostgreSQL allows only one character set encoding per database. It is therefore not possible for the JSON types to…www.postgresql.org

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.