PostgreSQL JsonB query for object in JSON array based on object attributes

Question

So I have seen a few other responses to this question on StackOverflow already, but none of them have worked for me.

{
    "data": {
        "list": [
            {"id": "2ac5bf6f-bc4a-49e8-8f9f-bc518a839981", "type": "type1"},
            {"id": "d15ac090-11ce-4c0c-a05d-d4238f01e8b0", "type": "type3"},
            {"id": "b98958fa-87c4-4dcc-aa84-beaf2b32c5c0", "type": "type1"},
            {"id": "854f4d2a-f37c-42cb-9a1f-17a15454a314", "type": "type2"},
            {"id": "555816da-4547-4a82-9e7e-1e92515bd82b", "type": "type2"},
            {"id": "0f7f4ced-61c2-45da-b15c-0e12058f66a7", "type": "type4"}

        ]
    }
}

This Json is stored in a field called "questions", now I want to query this table for an object with a certain id in the list. So say I have the id 555816da-4547-4a82-9e7e-1e92515bd82b, I would like to return

{"id": "555816da-4547-4a82-9e7e-1e92515bd82b", "type": "type2"}

The solutions to this I have seen on the internet (primarily here), that have not worked are here:

SELECT questions->'data'->'list' 
FROM assignments 
WHERE id='81asd6230-126d-4bc8-9745-c4333338115c' 
AND questions->'data'->'list' @> '[{"id":"854f4d2a-f37c-42cb-9a1f-17a15454a314"}]';

I have seen this solution on multiple different responses but it doesn't narrow down the array at all, it returns the full thing every time. The first id in the where clause is the id of the specific assignment object that I want, its mostly irrelevant here.

SELECT questions->'data'->'list' 
FROM assignments 
WHERE id='81asd6230-126d-4bc8-9745-c4333338115c' 
AND questions->'data'->'list' @> '[{"id":"854f4d2a-f37c-42cb-9a1f-17a15454a314"}]';

This returns nothing.

Does anyone have an idea on how to do this easily?

score 5 · Accepted Answer · answered Sep 09 '15 at 21:17

You can use function jsonb_array_elements(jsonb) to select all elements of a json array:

select jsonb_array_elements(questions->'data'->'list') elem
from assignments
where id='81asd6230-126d-4bc8-9745-c4333338115c'

                              elem
-----------------------------------------------------------------
 {"id": "2ac5bf6f-bc4a-49e8-8f9f-bc518a839981", "type": "type1"}
 {"id": "d15ac090-11ce-4c0c-a05d-d4238f01e8b0", "type": "type3"}
 {"id": "b98958fa-87c4-4dcc-aa84-beaf2b32c5c0", "type": "type1"}
 {"id": "854f4d2a-f37c-42cb-9a1f-17a15454a314", "type": "type2"}
 {"id": "555816da-4547-4a82-9e7e-1e92515bd82b", "type": "type2"}
 {"id": "0f7f4ced-61c2-45da-b15c-0e12058f66a7", "type": "type4"}
(6 rows)

Use the above query if you want to select an element with specific id:

select elem
from (
    select jsonb_array_elements(questions->'data'->'list') elem
    from assignments
    where id='81asd6230-126d-4bc8-9745-c4333338115c'
    ) sub
where elem->>'id' = '854f4d2a-f37c-42cb-9a1f-17a15454a314'

                              elem
-----------------------------------------------------------------
 {"id": "854f4d2a-f37c-42cb-9a1f-17a15454a314", "type": "type2"}
(1 row)

Cool, this works, how would I do an update in a similar situation, where I have the id of a question and want to update its type attribute? — user1032369, Sep 10 '15 at 18:38
In Postgres 9.4 there is no features to modify jsonb elements. You can update a jsonb value as a whole. See related answers: [How do I modify fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) and [Update certain array elements of a json array in PostgreSQL 9.4](http://stackoverflow.com/questions/27288768/update-certain-array-elements-of-a-json-array-in-postgresql-9-4). — klin, Sep 10 '15 at 18:59
In [Postgres 9.5](http://www.postgresql.org/docs/9.5/static/functions-json.html) you will be able to use the function `jsonb_set()`. — klin, Sep 10 '15 at 18:59

PostgreSQL JsonB query for object in JSON array based on object attributes

1 Answers1