How to Query JSON in PostgreSQL

Accessing a Field

• ->: This operator retrieves the value of a JSON object field or array element by key or index. The result remains in its JSON format.

  SELECT data -> 'name' AS name FROM my_table;

  Copy code
• ->>: Like ->, this operator also fetches a JSON object field or an array element by key or index. However, it returns the result as text.

  SELECT data ->> 'name' AS name FROM my_table;

  Copy code

Querying Nested Fields

To query nested fields, you can use the #> and #>> operators. Or alternatively, use the -> multiple times and/or combine it with ->>.

• #>: Navigates a given path of keys and indexes to fetch a nested JSON value, keeping it in the JSON format.

  SELECT data #> '{user,0,name}' AS username FROM my_table;

  Copy code

  The same result can be achieved by using the -> operator multiple times:

  SELECT data -> 'user' -> 0 -> 'name' AS username FROM my_table;

  Copy code

• #>>: Similar to #>, this operator navigates the given path but returns the nested JSON value as text.

  SELECT data #>> '{user,0,name}' AS username FROM my_table;

  Copy code

  This result can also be achieved by combining -> and ->>:

  SELECT data -> 'user' -> 0 ->> 'name' AS username FROM my_table;

  Copy code

Popular Functions

jsonb_array_elements

The jsonb_array_elements function in PostgreSQL can be handy when you need to break down a JSONB array into a set of JSONB values.

Suppose we have the following data in our users table:

INSERT INTO users(data) VALUES 
('{"name": "John", "hobbies": ["reading", "cycling", "swimming"]}');

Now, if you want to list all the hobbies for John (i.e., transform the JSON array into individual rows), you can use the jsonb_array_elements function:

SELECT jsonb_array_elements(data->'hobbies') AS hobby
FROM users 
WHERE data->>'name' = 'John';

The result will be:

  hobby
----------
"reading"
"cycling"
"swimming"

jsonb_array_elements_text

jsonb_array_elements_text works mostly the same as jsonb_array_elements. The difference is that jsonb_array_elements_text returns text values, whereas jsonb_array_elements returns JSONB.

Taking the previous example, jsonb_array_elements_text would return:

  hobby
----------
reading
cycling
swimming

jsonb_each

jsonb_each converts a JSONB object into a set of key-value pairs.k

Imagine now we have this data in our users table:

INSERT INTO users(data) VALUES 
('{"name": "John", "email": "[email protected]"}'),
('{"name": "Tim", "email": "[email protected]"}')

Using jsonb_each like this:

SELECT jsonb_each(data) FROM users;

Returns:

jsonb_each
----------
(name,"""John""")
(email,"""[email protected]""")
(name,"""Tim""")
(email,"""[email protected]""")

And lots more…

Of course there are many more functions available to work with JSON in PostgreSQL. See them all at https://www.postgresql.org/docs/current/functions-json.html.