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 } ]}
- 2Question 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– yogski2021-06-09 04:04:12 +00:00CommentedJun 9, 2021 at 4:04
- this is master data. we need to update once in a whileAmar Anugu– Amar Anugu2021-06-09 05:42:38 +00:00CommentedJun 9, 2021 at 5:42
1 Answer1
Its a bit complex. you can usejsonb_array_elements to unnest the array and get the path to be updated. Then update the JSON usingJSONB_INSERT like below:Here is the solution:
with cte as (select *,('{cap1,'||index1-1||',fe1,0}')::text[] as json_pathfrom 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 Sign up to request clarification or add additional context in comments.
Comments
Explore related questions
See similar questions with these tags.