时间戳

首页 > 解决方案 > Postgres：如何更改数组中每个元素的 jsonb 值类型？

sql - Postgres：如何更改数组中每个元素的 jsonb 值类型？

问题描述

我在 Postgres 10.12 中有一个名为items的 jsonb 列，如下所示：

{
    "items": [
        {
            "itemQty": 2,
            "itemName": "snake"
        },
        {
            "itemQty": 1,
            "itemName": "x kodiyum"
        }
    ]
}

现在我想将每个数组元素的itemQty类型转换为字符串，以便新值如下所示：

{
    "items": [
        {
            "itemQty": "2",
            "itemName": "snake"
        },
        {
            "itemQty": "1",
            "itemName": "x kodiyum"
        }
    ]
}

我该怎么做呢？我已经浏览了 Postgres jsonb 的文档，但无法弄清楚。

在服务器端，如果有帮助，我将使用 Spring boot 和 Hibernate 以及 com.vladmihalcea.hibernate.type.json (Hibernate Types 52)。

谢谢

标签： sqlarraysjsonpostgresqlsql-update

解决方案

您可以取消嵌套数组，修改元素，然后重新构建它。假设您的表的主键是id，那将是：

select jsonb_build_object(
    'items', jsonb_agg(
        jsonb_build_object(
            'itemQty',  (x.obj ->> 'itemQty')::text,
            'itemName', x.obj ->> 'Name'
        )
    ) 
    )new_items
from mytable t
cross join lateral jsonb_array_elements(t.items -> 'items') as x(obj)
group by id

请注意，::text此处并不真正需要显式转换为，因为->>无论如何都提取文本值：我保留它是因为它使意图更清晰。

如果你想要一个update声明：

update mytable t
set items = (
    select jsonb_build_object(
        'items', jsonb_agg(
            jsonb_build_object(
                'itemQty',  (x.obj ->> 'itemQty')::text,
                'itemName', x.obj ->> 'Name'
            )
        ) 
    )
    from jsonb_array_elements(t.items -> 'items') as x(obj)
)

DB Fiddle 上的演示

推荐阅读