1

I have a table with following definition:

create table json_test (
    filter_data jsonb);

and i insert to it values like this:

'{"task_packets": [
    {
        "state": "PROCEEDING",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'

Now I want to update this jsonb column to:

'{"task_packets": [
    {
        "state": "DONE",
        "task_id": 1001
    },
    {
        "state": "REVERTING",
        "task_id": 1002
    }
]}'

i.e. I want to change state for value with specified task_id inside array of task_packets. I suggest somehow to use jsonb_set() function in combination with #- operator (first delete value from array, then append to it with updated state).
How can I do it?

  • Is the array element you want to modify guaranteed to be in first position? Actually, this should probably be a normalized design instead of a jsonb column ... – Erwin Brandstetter Aug 13 '16 at 13:15
  • No, it's not guaranteed to be first. Actually, i've solved that probem by unpacking json array to elements with function jsonb_array_elements, thewn filtering, and then array_agg function. Like this: select jsonb_agg(l.*) || '{"task_id" : 1002, "state" : "REVERTING"}' from jsonb_array_elements('[{"task_id" : 1001, "state" : "PROCEEDING"}, {"task_id" : 1002, "state" : "REVERTING"}, {"task_id" : 1003, "state" : "REVERTING"}]') as l where (l->>'task_id')::int <> 1002 this code yelds initial array. – ArtemP Aug 13 '16 at 16:03
7

Like I commented, this would be more efficient with a normalized DB layout, with a table like this

CREATE TABLE task_packets (
  task_id int PRIMARY KEY
, state text NOT NULL
-- or: state_id int NOT NULL REFERENCES state(state_id) ...
);

Among other things, we can have a PK constraint enforcing unique task_id numbers. And the UPDATE you want is trivial.


But to answer the question asked:

To SELECT:

SELECT *
FROM   json_test jt
     , LATERAL (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
                               THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                               ELSE elem
                             END)) AS filter_data_new
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   ) tp
WHERE  jt.filter_data @> '{"task_packets": [{"task_id": 1001}]}';

I suggest a LATERAL join, among other things to exclude the possibility of multiple matching rows that might be lumped together incorrectly in a plain join.

To UPDATE:

UPDATE json_test
SET    filter_data =
   (
   SELECT jsonb_set(filter_data
                  , '{task_packets}'
                  , jsonb_agg(CASE WHEN elem->>'task_id' = '1001'
                                THEN jsonb_set(elem, '{state}', to_jsonb(text 'DONE'))
                                ELSE elem
                              END))
   FROM   jsonb_array_elements(filter_data->'task_packets') elem
   )
WHERE  filter_data @> '{"task_packets": [{"task_id": 1001}]}';

The same can be implemented with a correlated subquery in the UPDATE (or in the SELECT as well).

To make this fast for big tables, be sure to have an appropriate index, ideally a jsonb_path_ops index:

  • Thx, your answer if full enough for my purposes. – ArtemP Aug 14 '16 at 9:20
0

This helped a lot, however I had to update multiple keys in the array at once and went with the 9.5+ concatenate shortcut ||.

update public.contactsdb
set data = (select jsonb_set(data, '{Contacts}', jsonb_agg(
    elem || '{"Summary": "A User", "Email":"thisemail@is.bad"}'))
    from jsonb_array_elements(data->'Contacts') elem )
  where id in (select id from contactsdb,
   jsonb_array_elements(data->'Contacts') as elems where elems->'Summary' is not null)

sorry for the formatting in the block. i can't get it right. mod have at it

  • I'm not sure if your parenthesis are correct. If I try to reformat your code I am under the impression that parts of the statement are closed ) in the wrong place. Does this statement actually work? – John aka hot2use Sep 5 '18 at 7:01
  • Strange. Yes, it does work for me. it is taken directly after running minus some field and value redacting only. – cyclops Sep 5 '18 at 22:02
  • @hot2use i did mess up the where table orginally. just fixed that, but otherwise i just double checked it and it is exactly the same as the one i used but with redacting. – cyclops Sep 5 '18 at 22:09
0

try this

update 
    json_test 
set
    filter_data  = 
        jsonb_set(
            filter_data ,
            array['task_packets', elem_index::text, 'state'],
            '"DONE"'::jsonb,
            true)
from (
    select 
     pos- 1 as elem_index
    id 
    from 
        json_test, 
        jsonb_array_elements(filter_data ) with ordinality arr(elem, pos)
    where
       elem->>'task_id' = '1001'
    ) sub
where sub.id=id 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.