postgresql json array query

Question

I tried to query my json array using the example here: How do I query using fields inside the new PostgreSQL JSON datatype?

They use the example:

SELECT *
FROM   json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ]'
  ) AS elem
WHERE elem->>'name' = 'Toby';

But my Json array looks more like this (if using the example):

    {
    "people": [{
            "name": "Toby",
        "occupation": "Software Engineer"
    },
    {
        "name": "Zaphod",
        "occupation": "Galactic President"
    }
    ]
}

But I get an error: ERROR: cannot call json_array_elements on a non-array

Is my Json "array" not really an array? I have to use this Json string because it's contained in a database, so I would have to tell them to fix it if it's not an array. Or, is there another way to query it?

I read documentation but nothing worked, kept getting errors.

Answer 1

The json array has a key people so use my_json->'people' in the function:

with my_table(my_json) as (
values(
'{
    "people": [
        {
            "name": "Toby",
            "occupation": "Software Engineer"
        },
        {
            "name": "Zaphod",
            "occupation": "Galactic President"
        }
    ]
}'::json)
)
select t.*
from my_table t
cross join json_array_elements(my_json->'people') elem
where elem->>'name' = 'Toby';

---

The function json_array_elements() unnests the json array and generates all its elements as rows:

select elem->>'name' as name, elem->>'occupation' as occupation
from my_table
cross join json_array_elements(my_json->'people') elem

  name  |     occupation     
--------+--------------------
 Toby   | Software Engineer
 Zaphod | Galactic President
(2 rows)    

If you are interested in Toby's occupation:

select elem->>'occupation' as occupation
from my_table
cross join json_array_elements(my_json->'people') elem
where elem->>'name' = 'Toby'

    occupation     
-------------------
 Software Engineer
(1 row)