PostgreSQL多键值嵌套jsonb查询

Question

我将从JSONB数据类型入手，希望有人能够帮帮我。

我有一个表格（properties），其中包含两列（id为主键，data为jsonb）。 data的结构如下：

    {
        "ProductType": "ABC",
        "ProductName": "XYZ",
        "attributes": [
            {
            "name": "Color",
            "type": "STRING",
            "value": "Silver"
            },
            {
            "name": "Case",
            "type": "STRING",
            "value": "Shells"
            },
            ...
        ]
    }

我想获取所有属性值为特定值的行，例如返回所有Case = 'Shells' 和/或 Color = 'Red'的行。

我尝试了以下方法，但是我无法应用两个条件，比如 Case ='Shells' 和 Color = 'Silver'。 当一个单独属性的名称和值与条件匹配时，我能够获取行，但我无法弄清如何使其适用于多个属性。

---

编辑1： 我能够使用以下查询获得结果：

WITH properties AS (
    select *
    from (
        values 
        (1, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Silver"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb), 
        (2, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Red"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb)
    ) s(id, data)
)
select 
    *
from (
    SELECT 
        id,
        jsonb_object_agg(attr ->> 'name', attr -> 'value') as aggr
    FROM properties m,
       jsonb_array_elements(data -> 'attributes') as attr
    GROUP BY id
    ) a
where aggr ->> 'Color' = 'Red' and aggr ->> 'Case' LIKE 'Sh%'

我可能会有数百万条这样的记录，所以我现在唯一的担忧是它是否高效，如果不是，是否有更好的方法？

- cleatedheels

Answer 1

逐步演示：db<>fiddle

SELECT 
    id
FROM properties m,
   jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
HAVING jsonb_object_agg(attr ->> 'name', attr -> 'value') @> '{"Color":"Silver", "Case":"Shells"}'::jsonb

问题在于jsonb_array_elements()会将相关的值移动到不同的记录中，然而这个调用是必要的以获取这些值。因此，在读取这些值后你需要重新聚合它们，从而可以按相关方式进行检查。

可以通过使用jsonb_object_agg()聚合函数来实现这一点。关键在于我们创建一个带有"name":"value"属性的对象。这样，我们就可以使用@>运算符轻松地检查JSON对象中是否存在所有所需的属性。

---

关于“编辑1”

示例：db<>fiddle

你可以这样做：

SELECT
    *
FROM ( 
    SELECT 
        id,
        jsonb_object_agg(attr ->> 'name', attr -> 'value') as obj
    FROM properties m,
       jsonb_array_elements(data -> 'attributes') as attr
    GROUP BY id
) s
WHERE obj ->> 'Color' = 'Silver'
    AND obj ->> 'Case' LIKE 'Sh%'

1. 按照上述方式为所有的JSON创建新的JSON结构。
2. 之后对此结果进行筛选。

或者您可以在HAVING子句中尽可能多地使用jsonb_object_agg()。我猜您需要检查哪种方式在您的情况下更有效率：

SELECT 
    id
FROM properties m,
   jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
HAVING 
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Color' = 'Silver'
   AND
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Case' LIKE 'Sh%'

- S-Man

---

能否对整数值应用比较运算符例如 '>=' 或者逻辑运算符例如 'LIKE'， 'EXISTS'？我是新来的，但我不会让任何人空等，我保证 :D - cleatedheels

我不太确定你想要实现什么。请编辑你的问题并展示你的用例。 - S-Man

更新。对于效率有什么想法吗？ - cleatedheels

扩展了答案 - S-Man