时间戳

首页 > 解决方案 > 在 JSONB 字段中循环 JSON 数组

sql - 在 JSONB 字段中循环 JSON 数组

问题描述

我想遍历列并获取相关对象的JSONB某些值（price、、discount_price和）到我的过滤器。但我得到这个错误：currencyJSON

syntax error at or near "FOR"

parts列的值为JSONB：

[
    {
        "item_tags": ["black", "optional"],
        "name": "Keyboard",
        "price": 50,
        "currency": "USD",
        "discount_price": 40
    },
    {
        "item_tags": ["white", "optional"],
        "name": "Mouse",
        "price": 40,
        "currency": "USD",
        "discount_price": 30
    }
]

我的查询（$1是用户输入。可以是“可选”或“必需”）：

SELECT
    id,
    title,
    FOR element IN SELECT * FROM jsonb_array_elements(parts)
        LOOP
            CASE
                WHEN element->'item_tags' @> $1
                    THEN SELECT element->>'discount_price' AS price, element->>'currency' AS currency
                ELSE SELECT element->>'price' AS price, element->>'currency' AS currency
            END
        END LOOP
FROM items;

$1如果等于“可选”，这是我想要得到的输出：

{
    "id": 1,
    "title": "example title",
    "parts": [
        {
            "name": "Keyboard",
            "discount_price": 40,
            "currency": "USD"
        },
        {
            "name": "Mouse",
            "discount_price": 30,
            "currency": "USD"
        }
    ]
}

非常感谢任何帮助。我遵循官方文档，但它不适合初学者。我使用 PostgreSQL 13。

标签： sqlpostgresqljsonbpostgresql-13

解决方案

您需要取消嵌套数组，过滤掉不需要的部分，删除不需要的键，然后将更改的部分聚合回 JSON 数组。

这可以使用标量子查询来完成：

select id, title, 
       (select jsonb_agg(x.part - 'item_tags') 
        from jsonb_array_elements(i.parts) as x(part)
        where (x.part -> 'item_tags') ? 'optional')
from items i;

该表达式从 JSON 对象x.part - 'item_tags'中删除键。运算符测试数组是否包含右侧的字符串item_tags。然后将这些 JSON 值聚合回一个数组。?item_tagsjsonb_agg()

'optional'您可以在字符串的位置传递您的参数。

推荐阅读