Conditional update with jsonb_set()

Question

I have a table in a Postgres 11.3 database with a jsonb column.

Trying to update all objects inside a nested array name "iProps".

If the path {iProps -> value -> rules -> ao -> sc} is an object, then the path should be updated from an object to a string with the value {iProps -> value -> rules -> ao -> sc -> name}

If the path {iProps -> value -> rules -> ao -> sc} is not present, then the object should be left unchanged.

Test setup with query: Fiddle link

Desired result:

{
    "iProps": [
        {
            "value": {
                "rules": [
                    {
                        "ao": {
                            "set": "get"
                        },
                        "name": "PRule"
                    },
                    {
                        "ao": {
                            "sc":  "name1"
                            
                        }
                    },
                    {
                        "ao": {
                            "sc": "name2"
                            
                        }
                    },
                    {
                        "ao": {
                            "sc":  "name3"                            
                        }
                    }
                ]
            }
        }
    ]
}

I have modified the query and linked in the fiddle. Can someone take a look to see if it's right?

This just hurts. Use a normal relational data model without JSON, and it will be simple. — Laurenz Albe, Jan 21 '21 at 04:07
but the data model does not follow a specific schema. Data is not as regular as the sample data — jojo, Jan 21 '21 at 05:04
Please remember to *always* disclose your version of Postgres. The fiddle says 9.5, but please be explicit. And be clear about your objective. I have read it twice and don't understand it. — Erwin Brandstetter, Jan 21 '21 at 17:06
Maybe you mean *and not modify the object if there is **no** 'sc' inside 'ao'`*? — Erwin Brandstetter, Jan 21 '21 at 17:14

Erwin Brandstetter · Accepted Answer · 2021-01-21T18:09:04.020

A plain CASE should make the difference.

UPDATE table_ t
SET    value_ = jsonb_set(value_, '{iProps}', sub2.new_prop, false)
FROM  (
   SELECT id
        , jsonb_agg(jsonb_set(prop, '{value, rules}', new_rules, false)
                    ORDER BY idx1) AS new_prop
   FROM  (
      SELECT t.id, arr1.prop, arr1.idx1
           , jsonb_agg(CASE WHEN jsonb_typeof(rule #> '{ao,sc}') = 'object'
                            THEN jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false)
                            ELSE rule
                       END
                       ORDER BY idx2) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       WITH ORDINALITY arr1(prop,idx1)
         , jsonb_array_elements(prop->'value'->'rules') WITH ORDINALITY arr2(rule,idx2)
      GROUP  BY t.id, arr1.prop, arr1.idx1
      ) sub1
   GROUP  BY id
   ) sub2
WHERE t.id = sub2.id;

db<>fiddle here (Postgres 11!)

To also meet the second filter you added in the update (must be an object), check with jsonb_typeof().

The query in your fiddle seems needlessly complicated (tl;dr). Also, it does not preserve original order of array elements. If that's in fact irrelevant, omit WITH ORDINALITY and ORDER BY and simplify further:

UPDATE table_ t
SET    value_ = jsonb_set(value_, '{iProps}', sub2.new_prop, false)
FROM  (
   SELECT id
        , jsonb_agg(jsonb_set(prop, '{value, rules}', new_rules, false)) AS new_prop
   FROM  (
      SELECT t.id, prop
           , jsonb_agg(CASE WHEN jsonb_typeof(rule #> '{ao,sc}') = 'object'
                            THEN jsonb_set(rule, '{ao,sc}', rule #> '{ao,sc,name}', false)
                            ELSE rule
                       END) AS new_rules
      FROM table_ t
         , jsonb_array_elements(value_->'iProps')       prop
         , jsonb_array_elements(prop->'value'->'rules') rule
      GROUP  BY t.id, prop
      ) sub1
   GROUP  BY id
   ) sub2
WHERE t.id = sub2.id;

db<>fiddle here

This typically still preserves the order of array elements (unlike your original). There's just no guarantee with two levels of aggregation.

See:

PostgreSQL unnest() with element number

More advice in my answer to your earlier related question:

UPDATE with jsonb_set() only affects one object in nested array

Do you think this problem could be solved with a procedural block with loop for update. This query looks complicated for a simple problem. — jojo, Jan 21 '21 at 19:24
@jojo: Certainly doable. But I don't think it will be much simpler. Another alternative would be with **SQL/JSON in Postgres 12** or later. Example: https://dba.stackexchange.com/a/251181/3684 — Erwin Brandstetter, Jan 21 '21 at 19:38

Conditional update with jsonb_set()

1 Answers1

Linked