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';
4
  • Show us what you have implemented so far, even if it is not yet working. stackoverflow.com/help/minimal-reproducible-example
    – J_H
    Commented Aug 16, 2022 at 17:20
  • Updated the post @J_H
    – sharknado
    Commented Aug 16, 2022 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
    Commented Aug 16, 2022 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? Commented Aug 17, 2022 at 0:29

1 Answer 1

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:

1
  • Thank you very much. It worked perfectly.
    – sharknado
    Commented Aug 17, 2022 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.