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;
->>
: 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;
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;
The same result can be achieved by using the
->
operator multiple times:SELECT data -> 'user' -> 0 -> 'name' AS username FROM my_table;
#>>
: 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;
This result can also be achieved by combining
->
and->>
:SELECT data -> 'user' -> 0 ->> 'name' AS username FROM my_table;
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": "john.doe@example.com"}'),
('{"name": "Tim", "email": "tim@example.com"}')
Using jsonb_each
like this:
SELECT jsonb_each(data) FROM users;
Returns:
jsonb_each
----------
(name,"""John""")
(email,"""john.doe@example.com""")
(name,"""Tim""")
(email,"""tim@example.com""")
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.