PostgreSQL查询JSONB列数组元素满足多条件的表记录方法

问题背景 #

你在PostgreSQL数据库中有一张名为reports的表，表内存在一个jsonb类型的列data，测试数据如下：

• report.id = 1对应的data字段值：

[
    {
        "Product": [
            {
                "productIDs": [
                    "ABC1",
                    "ABC2"
                ],
                "groupID": "Food123"
            },
            {
                "productIDs": [
                    "EFG1"
                ],
                "groupID": "Electronic123"
            }
        ],
        "Package": [
            {
                "groupID": "Electronic123"
            }
        ],
        "type": "Produce"
    },
    {
        "Product": [
            {
                "productIDs": [
                    "ABC1",
                    "ABC2"
                ],
                "groupID": "Clothes123"
            }
        ],
        "Package": [
            {
                "groupID": "Food123"
            }
        ],
        "type": "Wearables"
    }

]

• report.id = 2对应的data字段值：

[
    {
        "Product": [
            {
                "productIDs": [
                    "XYZ1",
                    "XYZ2"
                ],
                "groupID": "Food123"
            }
        ],
        "Package": [],
        "type": "Wearable"
    },
    {
        "Product": [
            {
                "productIDs": [
                    "ABC1",
                    "ABC2"
                ],
                "groupID": "Clothes123"
            }
        ],
        "Package": [
            {
                "groupID": "Food123"
            }
        ],
        "type": "Wearables"
    }
]

查询需求 #

需要查询reports表中所有符合以下条件的条目：data列的数组中至少存在一个元素同时满足两个条件：

1. 该元素的type字段值为Produce
2. 该元素下Product数组中的任意一个元素的groupID字段值以Food开头

按照示例数据，只有id为1的记录符合要求。你目前已经实现了筛选type为Produce的SQL：

select * from reports r, jsonb_to_recordset(r.data) as items(type text) where items.type like 'Produce';

需要补充groupID的前缀匹配条件，实现完整查询逻辑。 #

完整实现方案 #

方案1：多层jsonb展开查询 #

SELECT DISTINCT r.*
FROM reports r,
     jsonb_to_recordset(r.data) AS items(type text, Product jsonb),
     jsonb_to_recordset(items.Product) AS products(groupID text)
WHERE items.type = 'Produce'
  AND products.groupID LIKE 'Food%';

说明：

• 在原有逻辑基础上，额外展开每个条目下的Product数组，拿到groupID做前缀匹配
• 加DISTINCT是避免同一条记录匹配到多个符合条件的嵌套元素时，返回重复结果

方案2：EXISTS子查询（性能更优） #

SELECT * FROM reports r
WHERE EXISTS (
    SELECT 1 FROM jsonb_to_recordset(r.data) AS items(type text, Product jsonb)
    WHERE items.type = 'Produce'
      AND EXISTS (
          SELECT 1 FROM jsonb_array_elements(items.Product) AS prod
          WHERE prod->>'groupID' LIKE 'Food%'
      )
);

说明：

• 用EXISTS判断是否存在符合条件的元素，不会产生重复记录，不需要额外去重
• 数据量较大时，性能比方案1更好

内容的提问来源于stack exchange，提问作者J Developer