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?

klin · Accepted Answer · 2015-09-09 21:17:03Z

6

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)

answered Sep 9, 2015 at 21:17

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1032369 Over a year ago

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?

klin Over a year ago

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? and Update certain array elements of a json array in PostgreSQL 9.4.

klin Over a year ago

In Postgres 9.5 you will be able to use the function jsonb_set().

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related