Query for array elements inside JSON type

Question

jsonb in Postgres 9.4+

You can use the same query as below, just with jsonb_array_elements().

But rather use the jsonb "contains" operator @> in combination with a matching GIN index on the expression data->'objects':

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

Since the key objects holds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.

More explanation and options:

• Index for finding an element in a JSON array

json in Postgres 9.3+

Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements:

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

db<>fiddle here
_{Old sqlfiddle}

Or, equivalent for just a single level of nesting:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>, -> and #> operators are explained in the manual.

Both queries use an implicit JOIN LATERAL.

Closely related:

• Query for element of array in JSON column

Create a table with column as type json

CREATE TABLE friends ( id serial primary key, data jsonb);

Now let's insert json data

INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}');
INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}');

Now let's make some queries to fetch data

select data->'name' from friends;
select data->'name' as name, data->'work' as work from friends;

You might have noticed that the results comes with inverted comma( " ) and brackets ([ ])

    name    |            work            
------------+----------------------------
 "Arya"     | ["Improvements", "Office"]
 "Tim Cook" | ["Cook", "ceo", "Play"]
(2 rows)

Now to retrieve only the values just use ->>

select data->>'name' as name, data->'work'->>0 as work from friends;
select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';