Sunday, August 6, 2023

PostgreSQL - queries for jsonb data type to apply and unapply json attribute structure changes

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!