Request array elements inside JSON type

Question

I am trying to check json type in PostgreSQL 9.3.
I have a json column named data in a table called reports . JSON looks something like this:

 { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } 

I would like to query a table for all reports that match the value of 'src' in the array 'objects'. For example, is it possible to query the database for all reports matching 'src' = 'foo.png' ? I have successfully written a query that may match "background" :

 SELECT data AS data FROM reports where data->>'background' = 'background.png' 

But since "objects" has an array of values, I cannot write something that works. Is it possible to query the database for all reports matching 'src' = 'foo.png' ? I looked through these sources, but still can not understand:

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• How to execute a query using fields inside a new PostgreSQL JSON data type?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

I also tried similar things, but to no avail:

 SELECT json_array_elements(data->'objects') AS data from reports WHERE data->>'src' = 'foo.png'; 

I am not an expert on SQL, so I do not know what I am doing wrong.

Answer 1

json in Postgres 9.3 +

Destroy the JSON array with the json_array_elements() function in the side join in the FROM and test its elements:

 WITH reports(data) AS ( VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}] , "background":"background.png"}'::json) ) SELECT * FROM reports r, json_array_elements(r.data#>'{objects}') obj WHERE obj->>'src' = 'foo.png'; 

CTE ( WITH query) simply replaces the reports table.
Or, equivalently for only one level of nesting:

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

->> , -> and #> described in the manual.

Both queries use an implicit JOIN LATERAL .

SQL Fiddle

Close answer:

• Query for an array element in a JSON column

jsonb in Postgres 9.4 +

Use equivalent jsonb_array_elements() .

Better , use the new "contains" @> operator (best when combined with the corresponding GIN index in the data->'objects' expression):

 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 objects key contains a JSON array, we need to match the structure in terms of the search and wrap the array element in square brackets. Leave the array brackets when looking for a simple entry.

Detailed explanation and additional parameters:

• Pointer to search for an item in a JSON array

Answer 2

Create a table with json type column

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

Now let's insert the 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 retrieve the data

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

You may have noticed that the results come with quotation marks (") and brackets ([])

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

Now to retrieve only 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'; 

Answer 3

select data-> 'objects' → 0-> 'src' as SRC from the table, where data-> 'objects' → 0-> 'src' = 'foo.png'