Query Postgres JSON type value with arrays of json

Vote count:

0

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" is an array, 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.

asked 1 min ago

pacothelovetaco

24