2021 – JSON_TRANSFORM SET Operation

The simple explanation of the JSON_TRANSFORM SET operation is that it sets values for specified JSON keys. For example “set $.KEY1 to 42′ will set the value of the KEY1 to 42. That’s pretty straightforward. The real power is the additional behaviours that can be defined such as:

  • If $.KEY1 is missing then add it
  • IF $.KEY1 is missing then do not add it
  • If $.KEY1 is already there then do not set it

I’ll demonstrate each of these and more using this test data – two rows of data containing an array named mrns with each element in the array having two elements (OS and MRN) per entry.

SELECT JSON_QUERY(patient_data,'$.mrns') mrns
  FROM json_patient;
mrns
-----------------------------------------
[{"OS":1,"MRN":"27"},{"OS":2,"MRN":"28"}]
[{"OS":1,"MRN":"29"},{"OS":2,"MRN":"30"}]

ON MISSING

What should happen when the JSON key does not exist? That is determined by the ON MISSING clause as demonstrated by the following queries using JSON_TRANSFORM to SET two keys:

  • sdate which is set to the current date
  • edate which is set to NULL

And now for the examples…

CREATE ON MISSING

This is the default behaviour which is add and set the keys if they do not exist.

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].sdate' = SYSDATE CREATE ON MISSING,
           SET '$.mrns[*].edate' = NULL    CREATE ON MISSING),
         '$.mrns') added_dates
  FROM json_patient;
added_dates
----------------------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:41:57","edate":null},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:41:57","edate":null}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:41:57","edate":null},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:41:57","edate":null}]

ERROR ON MISSING

This clause throws an Oracle error if the key does not exist.

The error is ORA-40762: missing value in JSON_TRANSFORM ()

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].sdate' = SYSDATE ERROR ON MISSING,
           SET '$.mrns[*].edate' = NULL    ERROR ON MISSING ),
         '$.mnrns') mrns
  FROM json_patient;

IGNORE ON MISSING

This clause simply ignores the operation if the key does not exist. No update or insert is done.

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].sdate' = SYSDATE IGNORE ON MISSING,
           SET '$.mrns[*].edate' = NULL    IGNORE ON MISSING ),
         '$.mrns') ignored_dates
  FROM json_patient;
IGNORED_DATES
------------------------------------------
[{"OS":1,"MRN":"27"},{"OS":2,"MRN":"28"}]
[{"OS":1,"MRN":"29"},{"OS":2,"MRN":"30"}]

That’s it for missing keys. Now lets find out how to handle not-missing (existing) keys.

ON EXISTING

What should happen when the key to be set exists? That is determined the ON EXISTING clause.

Before we demonstrate I’ll updates the records in the DB to include the sdate and edate keys.

-- Add sdate and edate keys to all records
UPDATE JSON_PATIENT
 SET patient_data = JSON_TRANSFORM(patient_data,
                      SET '$.mrns[*].sdate' = SYSDATE,
                      SET '$.mrns[*].edate' = NULL);

And here is the result. Pay close attention to the sdate values. Trust me.

SELECT JSON_QUERY(patient_data,'$.mrns') check_edate
  FROM json_patient;
CHECK_EDATE
----------------------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:47:24","edate":null},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:47:24","edate":null}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:47:24","edate":null},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:47:24","edate":null}]

REPLACE ON EXISTING

This is the default behaviour . It replaces the existing value with the specified one. Note the sdate value changed. I warned you.

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].sdate' = SYSDATE REPLACE ON EXISTING,
           SET '$.mrns[*].edate' = NULL    REPLACE ON EXISTING),
       '$.mrns') diff_sdate
  FROM json_patient;
DIFF_SDATE
----------------------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:51:55","edate":null},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:51:55","edate":null}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:51:55","edate":null},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:51:55","edate":null}]

ERROR ON EXISTING

This clause raises an error if the specified key already exists.

The error is ORA-40763: existing value in JSON_TRANSFORM

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].sdate' = SYSDATE ERROR ON EXISTING,
           SET '$.mrns[*].edate' = NULL    ERROR ON EXISTING ),
         '$.mrns') mrns
  FROM json_patient;

IGNORE ON EXISTING

This clause just ignores the set operation altogether if the key exists. Note the sdate values in the next example. I know you were tracking that right?

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data, 
           SET '$.mrns[*].sdate' = SYSDATE IGNORE ON EXISTING,
           SET '$.mrns[*].edate' = NULL    IGNORE ON EXISTING ),
       '$.mrns') same_sdate
FROM json_patient;
SAME_SDATE
----------------------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:47:24","edate":null},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:47:24","edate":null}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:47:24","edate":null},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:47:24","edate":null}]

It shows the original sdate values because the SET operation was ignored.

ON NULL

This defines the behaviour when the new value evaluates to NULL. To simplify things I’ll just specify NULL directly in the following examples. But first one last modification to the data to set the edate’s.

UPDATE json_patient
 SET patient_data = JSON_TRANSFORM(patient_data,
 SET '$.mrns[*].edate' = SYSDATE + 10 );

-- Query the edates
SELECT JSON_QUERY(patient_data,'$.mrns[*].edate' WITH WRAPPER) just_edate_pls
  FROM json_patient;
JUST_EDATE_PLS
---------------------------------------------
["2021-10-13T21:24:14","2021-10-13T21:24:14"]
["2021-10-13T21:24:14","2021-10-13T21:24:14"]

Now I’ll set the edates to NULL via JSON_TRANSFORM to demonstrate the ON NULL options.

NULL ON NULL

This is the default behaviour that says “If you really want to set the value to NULL then go right ahead”.

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
         SET '$.mrns[*].edate' = NULL NULL ON NULL ),
       '$.mrns') nullz
FROM json_patient;
NULLZ
-----------------------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:47:24","edate":null},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:47:24","edate":null}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:47:24","edate":null},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:47:24","edate":null}]

ERROR ON NULL

This clause specifies that an error be thrown if the value is being set to NULL.

The error is ORA-40803: JSON_TRANSFORM operation called with NULL input

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].edate' = NULL ERROR ON NULL ),
       '$.mrns') mrns
FROM json_patient;

IGNORE ON NULL

This clause just ignores the operation altogether if the new value is NULL.

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].edate' = NULL IGNORE ON NULL ),
       '$.mrns') ignored
FROM json_patient;
IGNORED
---------------------------------------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:47:24","edate":"2021-10-13T21:24:14"},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:47:24","edate":"2021-10-13T21:24:14"}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:47:24","edate":"2021-10-13T21:24:14"},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:47:24","edate":"2021-10-13T21:24:14"}]

Note the edate values are not modified.

REMOVE ON NULL

This clause removes the key altogether if the value is being set to NULL.

SELECT JSON_QUERY(
         JSON_TRANSFORM(patient_data,
           SET '$.mrns[*].edate' = NULL REMOVE ON NULL ),
       '$.mrns') edates_are_gone
FROM json_patient;
EDATES_ARE_GONE
---------------------------------------------------
[{"OS":1,"MRN":"27","sdate":"2021-10-03T03:47:24"},
 {"OS":2,"MRN":"28","sdate":"2021-10-03T03:47:24"}]
[{"OS":1,"MRN":"29","sdate":"2021-10-03T03:47:24"},
 {"OS":2,"MRN":"30","sdate":"2021-10-03T03:47:24"}]

The edates are all gone.

To keep things simple for this introduction to the SET operation of JSON_TRANSFORM I used hardcoded values and NULL’s but more complex functions can be used as I’ll demonstrate in the next post.

Thanks for reading!

Leave a Comment