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)

• Armando Perea: Thanks, that worked. I had some bad data in other rows, so I had to choose only a "good" row. Now I have another question: how can I select only the "occupation" for example? Where you say select t.* I only want the "occupation". I tried t.my_json -> 'occupation' and it didn't work. The results show ?column? and null data which it isn't null in my row.
• Armando Perea: You are awesome B-)
• Abrham Smith: I've seen several people try to explain how to do this and you have given the most elegant solution on stack. Thank you sir.