Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using jsonb_set() for updating specific jsonb array value

Currently I am working with PostgreSQL 9.5 and try to update a value inside an array of a jsonb field. But I am unable to get the index of the selected value

My table just looks like this:

 CREATE TABLE samples (
    id serial,
    sample jsonb
 );

My JSON looks like this:

{"result": [
    {"8410": "ABNDAT", "8411": "Abnahmedatum"},
    {"8410": "ABNZIT", "8411": "Abnahmezeit"},
    {"8410": "FERR_R", "8411": "Ferritin"}
]}

My SELECT statement to get the correct value works:

SELECT 
    id, value 
FROM 
    samples s, jsonb_array_elements(s.sample#>'{result}') r  
WHERE 
    s.id = 26 and r->>'8410' = 'FERR_R';

results in:

id | value
----------------------------------------------
26 | {"8410": "FERR_R", "8411": "Ferritin"}

Ok, this is what I wanted. Now I want to execute an update using the following UPDATE statement to add a new element "ratingtext" (if not already there):

UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,2,ratingtext}',
              '"Some individual text"'::jsonb,
              true)
WHERE
      s.id = 26;

After executing the UPDATE statement, my data looks like this (also correct):

{"result": [
    {"8410": "ABNDAT", "8411": "Abnahmedatum"},
    {"8410": "ABNZIT", "8411": "Abnahmezeit"},
    {"8410": "FERR_R", "8411": "Ferritin", "ratingtext": "Some individual text"}
]}

So far so good, but I manually searched the index value of 2 to get the right element inside the JSON array. If the order will be changed, this won't work.

So my problem:

Is there a way to get the index of the selected JSON array element and combine the SELECT statement and the UPDATE statement into one?

Just like:

UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,' || INDEX OF ELEMENT || ',ratingtext}',
              '"Some individual text"'::jsonb,
              true)
WHERE
      s.id = 26;

The values of samples.id and "8410" are known before preparing the statement.

Or is this not possible at the moment?

like image 760
Daniel Seichter Avatar asked Aug 17 '16 12:08

Daniel Seichter


People also ask

How do you update objects in Jsonb arrays with PostgreSQL?

Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .

Can you index Jsonb Postgres?

JSONB and IndexesPostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.

Is Jsonb faster than JSON?

The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.


1 Answers

You can find an index of a searched element using jsonb_array_elements() with ordinality (note, ordinality starts from 1 while the first index of json array is 0):

select 
    pos- 1 as elem_index
from 
    samples, 
    jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
where
    id = 26 and
    elem->>'8410' = 'FERR_R';

 elem_index 
------------
          2
(1 row) 

Use the above query to update the element based on its index (note that the second argument of jsonb_set() is a text array):

update 
    samples
set
    sample = 
        jsonb_set(
            sample,
            array['result', elem_index::text, 'ratingtext'],
            '"some individual text"'::jsonb,
            true)
from (
    select 
        pos- 1 as elem_index
    from 
        samples, 
        jsonb_array_elements(sample->'result') with ordinality arr(elem, pos)
    where
        id = 26 and
        elem->>'8410' = 'FERR_R'
    ) sub
where
    id = 26;    

Result:

select id, jsonb_pretty(sample)
from samples;

 id |                   jsonb_pretty                   
----+--------------------------------------------------
 26 | {                                               +
    |     "result": [                                 +
    |         {                                       +
    |             "8410": "ABNDAT",                   +
    |             "8411": "Abnahmedatum"              +
    |         },                                      +
    |         {                                       +
    |             "8410": "ABNZIT",                   +
    |             "8411": "Abnahmezeit"               +
    |         },                                      +
    |         {                                       +
    |             "8410": "FERR_R",                   +
    |             "8411": "Ferritin",                 +
    |             "ratingtext": "Some individual text"+
    |         }                                       +
    |     ]                                           +
    | }
(1 row)

The last argument in jsonb_set() should be true to force adding a new value if its key does not exist yet. It may be skipped however as its default value is true.

Though concurrency issues seem to be unlikely (due to the restrictive WHERE condition and a potentially small number of affected rows) you may be also interested in Atomic UPDATE .. SELECT in Postgres.

like image 125
klin Avatar answered Sep 18 '22 15:09

klin