Query for array elements inside JSON type

Question

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:

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• How do I query using fields inside the new PostgreSQL JSON datatype?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

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.

Rainband

Answer 1

jsonb in Postgres 9.4+

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

But you should 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

Koh

@pacothelovetaco: added an update for jsonb / pg 9.4. Aside: for the simple case (1 level of nesting), the -> operator also does the trick for json in pg 9.3. – Koh

@pacothelovetaco, for pg 9.3, '#>' is not the secret sauce, '->' would be just fine for your case as it also returns the json objec. '#>' would be more helpful in nested json path case as it's allows you easily specify the path in the '{}' – Butterflies

@> '[{"src":"foo.png"}]'; work fine in where condition but how to delete particular object like this ? i dont know index of this object . i want to delete by key value . – Teetotalism

@PranaySoni: Please ask the new question as question. Comments are not the place. You can always link to this one for context. – Koh

dear @ErwinBrandstetter, is it possible to find both documents by partial matching? For instance I'd like to get both records something like that '[{"src":".png"}]' – Discommon

@Pyrejkee: Not simply with the @> operator which offers no pattern matching. There is always a way. You might post a new question with all defining details. – Koh

Is adding an index strictly required to do the 9.4 version of the query or is it just a performance optimization (as indexes usually are)? – Putscher

@oligofren: Performance. But it makes a huge difference for big tables. – Koh

This is a really great answer. It helped me out a lot. Thank you. – Swag

Event if 'object' field is empty, i want to query on other fields. How can i achieve this ,as according to your solution we are using 'json_array_elements' in FROM and if it is empty for some field it is not included in result set – Twigg

ERROR: operator does not exist: json @> unknown – Catacaustic

@Oliver But there is an operator for jsonb @> unknown (which resolves to jsonb @> jsonb). – Koh

@ErwinBrandstetter the answer was missing the de-structuring element jsonb behind the where statement, for example WHERE ingredients::jsonb @> '[{"ingredientId":"${ingredientId}"}]'; – Catacaustic

Answer 2

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 as list

    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';

Sponge

I found this useful. Shows how to drill into the array in a jsonb – Eyebrow

Found lots of complex ways, but this is so simple and easy to understand! – Regression

How to retrieve amount of array elements? – Bunkmate