Question 110232 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Append in nested array JSON object in oracle

I have JSON document column in one of the table and its structure is like:-

{ 
 "root":[{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"ABC1",
                           "MCR_COLUMN_2":"ABC2"
                        }
            },
         {"MCR":"MCR_2",
          "MCR_COLUMNS":{ 
                            "MCR_COLUMN_1":"XYZ1",
                             "MCR_COLUMN_2":"XYZ2"
                        }
            }
        ]
}

Now I want to write a merge statement to merge in this document to manage two cases

CASE-1) If MCR value is already present in document, then directly append MCR_COLUMN_x and its value to JSON object of its MCR_COLUMNS. eg. I want to append

{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_3":"ABC3"
                        }
            }

so, the updated document should be

{ 
 "root":[{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"ABC1",
                           "MCR_COLUMN_2":"ABC2",
                           "MCR_COLUMN_3":"ABC3"
                        }
            },
         {"MCR":"MCR_2",
          "MCR_COLUMNS":{ 
                            "MCR_COLUMN_1":"XYZ1",
                             "MCR_COLUMN_2":"XYZ2"
                        }
            }
        ]
}

CASE-2) If MCR value does not exist then it appends a new JSON object into the root array. for eg: if i want to append

{"MCR":"MCR_3",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"UVW1",
                           "MCR_COLUMN_2":"UVW2"
                        }
            }

then updated document should be

{ 
 "root":[{"MCR":"MCR_1",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"ABC1",
                           "MCR_COLUMN_2":"ABC2"
                        }
            },
         {"MCR":"MCR_2",
          "MCR_COLUMNS":{ 
                            "MCR_COLUMN_1":"XYZ1",
                            "MCR_COLUMN_2":"XYZ2"
                        }
            },
         {"MCR":"MCR_3",
          "MCR_COLUMNS":{ 
                           "MCR_COLUMN_1":"UVW1",
                           "MCR_COLUMN_2":"UVW2"
                        }
            }
        ]
}

I had tried JSON_mergepatch and JSON_Transform but case-1 I’m not able to achieve. And since I’ll not have before-hand knowledge whether MCR is already present or not, I just can not only right solution for case-2.
Any help or suggestion will be very much appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In addition to @p3consulting's answer, to insert into a particular array element only(here in this case 'MCR_1') further conditions can be applied to INSERT.

    update test_js 
set json_value = json_transform( json_value, INSERT '$.root[*]?(@.MCR=="MCR_1").MCR_COLUMNS.MCR_COLUMN_4' = 'ABC4') ) 
where json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")') ;

  2. 0

    To check if the MCR value exists:

    WHERE json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")')

    To add an item to MCR_COLUMNS

    update test_js
set json_value = json_transform(
    json_value,
    INSERT '$.root.MCR_COLUMNS.MCR_COLUMN_3' = 'ABC3'
)
where json_exists(json_value, '$?(@.root[*].MCR == "MCR_1")')
;

    To add an item to root array:

    update test_js
set json_value = json_transform(
    json_value,
    APPEND '$.root' = '{"MCR":"MCR_3", "MCR_COLUMNS":{  "MCR_COLUMN_1":"UVW1", "MCR_COLUMN_2":"UVW2" } }' FORMAT JSON
)
where not json_exists(json_value, '$?(@.root[*].MCR == "MCR_3")')
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search