How to update json in postgres array?

Question

I have attached the JSON Input and Output

Input

{
  "cap1": [
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 6,
          "fun1": [
            {
              "fnd1": [
                {
                  "name": "v1",
                  "site": [
                    "w1",
                    "mb1",
                    "tb1"
                  ]
                }
              ]
            }
          ]
        }
      ],
      "capab1": 2
    },
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 42,
          "fun1": null
        },
        {
          "par1": 42,
          "fet1": 43,
          "fun1": null
        }
      ],
      "capab1": 11
    }
  ]
}

I want to add {"par1": 0, "fet1": 44, "fun1": null},{"par1": 0, "fet1": 45, "fun1": null} where "capab1": 11.

Output should be

{
  "cap1": [
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 6,
          "fun1": [
            {
              "fnd1": [
                {
                  "name": "v1",
                  "site": [
                    "w1",
                    "mb1",
                    "tb1"
                  ]
                }
              ]
            }
          ]
        }
      ],
      "capab1": 2
    },
    {
      "fe1": [
        {
          "par1": 0,
          "fet1": 42,
          "fun1": null
        },
        {
          "par1": 42,
          "fet1": 43,
          "fun1": null
        },
        {
          "par1": 0,
          "fet1": 44,
          "fun1": null
        },
        {
          "par1": 0,
          "fet1": 45,
          "fun1": null
        }
      ],
      "capab1": 11
    }
  ]
}

Question aside, it is bad practice to store deeply nested JSON in SQL, especially when it needs to be updated frequently. I'd rather fetch and manipulate the JSON in program, then update the DB record. But still, it performs poorly. — yogski
– yogski, Commented Jun 9, 2021 at 4:04

Akhilesh Mishra · Accepted Answer · 2021-06-09 05:50:36Z

1

Its a bit complex. you can use jsonb_array_elements to unnest the array and get the path to be updated. Then update the JSON using JSONB_INSERT like below: Here is the solution:

with cte as (
select  *,
('{cap1,'||index1-1||',fe1,0}')::text[] as json_path
from test,
jsonb_array_elements(col->'cap1') with ordinality arr1 (vals1,index1)
where (vals1->>'capab1')::int=11
)
 update test 
 set col = jsonb_insert(test.col,cte.json_path,'[{"par1": 0, "fet1": 44, "fun1": null},{"par1": 0, "fet1": 45, "fun1": null}]'::jsonb,true) 
 from cte 
 where test.id=cte.id

DEMO

answered Jun 9, 2021 at 5:50

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

How to update json in postgres array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related