Postgres/JSON Updating nested array elements

Question

Given the input JSON from the 'table' under a column named '_value'. I would like to replace the field "sc" as text from object to value of name under sc.

The json before updating looks like this.

{
    "iProps": [
    {
        "value": {
            "rules": [
                {
                    "ao": {
                        "sc": {
                            "web_link": "abc.com",
                            "name": "name"
                        }
                    }
                },
                {
                    "ao": {
                        "sc": ""
                    }
                }
            ]
        }
    }
]
}

The json after updating should look like this.

{
    "iProps": [
    {
        "value": {
            "rules": [
                {
                    "ao": {
                        "sc":  "name"
                    }
                },
                {
                    "ao": {
                        "sc": ""
                    }
                }
            ]
        }
    }
]
}

I tried the below query to get to 'rules' array but having difficulty to proceed further in parsing and updating.

 WITH values AS (
    SELECT iprop -> 'value' -> 'rules' AS value FROM
    table t,jsonb_array_elements(t._value->'iProps') AS 
        iprop )
SELECT *
from values, jsonb_array_elements(values.ao)

throws following error

ERROR:  column values.ao does not exist
LINE 26: from values, jsonb_array_elements(values.ao)
                                           ^
SQL state: 42703
Character: 1396

jojo · Accepted Answer · 2021-01-17 09:12:33Z

1

You can try below mentioned query considering your structure is constant and the data type of your column is JSONB.

with cte as (
select 
 vals2->'ao'->'sc'->'name' as namevalue,
  ('{iProps,'||index1-1||',value,rules,'||index2-1||',ao,sc}')::text[] as json_path
from 
  table_, 
  jsonb_array_elements(value_->'iProps') 
  with ordinality arr1(vals1,index1),
  jsonb_array_elements(vals1->'value'->'rules') 
  with ordinality arr2(vals2,index2)

  )

 update table_ 
 set value_ = jsonb_set(value_,cte.json_path,cte.namevalue,false) 
 from cte
WHERE cte.namevalue IS NOT NULL

DEMO

edited Jan 17, 2021 at 9:12

jojo

3951 gold badge4 silver badges15 bronze badges

answered Jan 12, 2021 at 9:28

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jojo Over a year ago

This query works but sometime hit null constraints when the cte.namevalue is null . Do you think adding "WHERE cte.namevalue IS NOT NULL" will solve the problem? Am I missing to catch any other errors?

jojo Over a year ago

Here index 1 is 1 since iProps has one object in array while index2 is 1 and 2 since there are 2 objects in the inner array. But I am failing to understand why this query does not update the 2nd rule . json path iProps -> value -> rules -> ao -> sc -> name = "name2". dbfiddle.uk/…

Collectives™ on Stack Overflow

Postgres/JSON Updating nested array elements

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related