CODEWITHSUNDEEP
postgresql
Nicholas Porter
Nicholas Porter

Reputation: 2951

PostgreSQL trying to select json array value

I have the following json array stored in a column in my postgres db. I am trying to write a query to get all rows by country name.

The query will execute but cannot get it to return any values.

The column name is sources and is of json array type.

Example value

[{
    "name": "Albania",
    "type": "domain",
    "order": 2
},{
    "name": "Samoa",
    "type": "domain",
    "order": 3
},{
    "name": "United States",
    "type": "domain",
    "order": 4
}]

Here is the query I'm trying to run:

 SELECT * from my_table,
        json_array_elements(sources->'name') elem
            where elem->>'name' = 'Samoa';

Upvotes: 0

Views: 56

Answers (2)

jjanes
jjanes

Reputation: 44167

You almost had it correct. The problem is that you specified 'name' one time too often. sources doesn't have a top level key named 'name' (because it is an array, it doesn't have string keys), so that gives 'null', which can't be unnested. So:

SELECT * from my_table,
        json_array_elements(sources) elem
            where elem->>'name' = 'Samoa';

However, if you don't need the specific element which contains the match as a separate column, then you can use the containment operator to get rid of the join altogether.

select * from my_table where sources::jsonb @> '[{"name":"Samoa"}]';

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Try to use EXISTS and a correlated subquery.

SELECT *
       FROM my_table t
       WHERE EXISTS (SELECT *
                            FROM json_array_elements(t.sources) jae (e)
                            WHERE jae.e->>'name' = 'Samoa');

Upvotes: 1

Related Questions