Tuesday, February 23, 2021

JSON_TRANSFORM in Oracle Database 21c

Oracle Database 21c, Oracle Database Tutorial and Material, Oracle Database Learning, Oracle Database Career, Oracle Database Preparation

The JSON_TRANSFORM function has more functionality, and is easier to use. 

◉ 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 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}'));

insert into t1 (id, json_data) values (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>

◉ SET Operation

The following SET operation updates the quantity value from 10 to 20. The output is returned as a CLOB using the RETURNING clause, with the PRETTY keyword to pretty-printed the output.

select json_transform(json_data,

                      set '$.quantity' = 20

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

--------------------------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 20

}

SQL>

If we use SET to amend an element that isn't already present, the default operation is to create it. Here we use the SET operation to add a new element called "updated_date".

select json_transform(json_data,

                      set '$.updated_date' = systimestamp

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

--------------------------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10,

  "updated_date" : "2020-12-20T15:35:36.286485Z"

}

SQL>

We can use complex JSON object values using the JSON constructor or FORMAT JSON. Without these the value would just be added as an escaped string, rather than a JSON object. Here we show both methods to add a new element called "additional_info", which has a JSON object as its value.

select json_transform(json_data,

                      set '$.additional_info' = json('{"colour":"red","size":"large"}')

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10,

  "additional_info" :

  {

    "colour" : "red",

    "size" : "large"

  }

}

SQL>

select json_transform(json_data,

                      set '$.additional_info' = '{"colour":"red","size":"large"}' format json

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10,

  "additional_info" :

  {

    "colour" : "red",

    "size" : "large"

  }

}

SQL>

The SET operation works equally well for arrays, as shown in the following examples.

-- Set quantity to 20 for first item in the produce array.

select json_transform(json_data,

                      set '$.produce[0].quantity' = 20

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 20

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    }

  ]

}

SQL>

-- Add the updated_date element to the first item in the produce array.

select json_transform(json_data,

                      set '$.produce[0].updated_date' = systimestamp

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10,

      "updated_date" : "2021-01-30T08:10:38.368785Z"

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    }

  ]

}

SQL>

-- Add the updated_date element to all items in the produce array.

select json_transform(json_data,

                      set '$.produce[*].updated_date' = systimestamp

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10,

      "updated_date" : "2021-01-30T08:10:55.828378Z"

    },

    {

      "fruit" : "orange",

      "quantity" : 15,

      "updated_date" : "2021-01-30T08:10:55.828378Z"

    }

  ]

}

SQL>

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

◉ REPLACE ON EXISTING (default), ERROR ON EXISTING, IGNORE ON EXISTING

◉ CREATE ON MISSING (default), ERROR ON MISSING, IGNORE ON MISSING

◉ NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL, REMOVE ON NULL

For example, to raise an error if we try to amend an item that isn't present, we would do the following.

select json_transform(json_data,

                      set '$.updated_date' = systimestamp error on missing

                      returning clob pretty) as data

from   t1

where  id = 1;

Error report -

ORA-40762: missing value in JSON_TRANSFORM ()

SQL>

Many of the following operations can be replicated using the SET operation with the correct handlers.

◉ INSERT Operation

The INSERT operation is used to add a new element which doesn't already exist. Here we use the INSERT operation to add a new element called "updated_date".

select json_transform(json_data,

                      insert '$.updated_date' = systimestamp

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10,

  "updated_date" : "2021-01-05T08:44:58.406618Z"

}

SQL>

This differs from the default SET operation, in that is produces an error if the item already exists. Here we use the INSERT operation to add a new element called "fruit". Since this element already exists, it produces an error.

select json_transform(json_data,

                      insert '$.fruit' = 'orange'

                      returning clob pretty) as data

from   t1

where  id = 1;

Error report -

ORA-40763: existing value in JSON_TRANSFORM ()

SQL>

So it's similar to using the SET operation with the ERROR ON EXISTING handler.

select json_transform(json_data,

                      set '$.fruit' = 'orange' error on existing

                      returning clob pretty) as data

from   t1

where  id = 1;

ORA-40763: existing value in JSON_TRANSFORM ()

SQL>

The INSERT operation can also be used to add an element to an array. In these examples we add a new "fruit" to different positions in the "produce" array. Notice this position is specified in the search path.

-- Added to first position in the array.

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>

-- Added to second position in the array.

select json_transform(json_data,

                      insert '$.produce[1]' = JSON('{"fruit":"banana","quantity":20}')

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10

    },

    {

      "fruit" : "banana",

      "quantity" : 20

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    }

  ]

}

SQL>

-- Appended to the end of the array.

select json_transform(json_data,

                      insert '$.produce[last+1]' = JSON('{"fruit":"banana","quantity":20}')

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    },

    {

      "fruit" : "banana",

      "quantity" : 20

    }

  ]

}

SQL>

The default behaviour of the INSERT operation can be altered 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

◉ APPEND Operation

The APPEND operation is used to add a new element to the end of an array. Here we use the APPEND operation to add a new "fruit" at the end of the "produce" array.

select json_transform(json_data,

                      append '$.produce' = JSON('{"fruit":"banana","quantity":20}')

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    },

    {

      "fruit" : "banana",

      "quantity" : 20

    }

  ]

}

SQL>

This is similar to using the INSERT operation with the [last+1] position.

select json_transform(json_data,

                      insert '$.produce[last+1]' = JSON('{"fruit":"banana","quantity":20}')

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    },

    {

      "fruit" : "banana",

      "quantity" : 20

    }

  ]

}

SQL>

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

◉ ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING

◉ NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL

◉ REMOVE Operation

The REMOVE operation is used to delete an element from an object or an array. Here we use the REMOVE operation to delete the "quantity" element from an object, from an object in an array, and remove a "fruit" from the "produce" array.

-- Remove an element from an object.

select json_transform(json_data,

                      remove '$.quantity'

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple"

}

SQL>

-- Remove an element from an object in an array.

select json_transform(json_data,

                      remove '$.produce[0].quantity'

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple"

    },

    {

      "fruit" : "orange",

      "quantity" : 15

    }

  ]

}

-- Remove an element from the "produce" array.

select json_transform(json_data,

                      remove '$.produce[1]'

                      returning clob pretty) as data

from   t1

where  id = 2;

DATA

------------------------------------------------------------

{

  "produce" :

  [

    {

      "fruit" : "apple",

      "quantity" : 10

    }

  ]

}

SQL>

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

◉ IGNORE ON MISSING (default), ERROR ON MISSING

◉ RENAME Operation

The RENAME operation is used to rename an element. Here we use the RENAME operation to rename the "fruit" element to "fruit_name".

select json_transform(json_data,

                      rename '$.fruit' = 'fruit_name'

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "quantity" : 10,

  "fruit_name" : "apple"

}

SQL>

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

IGNORE ON MISSING (default), ERROR ON MISSING

◉ REPLACE Operation

The REPLACE operation is used to update the value of an element. Here we use the REPLACE operation to update the "quantity" value from 10 to 20.

select json_transform(json_data,

                      replace '$.quantity' = 20

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 20

}

SQL>

If the element doesn't exist the operation is ignored. A new element is not created. Here we use the REPLACE operation to update the "updated_date" value. The "updated_date" element doesn't exist, so no action is taken.

select json_transform(json_data,

                      replace '$.updated_date' = systimestamp

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10

}

SQL>

This is similar to using the SET operation with the IGNORE ON MISSING handler.

select json_transform(json_data,

                      replace '$.updated_date' = systimestamp

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10

}

SQL>

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

◉ IGNORE ON MISSING (default), ERROR ON MISSING, CREATE ON MISSING

◉ NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL, REMOVE ON NULL

◉ KEEP Operation

The KEEP operation is used to remove all elements except those included in the comma-separated list or search paths. Using the "$" search path returns an empty JSON document.

-- Remove everything.

select json_transform(json_data,

                      keep '$'

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

}

SQL>

-- Remove everything except the "fruit" element.

select json_transform(json_data,

                      keep '$.fruit'

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple"

}

SQL>

-- Remove everything except the "fruit" and "quantity" elements (remove nothing).

select json_transform(json_data,

                      keep '$.fruit', '$.quantity'

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "fruit" : "apple",

  "quantity" : 10

}

SQL>

◉ Combining Multiple Operations

Multiple operations can be combined into a single JSON_TRANSFORM call. They are processed in order, and if one operation fails they all fail.

select json_transform(json_data,

                      set '$.created_date' = systimestamp,

                      set '$.updated_date' = systimestamp,

                      rename '$.fruit' = 'fruit_type',

                      replace '$.quantity' = 20

                      returning clob pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "quantity" : 20,

  "created_date" : "2020-12-20T17:25:34.539480Z",

  "updated_date" : "2020-12-20T17:25:34.539480Z",

  "fruit_type" : "apple"

}

SQL>

◉ Direct Updates Using JSON_TRANSFORM

All the examples so far have performed transformations on the fly as part of SELECT statements, but we could just as easily do the transformations as part of an UPDATE statement.

-- Update the data directly in the table.

update t1

set    json_data = json_transform(json_data,

                                  set '$.created_date' = systimestamp,

                                  set '$.updated_date' = systimestamp,

                                  rename '$.fruit' = 'fruit_type',

                                  replace '$.quantity' = 20

                                  returning json)

where  id = 1;

-- Display the updated data.

select json_serialize(json_data pretty) as data

from   t1

where  id = 1;

DATA

------------------------------------------------------------

{

  "quantity" : 20,

  "created_date" : "2020-12-20T17:39:30.811689Z",

  "updated_date" : "2020-12-20T17:39:30.811689Z",

  "fruit_type" : "apple"

}

SQL>

rollback;

◉ RETURNING Clause

Oracle Database 21c, Oracle Database Tutorial and Material, Oracle Database Learning, Oracle Database Career, Oracle Database Preparation
The output of the JSON_TRANSFORM function depends on the input expression. If they input is a JSON data type, the output is also a JSON data type. All other input types result in a VARCHAR2(4000) return value. The RETURNING clause allows the output to be converted to JSON, BLOB, CLOB or VARCHAR2. Most of the examples above use RETURNING CLOB to make the output readable.

◉ PL/SQL Support

There is no PL/SQL support for direct assignments using the JSON_TRANSFORM function. The following attempt results in an error.

set serveroutput on

declare

  l_json_in   varchar2(32767);

  l_json_out  varchar2(32767);

begin

  l_json_in  := '{"fruit":"apple","quantity":10}';


  l_json_out := json_transform(l_json_in,

                               set '$.updated_date' = systimestamp

                               returning varchar2 pretty);

  dbms_output.put_line(l_json_out);

end;

/

Error report -

ORA-06550: line 8, column 36:

PLS-00103: Encountered the symbol "$.updated_date" when expecting one of the following:

   . ( ) , * @ % & | = - + < / > at in is mod remainder not null

   rem returning with => .. <an exponent (**)> <> or != or ~= >=

   <= <> and or default like like2 like4 likec between error ||

   multiset member empty submultiset lax strict without pretty

   ascii true false absent format allow truncate

The symbol "(" was substituted for "$.updated_date" to continue.

An assignment can be made using a SELECT ... INTO ... FROM DUAL statement, as shown here.

set serveroutput on

declare

  l_json_in   varchar2(32767);

  l_json_out  varchar2(32767);

begin

  l_json_in  := '{"fruit":"apple","quantity":10}';

  select json_transform(l_json_in,

                        set '$.updated_date' = systimestamp

                        returning varchar2 pretty)

  into   l_json_out

  from   dual;

  dbms_output.put_line(l_json_out);

end;

/

{

  "fruit" : "apple",

  "quantity" : 10,

  "updated_date" : "2020-12-21T09:44:33.150459Z"

}

PL/SQL procedure successfully completed.

SQL>

Related Posts

0 comments:

Post a Comment