My application has a table called cs_savedattribute which holds an args column that saves detailed JSON attributes for a record. There was an existing "cs_id": "123456789" attribute that needed to be moved to JSON structure:
{
"source": {
"id": "123456789",
"csGroupingType": "Primary"
}
}
Environment:
PostgreSQL 12.9
Below are the PostgreSQL statements that can apply it and then un-apply it if necessary.
NOTE: Because the args column is text all queries below had to be cast to jsonb for every instance first.
FORWARD
-- 1 WORKS: add the new args.source json attribute
UPDATE cs_savedattribute SET args = CAST(args AS jsonb) || '{"source": {"id": "SET_ME","csGroupingType": "Primary"}}'::jsonb
WHERE CAST(args AS jsonb) ? 'cs_id' = true;
-- 2 WORKS: pain because it's a nested item so the key is '{source,id}'
-- update the args.source.id value using args.cs_id's value
UPDATE cs_savedattribute SET args = jsonb_set(CAST(args AS jsonb), '{source,id}', to_jsonb(CAST(args AS jsonb) ->> 'cs_id'))
WHERE CAST(args AS jsonb) ? 'cs_id' = true;
-- 3 WORKS: remove args.cs_id
UPDATE cs_savedattribute SET args = CAST(args AS jsonb) - 'cs_id'
WHERE CAST(args AS jsonb) ? 'source' = true;
REVERSE
-- 1 Didn't use jsonb_set because since args.cs_id didn't exist got a null error, use jsonb_build_object instead
-- add and set args.cs_id using args.source.id's value
UPDATE cs_savedattribute SET args = CAST(args AS jsonb) || jsonb_build_object('cs_id', CAST(args AS jsonb) -> 'source' ->> 'id')
WHERE CAST(args AS jsonb) ? 'source' = true;
-- 2 remove args.source
UPDATE cs_savedattribute SET args = CAST(args AS jsonb) - 'source'
WHERE CAST(args AS jsonb) ? 'source' = true;
VERIFY
select id, uuid, args from cs_savedattribute WHERE CAST(args AS jsonb) ? 'cs_id' = true
select id, uuid, args from cs_savedattribute WHERE CAST(args AS jsonb) ? 'source' = true
This article was helpful: https://stackoverflow.com/questions/45481692/postgres-jsonb-set-multiple-nested-fields
No comments:
Post a Comment
I appreciate your time in leaving a comment!