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.

klin · Accepted Answer · 2021-07-01T10:01:38.170

25

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)

edited Jul 01 '21 at 10:01

answered Aug 17 '17 at 19:52

klin

112,967
15
204
232

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 Aug 17 '17 at 20:06
You are awesome B-) – Armando Perea Aug 17 '17 at 20:31
2

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. – Abrham Smith Nov 17 '20 at 19:27

postgresql json array query

1 Answers1