modify only last value in jsonb field

user_18063802 0
Viewed 46

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 of objects , 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 .

jsonb postgresql arrays json
2 Answers

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.

user_58413060

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

user_3303150
Related