PostgreSQL Question posted in
The official documentation can be found here.

Updating JSON value in PostgreSQL 15

I need to update a specific value in a column with the json type.
All solutions I found online are either for jsonb or don’t seem quite right and give me an syntax error and I don’t know why.

That we are on the same page: it’s a Postgres 15.3 database that was created like this:

create table if not exists "DOCF000T6"."assignments"
(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    correlation_id VARCHAR ( 31 ) UNIQUE NOT NULL,
    reference_id VARCHAR ( 100 ),
    jdata json NOT NULL,
    errormassage VARCHAR
);

And the one json that needs to be edited:

{
    "directoryRefId": "00DhDRC53dkoMUsC73znji4UoDUz",
    "useragent": "agent-ftps",
    "creationdate": "2023-12-12 15:41:03.3",
    "data": {
        "variables": {
            "dms-dokument-name": "Notifiation 22.09.2022.pdf",
            "dms-vo-remind": "false",
            "dms-messagebox": "E_BN",
            "dms-file-number": "0000-15840",
            "dms-pio-oe": "MUB",
        }
    }
}

I need to update the dms-file-number like this

UPDATE assignments
SET jdata['data']['variables']['dms-file-number']  = '0001-05840'
WHERE 
jdata-> 'data' -> 'variables' ->> 'dms-file-number' = '0000-15840';

I know that something like that should work for jsonb, but I can’t get it to work.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Zegarek'S anser workex perfectly i just trew out the jsonb_pretty because i realy don't need it in my case and i added a WHERE to not completely wipe my Database

    update "DOCF000T6"."assignments" 
set jdata=jsonb_set(
              jdata::jsonb,
              '{data,variables,dms-file-number}',
              '"0001-05840"' )::json
WHERE 
correlation_id='00DgLDdcUUXyLeIF9USLAf78fryu'

  2. 0

    As discussed in the comments, you can cast to ::jsonb, use jsonb_set(), then cast back to ::json. A side effect of that will be removed insignificant whitespace, reordered and deduplicated keys. You can restore formatting by running the value through jsonb_pretty() on its way back: demo at db<>fiddle

    update "DOCF000T6"."assignments" 
set jdata=jsonb_pretty(
            jsonb_set(
              jdata::jsonb,
              '{data,variables,dms-file-number}',
              '"0777-7"' ) )::json
where (jdata#>>'{data,variables,dms-file-number}') = '0000-15840'
returning *;

    Some display configurations might line-wrap jsonb_pretty()fied jdata in the table below. The fiddle should preserve the real resulting format.

    id correlation_id referece_id jdata errormassage
    1 correlation_id1 null {
        "data": {
            "variables": {
                "dms-pio-oe": "MUB",
                "dms-vo-remind": "false",
                "dms-messagebox": "E_BN",
                "dms-file-number": "0777-7",
                "dms-dokument-name": "Notifiation 22.09.2022.pdf"
            }
        },
        "useragent": "agent-ftps",
        "creationdate": "2023-12-12 15:41:03.3",
        "directoryRefId": "00DhDRC53dkoMUsC73znji4UoDUz"
    }    		 null

    You could also consider if it’s maybe worth to alter the column permanently and leave it as jsonb, which will make it lighter, faster and conveniently indexable, adding lots of flexibility through support of additional operators and functions, at the expense of having to cascade this change to where the value’s used in your code. Also some minor behavioral changes, like inability to hold duplicate keys, preserve custom formatting and custom key order.

    alter table "DOCF000T6"."assignments" alter column jdata type jsonb;
update "DOCF000T6"."assignments" 
  set jdata['data']['variables']['dms-file-number']='"0999-9"'
where jdata['data']['variables']['dms-file-number']='"0000-15840"';

    If formatting and whitespace were your primary concern, you’ll be disappointed with the solution using pure json without jsonb: it can preserve custom key order and duplicate keys but exploding, then re-aggregating the object still wipes the whitespace. Here I added a replace() to add some newlines for readability:

    update "DOCF000T6"."assignments" 
set jdata=(
select replace(json_object_agg(
         k1,
         case when k1<>'data' then v1 
              else json_build_object(
                     'variables',
                     (select json_object_agg(
                               k2,
                               case when k2<>'dms-file-number' then v2 
                                    else '"0777-7"' 
                               end
                               order by n2)
                       from json_each(v1->'variables')
                              with ordinality as variables(k2,v2,n2) ))
          end
          order by n1)::text, '",', E'",n')::json
from json_each(jdata)with ordinality as top_level_dict(k1,v1,n1)
)
where (jdata#>>'{data,variables,dms-file-number}') = '0000-15840'
returning *;
    id correlation_id referece_id jdata errormassage
    1 correlation_id1 null { "directoryRefId" : "00DhDRC53dkoMUsC73znji4UoDUz",
     "useragent" : "agent-ftps",
     "creationdate" : "2023-12-12 15:41:03.3",
     "data" : {"variables" : { "dms-dokument-name" : "Notifiation 22.09.2022.pdf",
     "dms-vo-remind" : "false",
     "dms-messagebox" : "E_BN",
     "dms-file-number" : "0777-7",
     "dms-pio-oe" : "MUB" }} }    		 null

    As you can see, it’s not pretty and as you can guess, it’s pretty slow compared to jsonb. To make things worse, you should actually add a middle level to make sure you don’t lose additional entries neighbouring variables under data – this now assumes there will only ever be variables as the only key under data and if there’s anything else, this would discard that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search