2

I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field.

The node we are trying to move, however, is not fixed; sometimes its index can be 1, 2, or 3.

I currently have this query:

 UPDATE table1 t1 SET jsonbfield = jsonb_set(jsonfield ,
            '{field1,0,field2,0,configuration}',
            (select jsonbfield->'field1'->0->'field2'->0->'field3'->0->'configuration'
            from table1 where
            jsonbfield->'field1'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME'
            and id=t1.id), true)
            where jsonbfield->'field'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME';

Which basically would achieve the requirement if the node we were trying to move was always at position 0, however, as mentioned, we noticed that the node we want to move up can be on another positon.

Is there a way to iterate trough each row in JSON, get the position of the node we want to move, save it into a variable and place it in the update statement?

I have tried with a do for, but it seems it doesn't interact well with select jsonb_array_elements.

Sample Data

{
   "parent":[
      {
         "steps":[
            {
               "name":"CONFIG1",
               "index":1,
               "childStep":[
                  {
                     "configuration":{
                        
                     }
                  }
               ]
            },
            {
               "name":"CONFIG2",
               "index":2,
               "childStep":[
                  {
                     "configuration":{
                         "configInfo":{
                           "info":"someinfo"
                        }
                     }
                  }
               ]
            }
         ]
      }
   ]
}

This json represents the jsonbfield row from table1. What we want to do is move the configuration Node CONFIG2 from childStep to Step Level, so it would look something like this after updating it:

{
   "parent":[
      {
         "steps":[
            {
               "name":"CONFIG1",
               "index":1,
               "childStep":[
                  {
                     "configuration":{
                        
                     }
                  }
               ]
            },
            {
               "name":"CONFIG2",
               "index":2,
               "configuration":{
                         "configInfo":{
                           "info":"someinfo"
                        }
                     }
            }
         ]
      }
   ]
}

The query that i posted, already moves the field up, however, the issue resides on that we can not know if CONFIG2 will be located at index 0, 1, 2, etc. So we are trying to figure a way to dynamically get where its located and then update it. With the current query posted here, it assumes CONFIG2 is always located at index 1 (or 0, first position).

4
2
UPDATE table1
SET    jsonfield = (
   SELECT jsonb_set(jsonfield
                  , '{parent,0,steps}'::text[] || (idx - 1)::text
                  ,  val->'childStep'->0 || (val - text 'childStep')
                   )
   FROM   jsonb_array_elements(jsonfield#>'{parent,0,steps}') WITH ORDINALITY a(val, idx)
   WHERE  val->>'name' = 'CONFIG2'
   )
WHERE  jsonfield @> '{"parent":[{"steps":[{"name":"CONFIG2"}]}]}';
-- WHERE  jsonfield @? '$.parent[0].steps[*].name ? (@ == "CONFIG2")';  -- equivalent alternative

db<>fiddle here

I compute the new jsonb value in a correlated subquery.

WITH ORDINALITY produces the array index while unnesting the JSON array with jsonb_array_elements(). The later idx - 1 adjusts the off-by-1 error from Postgres arrays being 1-based and JSON arrays being 0-based. More about WITH ORDINALITY:

I added an outer WHERE condition so that only applicable rows are processed to begin with. Avoid updating rows with unchanged values as this adds costs without benefits.

You can either use the contains operator @> or the new operator @? in Postgres 12 or later SQL/JSON path language. Either can use an index. See:

3
  • Currently, there is a problem with markdown rendering on this site. The operator #> is misinterpreted as start of comment. Jan 2 at 19:22
  • this worked! i just made a few adjustments to match itt to my tables and it worked like a charm! thanks a lot! I guess the trick here is the WITH_ORDINALITY to get the index, right? Jan 4 at 20:17
  • @Gregorio: Yes, that's the key component to get the array index. I added a link to more. Jan 4 at 21:25

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.