I'm trying to test out the json type in PostgreSQL 9.3.
I have a json column called data in a table called reports. The JSON looks something like this:
{
"objects": [
{"src":"foo.png"},
{"src":"bar.png"}
],
"background":"background.png"
}
I would like to query the table for all reports that match the 'src' value in the 'objects' array. For example, is it possible to query the DB for all reports that match 'src' = 'foo.png'? I successfully wrote a query that can match the "background":
SELECT data AS data FROM reports where data->>'background' = 'background.png'
But since "objects" has an array of values, I can't seem to write something that works. Is it possible to query the DB for all reports that match 'src' = 'foo.png'? I've looked through these sources but still can't get it:
I've also tried things like this but to no avail:
SELECT json_array_elements(data->'objects') AS data from reports
WHERE data->>'src' = 'foo.png';
I'm not an SQL expert, so I don't know what I am doing wrong.
This question is related to
sql
json
postgresql
jsonb
lateral
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:
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';The CTE (WITH query) just substitutes for a table reports.
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:
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';
Source: Stackoverflow.com