查询jsonb.如何选择值从jsonb?

Question

我有一个jsonbproducts列的postgresql表:

| 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"}]                               |

来自products列的json示例:

[
{
"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"
}
]

如何选择"@type"="@game"且"extId"=da32af17-fa03-4a62-bd04-f026d04d16e9的所有行?

Answer 1

可以使用包含运算符@>

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