从对象JSONB中的数组中删除元素 [英] Removing element from array within object JSONB

问题描述

我正在尝试从数据库中删除一个元素.我要删除的元素在一个名为playerContainer的JSON对象中，该对象包含一个名为players的数组.所有这些都位于一个名为site_content的表中.我正在尝试根据其ID删除该对象，但是出现此错误.

I am attempting to remove an element from my database. The element I want to remove is within a JSON object called playerContainer which contains an array named players. This all sits within a table called site_content. I am trying to remove the object based on it's ID, however I get this error.

删除元素 来自site_content， 侧面jsonb_array_elements(content->'playersContainer'->'players')元素 其中elem @>'{"id":"22"}'
因为:错误:"elem"位置或附近的语法错误:27

DELETE elem from site_content, lateral jsonb_array_elements(content->'playersContainer'->'players') elem where elem @> '{"id":"22"}'
because: ERROR: syntax error at or near "elem" Position: 27

下面是我的查询，谁能看到我要去哪里错了?

Below is my query, can anyone see where I am going wrong?

DELETE elem 
from site_content, 
lateral jsonb_array_elements(content->'playersContainer'->'players') elem 
where elem @> '{"id":"1"}' 

这是示例JSON

"playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick",
                "teamName": "Shire Soldiers",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19

            },
}

推荐答案

DELETE 适用于表的行.因此，除非要删除完整的行，否则不能使用它.

DELETE works on rows of a table. Therefore you can't use it unless if you want to remove the complete row.

尝试一下:

create temp table testing as
select 
    '{ "playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick"
            },
            {
                "id": "2",
                "name": "Rick"
            },
            {
                "id": "3",
                "name": "Trick"
            }
        ]
     }}'::jsonb as value;

现在，您需要找到要删除的球员的位置，假设您想要ID为2(负1，因为索引从0开始)的里克

Now you need to find the position of the player you want to remove, lets say you want Rick with id 2 (minus 1 because index starts from 0)

select position-1 from testing, jsonb_array_elements(value->'playersContainer'->'players') with ordinality arr(elem, position) WHERE elem->>'id' = '2';

现在，您可以将其与UPDATE语句结合使用以更新该字段.使用减号(-)运算符可删除所需索引处的元素.

Now you can combine this with an UPDATE statement to update the field. Use minus (-) operator to remove the element at wanted index.

UPDATE testing SET value = jsonb_set(value, '{playersContainer,players}', (value->'playersContainer'->'players') - (select position-1 from testing, jsonb_array_elements(value->'playersContainer'->'players') with ordinality arr(elem, position) WHERE elem->>'id' = '2')::int );

最终结果:

{
    "playersContainer":{
        "players":[
            {
                "id":"1",
                "name":"Nick"
            },
            {
                "id":"3",
                "name":"Trick"
            }
        ]
    }
}

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