Retrieve json data from oracle sql-CodePudding

This is my json data in one of the oracle sql columns "jsoncol" in a table named "jsontable"

{
  "Company": [
    {
      "Info": {
        "Address": "123"
      },
      "Name": "ABC",
      "Id": 999
    },
    {
      "Info": {
        "Address": "456"
      },
      "Name": "XYZ",
      "Id": 888
    }
  ]
}

I am looking for an UPDATE query to update all the value of "Name" with a new value based on a particular "Id" value. Thanks in advance

CodePudding user response：

From Oracle 19, you can use JSON_MERGEPATCH:

UPDATE jsontable j
SET   jsoncol = JSON_MERGEPATCH(
                  jsoncol,
                  (
                    SELECT JSON_OBJECT(
                             KEY 'Company'
                             VALUE JSON_ARRAYAGG(
                                     CASE id
                                     WHEN 999
                                     THEN JSON_MERGEPATCH(
                                            json,
                                            '{"Name":"DEF"}'
                                          )
                                     ELSE json
                                     END
                                     FORMAT JSON
                                     RETURNING CLOB
                                   )
                             FORMAT JSON
                             RETURNING CLOB
                             )
                    FROM   jsontable jt
                           CROSS APPLY JSON_TABLE(
                             jt.jsoncol,
                             '$.Company[*]'
                             COLUMNS(
                               json VARCHAR2(4000) FORMAT JSON PATH '$',
                               id   NUMBER PATH '$.Id'
                             )
                           )
                    WHERE  jt.ROWID = j.ROWID
                  )
                )

Which, for the sample data:

CREATE TABLE jsontable (
  jsoncol CLOB CHECK (jsoncol IS JSON)
);

INSERT INTO jsontable (jsoncol)
VALUES ('{
  "Company": [
    {
      "Info": {
        "Address": "123"
      },
      "Name": "ABC",
      "Id": 999
    },
    {
      "Info": {
        "Address": "456"
      },
      "Name": "XYZ",
      "Id": 888
    }
  ]
}');

Then after the UPDATE, the table contains:

JSONCOL

{"Company":[{"Info":{"Address":"123"},"Name":"DEF","Id":999},{"Info":{"Address":"456"},"Name":"XYZ","Id":888}]}

JSONCOL
{"Company":[{"Info":{"Address":"123"},"Name":"DEF","Id":999},{"Info":{"Address":"456"},"Name":"XYZ","Id":888}]}

db<>fiddle here

CodePudding user response：

You can use REPLACE() within JSON_TABLE() function in order to update the value of the Name(from ABC to DEF) for a specific Id value(999) such as

UPDATE jsontable jt0
   SET jsoncol = ( SELECT REPLACE(jsoncol,jt.name,'DEF') 
                     FROM jsontable j,
                          JSON_TABLE(jsoncol,
                                    '$' COLUMNS(NESTED PATH '$.Company[*]'
                                                COLUMNS(
                                                         name VARCHAR2 PATH '$.Name',
                                                         id   INT      PATH '$.Id' 
                                                        )
                                                )
                                     ) jt
                    WHERE jt.id = 999
                      AND j.id = jt0.id )

for the DB version prior to 19 provided that the identity values(id) of the table and of the JSON values are unique throughout the table and each individual JSON value, respectively.

Demo