Querying a JSON array of objects in Postgres

Question

I have a postgres db with a json data field. The json I have is an array of objects:

[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]

I'm trying to return values for a specific key in a JSON array, so in the above example I'd like to return the values for name.

When I use the following query I just get a NULL value returned:

SELECT data->'name' AS name FROM json_test

Im assuming this is because it's an array of objects? Is it possible to directly address the name key?

Ultimately what I need to do is to return a count of every unique name, is this possible?

Thanks!

Answer 1

you have to unnest the array of json-objects first using the function (json_array_elements or jsonb_array_elements if you have jsonb data type), then you can access the values by specifying the key.

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  y.x->'name' "name"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

-- outputs:
name
--------------
"Mickey Mouse"
"Donald Duck"

To get a count of unique names, its a similar query to the above, except the count distinct aggregate function is applied to y.x->>name

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  COUNT( DISTINCT y.x->>'name') distinct_names
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

It is necessary to use ->> instead of -> as the former (->>) casts the extracted value as text, which supports equality comparison (needed for distinct count), whereas the latter (->) extracts the value as json, which does not support equality comparison.

Alternatively, convert the json as jsonb and use jsonb_array_elements. JSONB supports the equality comparison, thus it is possible to use COUNT DISTINCT along with extraction via ->, i.e.

COUNT(DISTINCT (y.x::jsonb)->'name')

---

updated answer for postgresql versions 12+

It is now possible to extract / unnest specific keys from a list of objects using jsonb path queries, so long as the field queried is jsonb and not json.

example:

WITH json_test (col) AS (
  values (jsonb '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT jsonb_path_query(col, '$[*].name') "name"
FROM json_test
-- replaces this original snippet:
-- SELECT
--   y.x->'name' "name"
-- FROM json_test jt, 
-- LATERAL (SELECT json_array_elements(jt.col) x) y

Answer 2

You can use jsonb_array_elements (when using jsonb) or json_array_elements (when using json) to expand the array elements.

For example:

WITH sample_data_array(arr) AS (
    VALUES ('[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]'::jsonb)
)
, sample_data_elements(elem) AS (
    SELECT jsonb_array_elements(arr) FROM sample_data_array
)
SELECT elem->'name' AS extracted_name FROM sample_data_elements;

In this example, sample_data_elements is equivalent to a table with a single jsonb column called elem, with two rows (the two array elements in the initial data).

The result consists of two rows (one jsonb column, or of type text if you used ->>'name' instead):

 extracted_name
----------------
 "Mickey Mouse"
 "Donald Duck"
(2 rows)

You should them be able to group and aggregate as usual to return the count of individual names.

Answer 3

Do like this:

SELECT * FROM json_test WHERE (column_name @> '[{"name": "Mickey Mouse"}]');