By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table t1(data)as values
('{"type" : "users",
"info" : [
{
"id":1,
"name" : "user1"
},
{
"id":2,
"name" : "user2"
}
]
}'::jsonb)
,('[]'),('{}'),('null'),(null),('1'),('"abc"'),('1.1'),('false');
select jsonb_pretty(data)from t1;
SELECT 9
| jsonb_pretty |
|---|
| { "info": [ { "id": 1, "name": "user1" }, { "id": 2, "name": "user2" } ], "type": "users" } |
| [ ] |
| { } |
| null |
| null |
| 1 |
| "abc" |
| 1.1 |
| false |
SELECT 9
/*Now I want to delete entire json object which has id = 1
( I want to identify the object by json object id)
The expected result is
{
"type" : "users",
"info" : [
{
"id":2,
"name" : "user2"
}
]
}
*/
UPDATE t1
SET data = jsonb_set(data, '{info}', (
SELECT COALESCE(jsonb_agg(element), '[]'::jsonb)
FROM jsonb_array_elements(data -> 'info') element
WHERE element ->> 'id' <> '1'
))
WHERE data @> '{"info":[{"id":1}]}'
/*The where condition uses containment to makes sure that this doesn't rewrite rows, unless:
1. data root is a JSON type object, not a scalar, not an array, not null
2. in the root object there's an "info" key present
3. the "info" key holds an array, enabling jsonb_array_elements(), which would otherwise fail if there were scalars or objects under that path
4. inside that array there's an object with an "id" key,
5. its value is 1 that we're after @> uses an index if data has one - both jsonb_ops as well as jsonb_path_ops work
*/
RETURNING jsonb_pretty(data);
| jsonb_pretty |
|---|
| { "info": [ { "id": 2, "name": "user2" } ], "type": "users" } |
UPDATE 1