CODEWITHSUNDEEP
sqljsondatabaseoracle-databaseclob
user8446864
user8446864

Reputation: 105

Change JSON hierarchy by SQL query in Oracle

I have a table with clob that contains JSON. The JSON already exists, I need to change the hierarchy of the first value.

This is the original json:

{
  "configurationByAssetType" :
  {
    "default" :
    {
      "sections" :
      [
....
]
}}}

I need to put configurationByAssetType under new section - configurationByView:

{
"configurationByView"
{
 "default" :{
  "configurationByAssetType" :
  {
    "default" :
    {
      "sections" :
      [
....
]
}}}}}

I want to copy all existing data to be under:

{
"configurationByView"
{
 "default" :{

Upvotes: 2

Views: 134

Answers (1)

Sergey Afinogenov
Sergey Afinogenov

Reputation: 2212

Why just not to concatenate necessary characters to the beginning and end of the clob:

with q as
 (select to_clob('{  "configurationByAssetType" :
                {
                  "default" :
                  {
                    "sections" :
                    [
              ....
              ]
              }}}') clob_json
from dual) 
select '{
"configurationByView"
    {
  "default" :{' || clob_json || '}}}'
  from q;

Upvotes: 1

Related Questions