Manipulate JSON data using JSON_TRANSFORM

The JSON_TRANSFORM function was introduced from Oracle database 21c to simplify the modification of JSON data . 

JSON_TRANSFORM is a very useful method in Oracle for manipulating JSON data by using multiple operations to

• set
• insert
• append
• update
• remove
• rename
• replace
• keep
• multi operator json_transform select/update

JSON data for specific tags and elements you want to modify.

In addition to these common data manipulation operations JSON_TRANSFORM can also be used to copy, minus, intersect, union, sort, merge JSON data. It has been enhanced with more operations, conditional controls, and richer path support in later versions.

Sample JSON data for JSON_TRANSFORM

{
  "name": "Novak Djokovic",
  "age": "38",
  "hobbies": ["tennis", "hiking", "swimming"],
  "address": {
    "street": "123 Main St",
    "city": "Anytown"
  },
  "isStudent": false,
  "height": "6.1"
}

JSON_TRANSFORM with SET to modify an existing tag value

When selecting the data I would like to manipulate some JSON values.

Exampe: Change Novak Djokovic to Novak Djokovic The Great.

select json_transform(PLAYER_DATA,
                      set '$.name' = 'Novak Djokovic The Great'
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

Output is

{
    "name": "Novak Djokovic The Great",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming"
    ],
    "address": {
        "street": "123 Main St",
        "city": "Anytown"
    },
    "isStudent": false,
    "height": "6.1"
}

To manipulate data in the select we make use of JSON_TRANFORM. 

To filter data from the table PLAYER_DATA column, which has JSON format we make use of JSON_VALUE and give the tag value to be filtered.

If I want to persist this data in the database I simply use JSON_TRANSFORM in an update statement as follows.

UPDATE TENNIS_STATS
SET PLAYER_DATA = json_transform(PLAYER_DATA,
                      set '$.name' = 'Novak Djokovic The Great'
                      returning clob pretty)
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

JSON_TRANSFORM with SET to add a new element to the JSON

The SET operation will amend an element which is not present in the JSON.

For example when changing the name element, I want to SET the update timestamp to the JSON.

I can write the JSON_TRANSFORM with the SET operation as below

select json_transform(PLAYER_DATA,
                      set '$.name' = 'Novak Djokovic',
                      '$.updated_date' = systimestamp
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic The Great';

The output JSON will have the updated_date element at the end of the JSON.

{
  "name" : "Novak Djokovic",
  "age" : "38",
  "hobbies" :
  [
    "tennis",
    "hiking",
    "swimming"
  ],
  "address" :
  {
    "street" : "123 Main St",
    "city" : "Anytown"
  },
  "isStudent" : false,
  "height" : "6.1",
  "updated_date" : "2025-05-30T14:56:19.457000+05:30"
}

JSON_TRANSFORM with SET to append complex JSON object types to the JSON

We can add JSON objects to an existing JSON using the JSON constructor or FORMAT JSON syntax.

select json_transform(PLAYER_DATA,
                      set '$.personal_info' = json('{"marital_status":"married","children":"2"}')
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

Instead of casting the RHS to JSON type we can also use format json as below

select json_transform(PLAYER_DATA,
                      set '$.personal_info' = '{"marital_status":"married","children":"2"}' format json
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

In both cases, the output JSON will have the formatted JSON object appended at the end of the JSON.

{
  "name" : "Novak Djokovic",
  "age" : "38",
  "hobbies" :
  [
    "tennis",
    "hiking",
    "swimming"
  ],
  "address" :
  {
    "street" : "123 Main St",
    "city" : "Anytown"
  },
  "isStudent" : false,
  "height" : "6.1",
  "personal_info" :
  {
    "marital_status" : "married",
    "children" : "2"
  }
}

JSON_TRANSFORM to append elements to an array in JSON, I will use the SET operation with APPEND as below.

Example - Mr Novak Djokovic has added hockey to his list of hobbies. 

select json_transform(PLAYER_DATA,
                      append '$.hobbies' = 'hockey'
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

This will append hockey at the end of the hobby array

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": {
        "street": "123 Main St",
        "city": "Anytown"
    },
    "isStudent": false,
    "height": "6.1"
}

We can also append objects to an array using the JSON clause to typecast the newly added object.

Example - We append a new residential address for Novak Djokovic in the address array as follows

select json_transform(PLAYER_DATA,
                      append '$.address' = JSON('{"street": "XXX MoonStreet 2", "city": "SpaceXY"}')
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output shows the new address details appended to the end of the address object array

{
  "name" : "Novak Djokovic",
  "age" : "38",
  "hobbies" :
  [
    "tennis",
    "hiking",
    "swimming",
    "hockey"
  ],
  "address" :
  [
    {
      "street" : "123 Main St",
      "city" : "Anytown"
    },
    {
      "street" : "XXX MoonStreet",
      "city" : "SpaceX"
    },
    {
      "street" : "XXX MoonStreet 2",
      "city" : "SpaceXY"
    }
  ],
  "isStudent" : false,
  "height" : "6.1"
}

In Oracle 21c there is a new function JSON_ARRAY_APPEND that has been added, which will add a new element to the end of a JSON array. Since I have Oracle 19c installed, am unable to test this feature, but including a sample block for the same

DECLARE
    json_data CLOB := '{"numbers": [1, 2, 3]}';
    updated_json CLOB;
BEGIN
    -- Append the value 4 to the 'numbers' array
    SELECT JSON_ARRAY_APPEND(json_data, '$.numbers', 4)
    INTO updated_json
    FROM dual;

    DBMS_OUTPUT.PUT_LINE(updated_json);
END;
/

If you try to run this block in Oracle 19c version you will get the below error

PL/SQL: ORA-00904: "JSON_ARRAY_APPEND": invalid identifier

JSON_TRANSFORM to modify an object type in an ARRAY with the SET operator we can do it as follows

The original JSON

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
	
    "address": [
	{
        "street": "123 Main St",
        "city": "Anytown"
    },
	{
        "street": "XXX MoonStreet",
        "city": "SpaceX"
    }
	],
    "isStudent": false,
    "height": "6.1"
}

Mr Djovokic has changed his residential address on Main Street. 

Since this is the first object element in the array, we filter it with the array tag $.address[0].street to indicate the 0th element in the array. 

We can use the SET operator for this change as follows

select json_transform(PLAYER_DATA,
                      set '$.address[0].street' = '555 Main St'
                      returning clob pretty) as data
from TENNIS_STATS
where  id = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic'
AND JSON_VALUE(PLAYER_DATA,'$.address[0].street') = '123 Main St';

The modified street addess will now reflect in the output below

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
	
    "address": [
	{
        "street": "555 Main St",
        "city": "Anytown"
    },
	{
        "street": "XXX MoonStreet",
        "city": "SpaceX"
    }
	],
    "isStudent": false,
    "height": "6.1"
}

JSON_TRANSFORM using the INSERT clause

To add the brands of shoes to the JSON block for Novak. This add the array tag for the shoes to the end of the JSON.

SELECT json_transform(PLAYER_DATA,
                      INSERT '$.shoes' = JSON('["Raebok", "Nike", "Puma"]')
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output for this is as below

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": [
        {
            "street": "123 Main St",
            "city": "Anytown"
        },
        {
            "street": "XXX MoonStreet",
            "city": "SpaceX"
        }
    ],
    "isStudent": false,
    "height": "6.1",
    "shoes": [
        "Raebok",
        "Nike",
        "Puma"
    ]
}

By default INSERT operator will add the tag to the end of the JSON.

If the JSON has an array tag, another element can be added to that array by specifying the position in the array in which the element has to be added.

Mr Djokovic has purchased another house on Moon Street. So we add that house to the address array in the second position as follows.

SELECT json_transform(PLAYER_DATA,
                      INSERT '$.address[1]' = JSON('{"street": "XXX MoonStreet 2", "city": "SpaceXY"}')
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output shows the new address added to the second position.

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": [
        {
            "street": "123 Main St",
            "city": "Anytown"
        },
        {
            "street": "XXX MoonStreet 2",
            "city": "SpaceXY"
        },
        {
            "street": "XXX MoonStreet",
            "city": "SpaceX"
        }
    ],
    "isStudent": false,
    "height": "6.1"
}

To add an element to an array of items (i.e. - not an array of objects of name-value tags) the syntax is a bit different. Mr Novak has just purchased a pair of Adidas shoes, and it is at number 1 position in his choice of shoes. 

Below is the JSON_TRANSFORM INSERT operation for the same

SELECT json_transform(PLAYER_DATA,
                      INSERT '$.shoes[0]' = JSON('"Adidas"')
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The ouput of this shows Adidas in position in the array of shoes

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": [
        {
            "street": "123 Main St",
            "city": "Anytown"
        },
        {
            "street": "XXX MoonStreet",
            "city": "SpaceX"
        }
    ],
    "isStudent": false,
    "height": "6.1",
    "shoes": [
        "Adidas",
        "Raebok",
        "Nike",
        "Puma"
    ]
}

If a JSON tag exists, the INSERT operation will give an error

ORA-40763: existing value in JSON_TRANSFORM ()

We can bypass this error by using the following handlers 

•   ERROR ON EXISTING (default), IGNORE ON EXISTING, REPLACE ON EXISTING 
•   NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL, REMOVE ON NULL 

 For example, we want to insert the tag for age for Djokovic. If the tag exists we want to replace the value with the new value.

SELECT json_transform(PLAYER_DATA,
                      INSERT  '$.age' = '37' REPLACE ON EXISTING
                      returning clob pretty) 
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

This will replace the age from 38 to 37.

JSON_TRANSFORM using the REMOVE clause to remove a tag element for a JSON

 Since it is a well know fact that Novak is no longer a student, but the Master of the game, we can revoe the isStudent tag as follows

SELECT json_transform(PLAYER_DATA,
                      REMOVE '$.isStudent' 
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

If we want to persist this data to the database we use the JSON_TRANSFORM update clause as below

UPDATE TENNIS_STATS
SET PLAYER_DATA = json_transform(PLAYER_DATA,
                      REMOVE '$.isStudent'
                      returning clob pretty)
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The final JSON will be without the isStudent tag

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": [
        {
            "street": "123 Main St",
            "city": "Anytown"
        },
        {
            "street": "XXX MoonStreet",
            "city": "SpaceX"
        }
    ],
    "height": "6.1",
    "shoes": [
        "Raebok",
        "Nike",
        "Puma"
    ]
}

JSON_TRANSFORM with RENAME to rename a JSON tag name

To rename a tag we can use JSON_TRANSFORM with the rename clause. To change the tag street to street name, we can run the query as follows

Using RENAME in JSON_TRANSFOM SELECT

SELECT json_transform(PLAYER_DATA,
                      RENAME '$.address.street' = 'streetname' 
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

Using RENAME in JSON_TRANSFOM UPDATE

UPDATE TENNIS_STATS
SET PLAYER_DATA = json_transform(PLAYER_DATA,
                      RENAME '$.address.street' = 'streetname'
                      returning clob pretty)
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output is below

{
  "name" : "Novak Djokovic",
  "age" : "38",
  "hobbies" :
  [
    "tennis",
    "hiking",
    "swimming",
    "hockey"
  ],
  "address" :
  [
    {
      "city" : "Anytown",
      "streetname" : "123 Main St"
    },
    {
      "city" : "SpaceX",
      "streetname" : "XXX MoonStreet"
    }
  ],
  "height" : "6.1",
  "shoes" :
  [
    "Raebok",
    "Nike",
    "Puma"
  ]
}

You can also update individual array tags by specifying the tag array element. I want to rename only the first tag in the array to street.

SELECT json_transform(PLAYER_DATA,
                      RENAME '$.address[0].streetname' = 'street' 
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

UPDATE TENNIS_STATS
SET PLAYER_DATA = json_transform(PLAYER_DATA,
                      RENAME '$.address[0].streetname' = 'street'
                      returning clob pretty)
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output shows only the first array tag renamed to street

{
    "name": "Novak Djokovic",
    "age": "38",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": [
        {
            "city": "Anytown",
            "street": "123 Main St"
        },
        {
            "city": "SpaceX",
            "streetname": "XXX MoonStreet"
        }
    ],
    "height": "6.1",
    "shoes": [
        "Raebok",
        "Nike",
        "Puma"
    ]
}  

JSON_TRANSFORM with REPLACE to update a JSON tag value

To update a JSON tag value, we can also use the replace clause.

Using REPLACE in JSON_TRANSFOM SELECT

SELECT json_transform(PLAYER_DATA,
                      REPLACE '$.address[0].streetname' = '123 Main Street Avenue' 
                      returning clob pretty)
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

Using REPLACE in JSON_TRANSFOM UPDATE

UPDATE TENNIS_STATS
SET PLAYER_DATA = json_transform(PLAYER_DATA,
                      REPLACE '$.address[0].streetname' = '123 Main Street Avenue'
                      returning clob pretty)
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output is below. streetname "123 Main St" is updated to "123 Main Street Avenue"

{
  "name" : "Novak Djokovic",
  "age" : "38",
  "hobbies" :
  [
    "tennis",
    "hiking",
    "swimming",
    "hockey"
  ],
  "address" :
  [
    {
      "city" : "Anytown",
      "streetname" : "123 Main Street Avenue"
    },
    {
      "city" : "SpaceX",
      "streetname" : "XXX MoonStreet"
    }
  ],
  "height" : "6.1",
  "shoes" :
  [
    "Raebok",
    "Nike",
    "Puma"
  ]
}  

JSON_TRANSFORM with KEEP

The keep operation is used to remove all elements except those included in the comma separated list of the KEEP clause. 

In the above JSON example, if I want to keep only the player's personal information like name, age, height, I use them as comma separated in the KEEP clause.

Example

select json_transform(PLAYER_DATA,
                      KEEP '$.name', '$.age', '$.height'
                      returning clob pretty) as data
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The output of this query is as below

{
  "name" : "Novak Djokovic",
  "age" : "38",
  "height" : "6.1"
}

We can also combine multiple operators in a single JSON_TRANSFORM operation. 

Using multiple operators in JSON_TRANSFOM SELECT

SELECT json_transform(PLAYER_DATA,
                      REPLACE '$.address[0].streetname' = '123 Main Street Avenue',
                      RENAME '$.address[1].streetname' = 'street',
                      REMOVE '$.isStudent',
                      INSERT  '$.age' = '35' REPLACE ON EXISTING,
                      INSERT '$.shoes[3]' = JSON('"Bata"'),
                      append '$.address' = JSON('{"street": "XXX MoonStreet, Mars", "city": "SpaceXXX"}'),
                      set '$.personal_info' = '{"marital_status":"married","children":"2"}' format json,
                      set '$.updated_date' = systimestamp
                      returning clob pretty) new_json
FROM TENNIS_STATS
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

Using multiple operators in JSON_TRANSFOM UPDATE

UPDATE TENNIS_STATS
SET PLAYER_DATA = json_transform(PLAYER_DATA,
                      REPLACE '$.address[0].streetname' = '123 Main Street Avenue',
                      RENAME '$.address[1].streetname' = 'street',
                      REMOVE '$.isStudent',
                      INSERT  '$.age' = '35' REPLACE ON EXISTING,
                      INSERT '$.shoes[3]' = JSON('"Bata"'),
                      append '$.address' = JSON('{"street": "XXX MoonStreet, Mars", "city": "SpaceXXX"}'),
                      set '$.personal_info' = '{"marital_status":"married","children":"2"}' format json,
                      set '$.updated_date' = systimestamp
                      returning json)
WHERE  ID = 10001
AND JSON_VALUE(PLAYER_DATA,'$.name') = 'Novak Djokovic';

The updated JSON is as below

{
    "name": "Novak Djokovic",
    "age": "35",
    "hobbies": [
        "tennis",
        "hiking",
        "swimming",
        "hockey"
    ],
    "address": [
        {
            "city": "Anytown",
            "streetname": "123 Main Street Avenue"
        },
        {
            "city": "SpaceX",
            "street": "XXX MoonStreet"
        },
        {
            "street": "XXX MoonStreet, Mars",
            "city": "SpaceXXX"
        }
    ],
    "height": "6.1",
    "shoes": [
        "Raebok",
        "Nike",
        "Puma",
        "Bata"
    ],
    "personal_info": {
        "marital_status": "married",
        "children": "2"
    },
    "updated_date": "2025-06-14T18:07:45.542000+05:30"
}  

References

JSON_TRANSFORM in Oracle Database 21c 

JSON_TRANSFORM Enhancements in Oracle Database 23ai

God's Word for the day

Do not abandon old friends, 

  for new ones cannot equal them

A new friend is like new wine,

  when it has aged you can drink it with pleasure.

Do not envy the success of sinners,

  for you do not know what their end will be like.

Do not delight in what pleases the ungodly,

  remember they  will not be held guiltless all their lives.

Sirach 9:10-12