Using jsonb_set() for updating specific jsonb array value

by

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.

More Related Contents:

  1. jsonb vs jsonb[] for multiple addresses for a customer
  2. Can PostgreSQL index array columns?
  3. Index for finding an element in a JSON array
  4. Query for array elements inside JSON type
  5. Add new element to existing JSON array with jq
  6. Unnest multiple arrays in parallel
  7. Return multiple columns of the same row as JSON array of objects
  8. Can PostgreSQL have a uniqueness constraint on array elements?
  9. Post an Array of Objects via JSON to ASP.Net MVC3
  10. Loop over array dimension in plpgsql
  11. How to query a json column for empty objects?
  12. Django JSONField inside ArrayField
  13. Accessing a JSON object in Bash – associative array / list / another model
  14. Swift JSONDecode decoding arrays fails if single element decoding fails
  15. Unnest array by one level
  16. Can an array be top-level JSON-text?
  17. How to check if element exists in array with jq
  18. Swift 4 JSON Decodable with multidimensional and multitype array
  19. Create a Single-Element Json Array Object Using PowerShell
  20. MongoDB rename database field within array
  21. Swift 4 JSON Codable – value returned is sometimes an object, others an array
  22. How can I decode a JSON response with an unknown key in Swift?
  23. Iterating over integer[] in PL/pgSQL
  24. Merging JSONB values in PostgreSQL?
  25. How to parse json in flutter?
  26. How to deserialize dodgy JSON (with improperly quoted strings, and missing brackets)?
  27. YAML equivalent of array of objects in JSON
  28. Operator does not exist: json = json
  29. jq: recursively merge objects and concatenate arrays
  30. In postgresql, how can I return a boolean value instead of string on a jsonb key?

Leave a Comment