2021 – JSON_TRANSFORM Introduction

A previous post in this series dealt with changes to the JSON data structures. Specifically the addition and population of two new key-value pairs. Two other posts here and here showed how object types can be used to make queries simpler as structures change. This is powerful but also quite limited. What we really need is a way to transform JSON data using a series of defined steps. Luckily Oracle has this and its called JSON_TRANSFORM. Here’s an introduction.

Here is the test data – two rows with JSON arrays with two keys (OS and MRN).

SELECT JSON_QUERY(patient_data,'$.mrns') before
  FROM json_patient
BEFORE
---------------------------------------
[{"OS":1,"MRN":"5"},{"OS":2,"MRN":"6"}]
[{"OS":1,"MRN":"7"},{"OS":2,"MRN":"8"}]

And now the update using JSON_TRANSFORM to add and populate 2 new keys (sdate and edate).

UPDATE JSON_PATIENT
SET patient_data = JSON_TRANSFORM(patient_data,
                     INSERT '$.mrns[*].sdate' = SYSDATE,
                     INSERT '$.mrns[*].edate' = NULL);
2 rows updated

The JSON_TRANSFORM explicitly says:

  • For each entry in the mrns array
    • Add a key named sdate with a value of the current date and time (SYSDATE)
    • Add a key name edate with a value of NULL

And the resultant data.

SELECT JSON_QUERY(patient_data,'$.mrns') after
  FROM json_patient
AFTER
-------------
[{"OS":1,"MRN":"5","sdate":"2021-09-26T15:36:46","edate":null},
 {"OS":2,"MRN":"6","sdate":"2021-09-26T15:36:46","edate":null}]
[{"OS":1,"MRN":"7","sdate":"2021-09-26T15:36:46","edate":null},
 {"OS":2,"MRN":"8","sdate":"2021-09-26T15:36:46","edate":null}]

The JSON_TRANSFORM also implicitly says

  • If the KEY to be added already exists then fail

What does that mean? Try the update again and see.

UPDATE JSON_PATIENT
  SET patient_data = JSON_TRANSFORM(patient_data,
    INSERT '$.mrns[*].sdate' = SYSDATE,
    INSERT '$.mrns[*].edate' = NULL);
Raises ORA-40763: existing value in JSON_TRANSFORM()

It fails because the sdate key already existed in a rows we tried to process. This is avoided by telling each operation to IGNORE cases where a KEY already exists.

UPDATE JSON_PATIENT
  SET patient_data = JSON_TRANSFORM(patient_data,
    INSERT '$.mrns[*].sdate' = SYSDATE IGNORE ON EXISTING,
    INSERT '$.mrns[*].edate' = NULL    IGNORE ON EXISTING );
2 rows updated

It still says 2 rows were updated even though the data did not actually change.

Here’s what the data looks like…still.

SELECT JSON_QUERY(patient_data,'$.mrns') after2
  FROM json_patient
AFTER2
-------------
[{"OS":1,"MRN":"5","sdate":"2021-09-26T15:36:46","edate":null},
 {"OS":2,"MRN":"6","sdate":"2021-09-26T15:36:46","edate":null}]
[{"OS":1,"MRN":"7","sdate":"2021-09-26T15:36:46","edate":null},
 {"OS":2,"MRN":"8","sdate":"2021-09-26T15:36:46","edate":null}]

I’ll cover JSON_TRANSFORM a lot in the next few months of posts.

Thanks for reading!

Leave a Comment