json - 更新 postgres json 中数组中的每个值

Question

在我的 postgres 数据库中，我有看起来类似于此的 json:

{
    "myArray": [
        {
            "myValue": 1
        },
        {
            "myValue": 2
        },
        {
            "myValue": 3
        }
    ]
}

现在我想将 myValue 重命名为 otherValue。我不能确定数组的长度!最好我想使用类似 set_jsonb 的通配符作为数组索引，但这似乎不受支持。那么最好的解决方案是什么？

Answer 1

您必须分解整个 jsonb 对象，修改单个元素并重新构建对象。

自定义函数会有帮助:

create or replace function jsonb_change_keys_in_array(arr jsonb, old_key text, new_key text)
returns jsonb language sql as $$
    select jsonb_agg(case 
        when value->old_key is null then value
        else value- old_key || jsonb_build_object(new_key, value->old_key) 
        end)
    from jsonb_array_elements(arr)
$$;

使用:

with my_table (id, data) as (
values(1, 
'{
    "myArray": [
        {
            "myValue": 1
        },
        {
            "myValue": 2
        },
        {
            "myValue": 3
        }
    ]
}'::jsonb)
)

select 
    id, 
    jsonb_build_object(
        'myArray',
        jsonb_change_keys_in_array(data->'myArray', 'myValue', 'otherValue')
        )
from my_table;

 id |                           jsonb_build_object                           
----+------------------------------------------------------------------------
  1 | {"myArray": [{"otherValue": 1}, {"otherValue": 2}, {"otherValue": 3}]}
(1 row)