current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
2

I'm trying to test out Postgresql's JSON type. 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"}

What I would like to do is query the table for all reports that matches 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 a SQL expert so I don't know what I am doing wrong.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Unnest the JSON array with json_array_elements() in a subquery, so you can test its elements:

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

The CTE (WITH query) just fakes a table t.
->> and #> operators are explained in the manual.

This is making use of an implicit LATERAL JOIN in Postgres 9.3+.

SQL Fiddle.

Closely related answer:
Query for element of array in JSON column

share|improve this answer
2  
That's it! The #> character was the secret sauce I was missing. I experimented with it but I didn't understand it fully until I saw your example. I even got everything to work within my Rails project. You are killing it with all of these Postgres/Json questions. Kudos to you! –  pacothelovetaco Mar 30 at 1:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.