Removing element from array within object JSONB

Question

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.

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"}'

Here is the sample 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

            },
}

Please, provide the table definition (\d site_content in psql). — klin
– klin, Commented Feb 20, 2017 at 19:32
"footballprototype";"public";"site_content";"id";1;"";"NO";"character varying";255;1020;;; "footballprototype";"public";"site_content";"content";2;"";"YES";"jsonb";;;;; — N P
– N P, Commented Feb 20, 2017 at 20:17
I have one table called site_content, with 2 rows, one called id and one called content which has the JSON in above — N P
– N P, Commented Feb 20, 2017 at 20:17

Evren Yurtesen · Accepted Answer · 2018-04-11 07:22:48Z

4

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

Try this:

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

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';

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 );

End result:

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

edited Apr 11, 2018 at 7:22

answered Apr 10, 2018 at 20:40

Evren Yurtesen

2,3691 gold badge24 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Camilo Delvasto Over a year ago

This fails for me if the element is not found (it will remove all the elements in the "players")... any ideas?

Evren Yurtesen Over a year ago

Divide it into 2 queries. execute UPDATE only if position was found in the previous query?

Camilo Delvasto Over a year ago

— yes, this is what I ended up doing

Collectives™ on Stack Overflow

Removing element from array within object JSONB

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related