Query for array elements inside JSON type
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 reportsUSING 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:
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, objFROM reports r, json_array_elements(r.data#>'{objects}') objWHERE obj->>'src' = 'foo.png';
Or, equivalent for just a single level of nesting:
SELECT *FROM reports r, json_array_elements(r.data->'objects') objWHERE obj->>'src' = 'foo.png';
->>
, ->
and #>
operators are explained in the manual.
Both queries use an implicit JOIN LATERAL
.
Closely related:
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';