modify only last value in jsonb[] field-CodePudding

I have table with jsonb[] field

Example:

id   jsonb_t
1    [ {"x" : 1 , "y": 2} , {"x" : 2 , "y": 3} , {"x": 3, "y" : 4} ]
2    [ {"x" : 1 , "y": 3} , {"x" : 3 , "y": 3} , {"x": 8, "y" : 2} ]
3    [ {"x" : 1 , "y": 4} , {"x" : 4 , "y": 3} , {"x": 5, "y" : 9} ]

I want to modify table where id = 3 but only the last row in jsonb[] array , it means replace e.g. "y":9 into "y":8 , and increment "x":5 by 1 to "x":6 . I can't figure out how to do it in one step (replace and increment should be done "in place" due to thousends rows in jsonb[] array field) , thanks in advance for help .

CodePudding user response：

You can use some jsonb functions such alike

SELECT jsonb_agg(jsonb_build_object('x', x, 'y', y))
  FROM (SELECT CASE
                 WHEN row_number() over() = jsonb_array_length(jsonb_t) THEN
                  x   1
                 ELSE
                  x
               END AS x,
               CASE
                 WHEN row_number() over() = jsonb_array_length(jsonb_t) THEN
                  y - 1
                 ELSE
                  y
               END AS y
          FROM t, jsonb_to_recordset(jsonb_t) AS(x INT, y INT)
         WHERE id = 3) AS j

Demo

where jsonb_to_recordset expands outermost array of objects as individual integer elements, then (in/de)crement them after determining match through use of row_number and jsonb_array_length functions, then go back to build up the jsonb value again within the main query.

CodePudding user response：

Your sample data looks like the column is in fact defined as jsonb not jsonb[] and the array is a proper JSON array (not an array of jsonb values)

If that is correct, then you can use jsonb_set() to extract and modify the value of the last array element:

update the_table
  set jsonb_t = jsonb_set(jsonb_t, 
                    array[jsonb_array_length(jsonb_t)-1]::text[], 
                    jsonb_t -> jsonb_array_length(jsonb_t)-1 || 
                        '{"y":8}' || 
                         jsonb_build_object('x', (jsonb_t -> jsonb_array_length(jsonb_t)-1 ->> 'x')::int   1)
                 )
where id = 3

As documented in the manual jsonb_set() takes three parameters: the input value, the path to the value that should be changed and the new value.

The second parameter array[jsonb_array_length(jsonb_t)-1]::text[] calculates the target position in the JSON array by taking its length and subtracting one to get the last element. This integer is then converted to a text array (which is the required type for the second parameter).

The expression jsonb_t -> jsonb_array_length(jsonb_t)-1 then picks that array element and appends the '{"y":8}' which will replace the existing key/value pair with y. The expression

jsonb_build_object('x', (jsonb_t -> jsonb_array_length(jsonb_t)-1 ->>  'x')::int   1

extracts the current value of the x key, converting it to an integer, increments it by one and builds a new JSON object with the key x that is also appended to the old value, thus replacing the existing key.

Online example