在Postgres中查询具有数组元素的JSONB

Question

我有一个名为settings的表，其中列program_id(number), client_id(number), filters(Jsonb)和过滤器列的jsonb数据格式如下-

{
"sources": [
{
"conditions": [
{
"value": [
{
"id": 1211,
"name": "ABM BETA INVITE LIST",
"isSelected": true
}
],
"condition": "one of the following"
}
],
"objectType": "SmartLists",
"subscriptionId": 1173,
"integrationType": "mkto"
}
],
"listType": "All Accounts",
"programId": 30203,
"noOfAccounts": null,
"expiryDuration": 0,
"subscriptionId": null,
"updateFrequency": null
}

我现在想检索表settings中的所有记录其中filters.sources[0].integrationType = 'mkto'。我已经尝试过这个查询，但给我错误的集合返回函数不允许在WHERE-

select * from settings where (jsonb_array_elements(filters -> 'sources') ->> 'integrationType' = 'mkto');

Answer 1

我现在想从表设置中检索所有记录，其中filters.sources[0].integrationType = 'mkto'.

使用#>>操作符:

SELECT *
FROM   settings
WHERE  filters #>> '{sources, 0, integrationType}' = 'mkto';

小提琴

filters #>> '{sources, 0, integrationType}'与

相同

filters -> 'sources' -> 0 ->> 'integrationType'
filters['sources'][0]['integrationType'] #>> '{}'  -- for Postgres 14+

但是你真的只想看第一个数组元素吗?