paint-brush
How to query JSONB, beginner sheet cheatby@rap2h
175,023 reads
175,023 reads

How to query JSONB, beginner sheet cheat

by Raphaël HUCHETMarch 30th, 2017
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

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.

Companies Mentioned

Mention Thumbnail
Mention Thumbnail
featured image - How to query JSONB, beginner sheet cheat
Raphaël HUCHET HackerNoon profile picture

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: 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.