查询JSONB对象中的数组元素

Question

在一个名为jsonb的表中，有一个名为data的reports列。下面是report.id = 1的样子

[
    {
        "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的样子：

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

我试图获得reports表中所有条目的列表，其中至少有一个data列的元素具有以下内容：type = Produce，Product数组的任何元素或Product数组的任何元素都以Food开头

因此，从上面的示例中，这个查询将只返回第一个索引，因为

1. 类型= Produce
2. groupID以Food开头，用于Product数组的第一个元素

第二个索引将被过滤掉，因为类型不是Produce。

我不知道如何查询来做和查询groupID。下面是我尝试获取Produce类型的所有条目的内容

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

Answer 1

样本结构和结果：[医]小提琴

select r.*
from reports r
         cross join jsonb_array_elements(r.data) l1
         cross join jsonb_array_elements(l1.value -> 'Product') l2
where l1 ->> 'type' = 'Produce'
and l2.value ->> 'groupID' ~ '^Food';