Postgres JSONB remove nested objects from array

Question

I am trying to remove specific objects from a nested array in JSONB (Postgresql 13) structure

I want to remove objects that contain the key:value {'id': 138105} from both the 'topTenUsersBySwims' and 'topTenUsersByCalories' arrays (can be done as two separate queries)

I am able to obtain each occurrence of the object but not sure how to remove from the array in a single SQL query?

The JSON structure is as follows

[{
        "year": 2023,
        "month": 5,
        "dailyStats": {},
        "totalSwims": 0,
        "totalUsers": 0,
        "dateUpdated": "Jun 01, 2023 12:01:45 AM",
        "totalCalories": 0,
        "totalDistance": 0,
        "totalNewUsers": 0,
        "totalSwimTime": 0,
        "topTenUsersBySwims": [{
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            }
        ],
        "topTenUsersByCalories": [{
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            }
        ]
    },
    {
        "year": 2023,
        "month": 3,
        "dailyStats": {
            "2": {
                "totalSwims": 1,
                "totalUsers": 1,
                "totalCalories": 112,
                "totalDistance": 400.0,
                "totalNewUsers": 0,
                "totalSwimTime": 492.1
            },
            "9": {
                "totalSwims": 2,
                "totalUsers": 1,
                "totalCalories": 210,
                "totalDistance": 750.0,
                "totalNewUsers": 0,
                "totalSwimTime": 975.8
            },
            "18": {
                "totalSwims": 1,
                "totalUsers": 1,
                "totalCalories": 112,
                "totalDistance": 400.0,
                "totalNewUsers": 0,
                "totalSwimTime": 465.3
            },
            "20": {
                "totalSwims": 1,
                "totalUsers": 1,
                "totalCalories": 659,
                "totalDistance": 2350.0,
                "totalNewUsers": 0,
                "totalSwimTime": 3307.2
            },
            "23": {
                "totalSwims": 1,
                "totalUsers": 1,
                "totalCalories": 140,
                "totalDistance": 500.0,
                "totalNewUsers": 0,
                "totalSwimTime": 540.1
            }
        },
        "totalSwims": 6,
        "totalUsers": 1,
        "dateUpdated": "Apr 01, 2023 12:01:44 AM",
        "totalCalories": 1233,
        "totalDistance": 4400.0,
        "totalNewUsers": 0,
        "totalSwimTime": 5780.5,
        "topTenUsersBySwims": [{
                "id": 138105,
                "name": "John Doe",
                "value": 6.0
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            }
        ],
        "topTenUsersByCalories": [{
                "id": 138105,
                "name": "John Doe",
                "value": 1233.0
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            },
            {
                "name": "--",
                "value": "--"
            }
        ]
    }
]

Pepe N O · Accepted Answer · 2023-06-07 16:10:56Z

0

It could be done like this (using the operator #- to remove elements)

select id, 
json_data #- ('{1,topTenUsersByCalories,'||e.i-1/*index to remove*/||'}')::text[] as result  
from (  select id, 
        json_data #- ('{1,topTenUsersBySwims,'||e.i-1/*index to remove*/||'}')::text[] as json_data  
        from test4, jsonb_array_elements(json_data->1->'topTenUsersBySwims') with ordinality as e(cont, i)
        where e.cont->'id'='138105') t
, jsonb_array_elements(t.json_data->1->'topTenUsersByCalories') with ordinality as e(cont, i)
where e.cont->'id'='138105';

this gives you the JSON data modified then you can execute an update to apply changes to the data. Check json operators

Working sample

answered Jun 7, 2023 at 16:10

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

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

3 Comments

DaveB Over a year ago

Thanks this is good however it only appears to be looking in the second array element ( using -> 1 ->), I have tried replacing this with '[*]' to search all array elements but this doesn't give any matches?

DaveB Over a year ago

Ok I can see that we need another index variable to specify the index of the top level array

Pepe N O Over a year ago

Yes, a more practical implementation could be done through a stored procedure/function to get both indexes and assemble the subtracting element's path.

Collectives™ on Stack Overflow

Postgres JSONB remove nested objects from array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related