1

I have a table tbl with the following structure:

CREATE TABLE tbl (
  org  text
, data jsonb
);

The data in the jsonb field is an array structured in the following way:

INSERT INTO tbl VALUES
('SOMETHING'
, '[
      {
        "type": "XYZ",
        "valueA": "500",
        "valueB": "ABC"
      },
      {
        "type": "ABC",
        "valueA": "300",
        "valueB": "CDE"
      }
   ]')
;

I want to add a key valueC to elements of data where the object has a "type" key with a value of "XYZ".

valueC's value will be an array of strings. The values of the array will depend on the value of the org column.

I want to do this for all rows such that if a specific org is present, and the jsonb array in the data column contains an object with "type": "XYZ", then I get this result:

[
  {
    "type": "XYZ",
    "valueA": "500",
    "valueB": "ABC",
    "valueC": ["SOMETHING"],
  },
  {
    "type": "ABC",
    "valueA": "300",
    "valueB": "CDE",
  }
]

I also want to ensure this script only runs if valueC is not present in the object that matches the conditions, so it is not re-run during a migration/rollback unless needed.

Here's what I have so far but it's not working when it does not find a result to the subquery and I can't figure out how to only run this if valueC does not exist:

UPDATE tbl SET  
  data = jsonb_set(
    data, 
    '{data}', 
   (SELECT jsonb_agg(elem ||'{"valueC":["SOMETHING"]}') FROM jsonb_array_elements(data->'data') as elem where elem ->> 'type' = 'XYZ')
  )
WHERE org = 'SOMETHING';
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
sharknado
  • 23
  • 1
  • 5
  • Show us what you have implemented so far, even if it is not yet working. https://stackoverflow.com/help/minimal-reproducible-example – J_H Aug 16 '22 at 17:20
  • Updated the post @J_H – sharknado Aug 16 '22 at 18:32
  • Well, you already have jsonb_array_elements(data->'data') as elem where elem ->> 'type' = 'XYZ', so you just need to add this to the where clause, right? – mustaccio Aug 16 '22 at 22:05
  • The path '{data}' you show in the UPDATE does not exist in the sample data. Also: Can there be multiple array elements with "type": "XYZ" in a single JSON array? And your version of Postgres? – Erwin Brandstetter Aug 17 '22 at 00:29

1 Answers1

3

SQL/JSON path expressions make this short and performant. Requires Postgres 12 or later:

UPDATE tbl
SET    data = (SELECT jsonb_agg(CASE WHEN jsonb_path_exists(elem, '$ ? (@.type == "XYZ") ? (!exists (@.valueC))')
                                     THEN elem || '{"valueC":["SOMETHING"]}'
                                     ELSE elem END)
               FROM   jsonb_array_elements(data) AS elem)
WHERE  org = 'SOMETHING'
AND    jsonb_path_exists(data, '$[*] ? (@.type == "XYZ") ? (!exists (@.valueC))')
RETURNING *;

db<>fiddle here

This allows for multiple qualifying array elements to be updated at once.

First, identify qualifying rows cheaply in the WHERE clause. (We wouldn't want to process all rows.)

Then, after unnesting with jsonb_array_elements(), recheck in a CASE expression to only modify qualifying array elements.

If the table is big, be sure to have an index. See:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600