使用PostgreSQL删除NESTED JSONB数组内的对象 [英] Delete 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",
                "z4": [{
                    "name": "john"
                }]
            },
             {
                "id": "RO",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }
            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}'::jsonb)

问题1:如何从y1数组中删除"id":"RO"中的数组值? y1 json数组中可以没有任何元素，我想根据id条件删除数组值.

Question 1 :how can i delete the the array values where "id":"RO" from y1 array ? There can be any no of element in the y1 json array i want to delete the array value based on id condition.

我如何从y1数组中删除"id":"RO"的数组值? y1 json数组中可以没有任何元素，我想根据id条件删除数组值.

how can i delete the the array values where "id":"RO" from y1 array ? There can be any no of element in the y1 json array i want to delete the array value based on id condition.

删除后的预期输出:

{
    "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",
                "z4": [{
                    "name": "john"
                }]
            },

            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}

问题2:如何仅从y1数组中删除"id":"RO"

Question 2 : How can i only delete the "id":"RO" from the y1 array

预期输出:

{
    "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",
                "z4": [{
                    "name": "john"
                }]
            },
             {
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }
            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}

推荐答案

编辑说明:我误解了这个问题.以为您想删除整个包含"RO"的对象.我编辑了答案，以便删除ID.

Edit note: I misunderstood the question. Thought you wanted to delete the whole object containing 'RO'. I edited the answer so as just to delete the id.

您提供的jsonb对象中有一个小错误.它可能看起来应该像这样:

There is a small error in the jsonb object you provide. It should probably look like this:

{
    "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",
                "z4": [{
                    "name": "john"
                }]
            },
             {
                "id": "RO",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "salin"
                }]
            },
            {
                "id": "DBA",
                "z2": false,
                "z3": "SELECT",
                "z4": [{
                    "name": "Samule"
                }]
            }
        ]
    }
}

话虽如此，这应该起作用，但请记住-这将替换工作表中的所有条目. jsonb对象位于字段"字段中.

With that said, this should work, but keep in mind - this will replace all entries in the working table. The jsonb objects are in the field "field".

with zd as (select ('{x4,y1,'||index-1||',id}')::text[] as path
            from table
            ,jsonb_array_elements((field->>'x4')::jsonb->'y1') 
            with ordinality arr(x,index)
            where x->>'id'='RO'
        )
        update table set field=
        field #- zd.path 
        from zd

最诚挚的问候，
比尼亚尼

Best regards,
Bjarni

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