Query for jsonb. How select by value from jsonb?

I have a postrgresql table with jsonb products column:

| products                                            |
| --------------------------------------------------- |
| [{"id": "eaaca8bc-c8a0-45f7-9698-d4fc701d2e5a", "@type": "@game", "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"}, {"id": "5fc5de21-9cb7-4bd3-a723-7936bfef7cde", "@type": "@book", "extId": "c945f005-2d37-491c-8ba9-9da2709a3aab"}, {"id": "892fe85c-d7d6-4815-8dec-1720b644205a", "@type": "@sport", "extId": "c252dcba-2a14-4e75-90db-29ccac2499d2"}]  |
| [{"id": "gh6d86ls-wj8o-39r4-2694-1720b644205a", "@type": "@game", "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"}]      |
|                                                     |
| [{"id": "892fe85c-d7d6-4815-8dec-1720b644205a", "@type": "@sport", "extId": "c252dcba-2a14-4e75-90db-29ccac2499d2"}]                               |

Example of pretty json from products column:

[
    {
        "id": "eaaca8bc-c8a0-45f7-9698-d4fc701d2e5a",
        "@type": "@game",
        "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"
    },
    {
        "id": "5fc5de21-9cb7-4bd3-a723-7936bfef7cde",
        "@type": "@book",
        "extId": "c945f005-2d37-491c-8ba9-9da2709a3aab"
    }
]

How can I select all rows, where "@type" equals "@game" and "extId" equals da32af17-fa03-4a62-bd04-f026d04d16e9?

>Solution :

You can use the contains operator @>

select *
from the_table
where products @> '[{"@type": "@game", "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"}]

Leave a Reply