8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 26ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23 » Here

JSON_TRANSFORM Enhancements in Oracle Database 23ai/26ai

The JSON_TRANSFORM function was introduced in Oracle database 21c to simplify the modification of JSON data. In Oracle 23ai/26ai the JSON_TRANSFORM function has lots of new operations, conditional control and richer path support.

This article assumes you are familiar with the JSON_TRANSFORM functionality in Oracle 21c, demonstrated here. The examples in this article will use some of the operations demonstrated in that article.

Thanks to Loic Lefevre for his help with the syntax of some of the set operations.

Related articles.

Setup

The examples in this article use the following table. We are using the JSON data type, introduced in Oracle database 21c. We could have used any supported data type, including VARCHAR2, CLOB or BLOB.

drop table if exists t1 purge;

create table t1 (
  id         number,
  json_data  json,
  constraint t1_pk primary key (id)
);

We insert two rows of test data.

insert into t1 (id, json_data) values
  (1, json('{"fruit":"apple","quantity":10}')),
  (2, json('{"produce":[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]}'));
commit;

From the output below we can see row 1 contains a flat JSON object, and row 2 contains an array of JSON objects.

set linesize 100 pagesize 1000 long 1000000
column data format a60

select id, json_serialize(json_data pretty) as data
from   t1;

        ID DATA
---------- ------------------------------------------------------------
         1 {
             "fruit" : "apple",
             "quantity" : 10
           }

         2 {
             "produce" :
             [
               {
                 "fruit" : "apple",
                 "quantity" : 10
               },
               {
                 "fruit" : "orange",
                 "quantity" : 15
               }
             ]
           }

SQL>

PREPEND Operation

The PREPEND operation adds a new element to the start of an array. It's similar to the APPEND operation, but adds the element to the other side of the array. In the following example we PREPEND and entry for "banana" into the array.

select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"banana","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "banana",
      "quantity" : 20
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

This is similar to using the INSERT operation with the [0] position.

select json_transform(json_data,
                      insert '$.produce[0]' = json('{"fruit":"banana","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "banana",
      "quantity" : 20
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

The default behaviour of the PREPEND operation can be altered using the following handlers.

COPY Operation

The COPY operation replaces the contents of a JSON array with the value provided. In the following example we replace the contents of the array with an entry for "lime".

select json_transform(json_data,
                      copy '$.produce' = json('{"fruit":"lime","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "lime",
      "quantity" : 20
    }
  ]
}


SQL>

The default behaviour of the COPY operation can be altered using the following handlers.

MINUS Operation

The MINUS operation removes all elements from an array that match those listed in the right-hand side. It also removes duplicates. In the following example we use add a duplicate entry for "orange" and use MINUS to remove the "apple" entry. Not only has the "apple" entry been removed, but the duplicate of the "orange" entry has also been removed.

select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"orange","quantity":15}'),
                      minus '$.produce' = json('{"fruit":"apple","quantity":10}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

In this example we use an array of items for the minus operation.

select json_transform(json_data,
                      minus '$.produce' = path '$temp[*]'
                      passing json('[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
  ]
}


SQL>

The default behaviour of the MINUS operation can be altered using the following handlers.

INTERSECT Operation

The INTERSECT operation removes all elements from an array except those that match the right-hand side. It also removes any duplicates. In the following example we add a duplicate "apple" entry, then perform an INTERSECT with "apple". Not only has the "orange" entry been removed, but the duplicate of the "apple" entry has also been removed.

select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"apple","quantity":10}'),
                      intersect '$.produce' = json('{"fruit":"apple","quantity":10}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    }
  ]
}


SQL>

In this example we use an array of items for the intersect operation.

select json_transform(json_data,
                      intersect '$.produce' = path '$temp[*]'
                      passing json('[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

The default behaviour of the INTERSECT operation can be altered using the following handlers.

UNION Operation

The UNION operation adds missing elements to the array, and removed duplicates. In the following example we add a duplicate "apple" entry, the UNION the entry for "lime". We can see the new entry has been added to the array, but the duplicate entry for "apple" has been removed.

select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"apple","quantity":10}'),
                      union '$.produce' = json('{"fruit":"lime","quantity":12}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "lime",
      "quantity" : 12
    }
  ]
}


SQL>

In this example we use an array of items for the union operation.

select json_transform(json_data,
                      union '$.produce' = path '$temp[*]'
                      passing json('[{"fruit":"lime","quantity":12},{"fruit":"lemon","quantity":20}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "lemon",
      "quantity" : 20
    },
    {
      "fruit" : "lime",
      "quantity" : 12
    }
  ]
}


SQL>

The default behaviour of the UNION operation can be altered using the following handlers.

SORT Operation

We PREPEND an entry for "kiwi", so it is at the start of the array, then sort the produce array using the SORT operation. We don't specify a sorting element, so it sorts by the first element.

select json_transform(json_data,
                      prepend '$.produce' = JSON('{"fruit":"kiwi","quantity":30}'),
                      sort '$.produce'
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "kiwi",
      "quantity" : 30
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

We can also do a descending order.

select json_transform(json_data,
                      prepend '$.produce' = JSON('{"fruit":"kiwi","quantity":30}'),
                      sort '$.produce' desc
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "kiwi",
      "quantity" : 30
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    }
  ]
}


SQL>

To identify the specific element to order by, use the ORDER BY clause. In this case we order by the descending quantity value.

select json_transform(json_data,
                      prepend '$.produce' = JSON('{"fruit":"kiwi","quantity":30}'),
                      sort '$.produce' order by '$.quantity' desc
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "kiwi",
      "quantity" : 30
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    }
  ]
}


SQL>

The default behaviour of the SORT operation can be altered using the following handlers.

MERGE Operation

The MERGE operation merges the specified fields into an existing object.

select json_transform(json_data,
                      merge '$' = json('{"weight":20, "lifespan":5}')
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "weight" : 20,
  "lifespan" : 5
}

In this example we use an array of items for the merge operation.

select json_transform(json_data,
                      merge '$' = path '$temp[*]'
                      passing json('[{"weight":20}, {"lifespan":5}, {"food miles":1000}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "weight" : 20,
  "lifespan" : 5,
  "food miles" : 1000
}


SQL>

The default behaviour of the MERGE operation can be altered using the following handlers.

NESTED PATH Operation

The NESTED PATH or NESTED operation allows us to define a specific path in our document, which can be referenced by subsequent operations using the "@" prefix. The "$" is still available to reference the root of the document in the right-hand side. In the following example we add 5 to the quantity of all items, and add a new entry called "weight". Notice the SET and INSERT operations are in parenthesis after the NESTED PATH definition, and we use "@" to reference the path. We have performed a mathematical operation on the right-hand side of the SET operation using PATH to reference an item value.

select json_transform(json_data,
                      nested path '$.produce[*]'
                        (set '@.quantity' = path '@.quantity + 5',
                         insert '@.weight' = 20)
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 15,
      "weight" : 20
    },
    {
      "fruit" : "orange",
      "quantity" : 20,
      "weight" : 20
    }
  ]
}


SQL>

In this example we use the SET operation to set the value of "$temp", which we later refer to using PATH. This is similar to the union example shown previously, but this time using SET and PATH instead of USING to pass the variable value.

select json_transform(json_data,
                      set '$temp' = json('[{"fruit":"lime","quantity":12},{"fruit":"lemon","quantity":24},{"fruit":"apple","quantity":10}]'),
                      union '$.produce' = path '$temp[*]'
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "lemon",
      "quantity" : 24
    },
    {
      "fruit" : "lime",
      "quantity" : 12
    }
  ]
}


SQL>

CASE Operation

The CASE operation allows us to make operations conditional. In the following example we combine NESTED PATH and CASE to perform some conditional processing of our JSON. For the "apple" element we assign a weight of 10 and a lifespan of 5. For the "orange" element we assign a weight of 12. For anything else we assign a weight of null. The syntax is similar to a searched CASE expression in SQL.

select json_transform(json_data,
                      nested path '$.produce[*]' (
                        case
                          when '@.fruit == "apple"' then (
                            insert '@.weight' = 10,
                            insert '@.lifespan' = 5
                          )
                          when '@.fruit == "orange"' then (
                            insert '@.weight' = 12
                          )
                          else (
                            insert '@.weight' = null
                          )
                        end
                      )
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10,
      "weight" : 10,
      "lifespan" : 5
    },
    {
      "fruit" : "orange",
      "quantity" : 15,
      "weight" : 12
    }
  ]
}


SQL>

Arithmetic Operations

The right-hand side expression can include arithmetic operations. In the following example we add a new item called "weight", and set the "total_weight" to "quantity" * "weight", using PATH to access the item values.

select json_transform(json_data,
                      nested path '$.produce[*]'
                        (set '@.weight' = 10,
                         set '@.total_weight' = path '@.quantity * @.weight')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10,
      "weight" : 10,
      "total_weight" : 100
    },
    {
      "fruit" : "orange",
      "quantity" : 15,
      "weight" : 10,
      "total_weight" : 150
    }
  ]
}


SQL>

Aggregate Functions

We can use aggregate functions against the contents of the array to produce aggregated summary information.

select json_transform(json_data,
                      set '$.count_entries' = path '@.produce[*].count()',
                      set '$.sum_quantity' = path '@.produce[*].quantity.sum()',
                      set '$.avg_quantity' = path '@.produce[*].quantity.avg()',
                      set '$.min_quantity' = path '@.produce[*].quantity.min()',
                      set '$.max_quantity' = path '@.produce[*].quantity.max()'
                      returning clob pretty) as data
from   t1
where  id = 2;
DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ],
  "count_entries" : 2,
  "sum_quantity" : 25,
  "avg_quantity" : 12.5,
  "min_quantity" : 15,
  "max_quantity" : 15
}


SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.