postgresql 查询jsonb，如何从jsonb中按值选择？[duplicate]

3htmauhk 于 2022-11-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(164)

此问题在此处已有答案：

JSONB in where clause postgres（2个答案）
how to query array of nested json in postgresql（2个答案）
3天前关闭。
我有一个包含jsonb products列的postrgresql表：

| 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列中的pretty 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的所有行？

postgresql

来源：https://stackoverflow.com/questions/74573443/query-for-jsonb-how-select-by-value-from-jsonb

1条答案

按热度按时间

hvvq6cgz1#

可以使用包含运算符@>

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

赞(0）回复(0）举报 2022-11-29

我来回答

postgresql 查询jsonb，如何从jsonb中按值选择？[duplicate]

1条答案

相关问题

热门标签

最新问答