Json请求:
INSERT INTO test.demotbl (data) VALUES ('{ "x1": "Americas", "x2": "West", "x3": [{ "x_id": "sam" }], "x4": { "a1": true, "a2": false, "a3": [ "xx", "xx" ], "a4": [ "Josh" ], "y1": [{ "id": "RW", "z2": true, "z3": "USER" }, { "id": "RO", "z2": false, "z3": "SELECT" } ] } }'::jsonb)我想基于id"id":"RO"更新归档的z4.
I want to update the filed z4 based on id "id": "RO".
当我需要更新以下查询中使用的z4字段时,我在这里有类似的用例:
I had similar use case here when i needed to update the z4 field used below query:
with zd as (select ('{x4,y1,'||index-1||',z4}')::text[] as path from table1 ,jsonb_array_elements((field1->>'x4')::jsonb->'y1') with ordinality arr(x,index) where x->>'id'='RO' ) update table1 set field1=jsonb_set(field1,zd.path,to_jsonb('[ { "name": "john" } ]'::jsonb),false) from zd但是现在在当前json中,归档的X4不存在,我需要添加"z4":[{ 名称":约翰" },而不只是更新字段
But now in the current json the filed X4 is not there and i need to add "z4": [{ "name": "john" } instead of just updating the field
预期输出:
{ "x1": "Americas", "x2": "West", "x3": [{ "x_id": "sam" }], "x4": { "a1": true, "a2": false, "a3": [ "xx", "xx" ], "a4": [ "Josh" ], "y1": [{ "id": "RW", "z2": true, "z3": "USER" }, { "id": "RO", "z2": false, "z3": "SELECT", "z4": [{ "name": "john" }] } ] } }是否可以修改以上查询或建议使用新查询以同时适用于add(如果没有归档z4)和更新归档z4示例"z4":[{"name":"john"},{"name :" Steve}](如果存在提交的z4.
Can the above query be modified or suggest a new query to work for both add(if filed z4 is not there) and update filed z4 example "z4": [{ "name": "john" },{ "name": "Steve" }] if filed z4 is present.
推荐答案如果要添加z4,则只需将jsonb_set函数中的最后一个参数更改为"true",而不是"false".这指示函数创建该字段(如果该字段不存在). 您需要将to_jsonb(...)更改为'....':: jsonb才能解析数组. 这应该可行:
If you want to add z4, you only need to change the last argument in the jsonb_set function to "true" instead of "false". This instructs the function to create the field if it doesn't exist. You need to change the to_jsonb(...) to '....'::jsonb to parse the array. This should work:
with zd as (select ('{x4,y1,'||index-1||',z4}')::text[] as path from table1 ,jsonb_array_elements((field1->>'x4')::jsonb->'y1') with ordinality arr(x,index) where x->>'id'='RO' ) update table1 set field1=jsonb_set(field1,zd.path,'[{ "name": "john" },{ "name": "Steve" }]'::jsonb,true) from zd我希望我在这里正确粘贴并修改了它:-) 最好的问候, 比尼亚尼
I hope I got this correctly pasted and modified in here :-) Best regards, Bjarni
更多推荐
使用PostgreSQL在NESTED JSONB数组中添加/更新对象和元素
发布评论