使用PostgreSQL在NESTED JSONB数组内添加对象 [英] Add objects inside NESTED JSONB arrays with PostgreSQL

问题描述

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. "z4":[{ 名称":约翰" }， { 名称":史蒂夫" }

I want to update a new filed z4 based on id condition "id": "RO".Eample "z4": [{ "name": "john" }, { "name": "Steve" }

预期输出:

{
    "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"
                },{
                    "name": "Steve"
                }]
            }
        ]
    }
}

我可以使用哪些postgres JSONB sql实现上述输出?

what postgres JSONB sql i can use to achive the above output?

推荐答案

您应该可以这样做:

with zd as (select ('{x4,y1,'||index-1||',z3}')::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  

请注意，jsonb_set中的最后一个参数现在为true，它指示该参数创建不存在的字段.

Note the last argument in jsonb_set is now true, which instructs it to create the field if it doesn't exist.

最诚挚的问候，
比尼亚尼

Best regards,
Bjarni

这篇关于使用PostgreSQL在NESTED JSONB数组内添加对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！