Modify a JSON structure with a single Update

Recently there was a question on the Oracle Community Forum related to JSON, more specifically how to change the structure of it. The link to the Question is at the bottom of this post.
The starting point is the following JSON:

  {
   "tktKey": "123456",
   "attlist1": [
      {
         "attkey": "123",
         "attval": "abc"
      },
      {
         "attkey": "456",
         "attval": "def"
      }
   ],
   "attlist2": [
      {
         "attkey": "098",
         "attval": "xyz"
      }
   ]
}
  

The task at hand was to combine both attlist1 and attlist2 into a single attlist.
To do this the JSON object that is contained in attlist2 need to be extracted and place in attlist1. After that the element attlist2 can be removed from the structure. When this is done, there is an element with attlist1 with three JSON objects in it. Finally the element attlist1 needs to be renamed to attlist.
All of these actions can be done with a single SQL statement using JSON_TRANSFORM.

Let's first create a table to hold the original JSON

   SQL> create table js
  2  (str clob 
  3  )
  4* /

Table JS created.
   

And insert the JSON Object in there.

   SQL> insert into js values ('{ "tktKey": "123456", "attlist1": [ { "attkey": "123",   "attval": "abc"  },  { "attkey": "456","attval": "def"  } ],  "attlist2": [{"attkey": "098", "attval": "xyz"  } ]}')
  2* /

1 row inserted.
   

Here comes the magic moment:

   SQL> update js
  2     set str= json_transform
  3                (js.str
  4                ,append '$.attlist1'
  5                    = json_query (js.str, '$.attlist2[*]')
  6                ,remove '$.attlist2'
  7                ,rename '$.attlist1' ='attlist')
  8* /

1 row updated.
   

With JSON_QUERY (line 5) I extract the JSON Object from attlist2, which is added (APPEND, line 4) to attlist1.
Attlist2 is removed from the JSON object (line 6) and finally on line 7 attlist1 is renamed to attlist.

And to view the results, in a nice readable format:

SQL> select json_query (j.str, '$' returning clob pretty) as result
  2    from js j
  3* /

RESULT 
_______ 
{
  "tktKey" : "123456",
  "attlist" :
  [
    {
      "attkey" : "123",
      "attval" : "abc"
    },
    {
      "attkey" : "456",
      "attval" : "def"
    },
    {
      "attkey" : "098",
      "attval" : "xyz"
    }
  ]
}    

The more I use the JSON functionality that the Oracle database has to offer, the more I like them!

Links

• Oracle Community Question
• JSON_QUERY
• JSON_TRANSFORM