Solution for How to delete key inside object inside jsonb array in SQL?
is Given Below:
I have the following json data in the foods column of my table:
[
{
"name": "Pasta",
"price": 45.8,
"comments": {
"promo": true,
"special": true
}
},
{
"name": "Risotto",
"price": 31.4,
},
{
"name": "Pizza",
"price": 64.9,
"comments": {
"promo": true,
"special": true
}
},
{
"name": "Hamburguer",
"price": 14.9,
"comments": {
"combo": true
}
},
]
And I would like to delete all the promo keys, to look like this.
[
{
"name": "Pasta",
"price": 45.8,
"comments": {
"special": true
}
},
{
"name": "Risotto",
"price": 31.4,
},
{
"name": "Pizza",
"price": 64.9,
"comments": {
"special": true
}
},
{
"name": "Hamburguer",
"price": 14.9,
"comments": {
"combo": true
}
},
]
This data may not make much sense because it’s an example to create to show the case I’m trying to solve, but with much more data, in which I need to delete a key that exists in some objects (not all, as in the example) that are inside of an jsonb array.
How can I best perform? I use PostgreSQL version 12, so preferably with jsonb functions.
even with jsonb operators you’ll need to unnest and reaggregate data, this seems to work:
create table receipts (
receipt int,
foods jsonb
);
insert into receipts values
(1, '[
{
"name": "Pasta",
"price": 45.8,
"comments": {
"promo": true,
"special": true
}
},
{
"name": "Risotto",
"price": 31.4
},
{
"name": "Pizza",
"price": 64.9,
"comments": {
"promo": true,
"special": true
}
},
{
"name": "Hamburguer",
"price": 14.9,
"comments": {
"combo": true
}
}
]');
SELECT receipt, jsonb_agg(jsonb_strip_nulls(jsonb_set(element, '{"comments","promo"}', 'null')) ORDER BY ordinality)
FROM receipts
JOIN LATERAL jsonb_array_elements(foods) WITH ORDINALITY AS el(element, ordinality)
ON receipt = 1
GROUP BY receipt
.. but it will strip all null values (if any)