Change JSON hierarchy by SQL query in Oracle

Question

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" :{

Sergey Afinogenov · Accepted Answer · 2021-01-21 16:11:51Z

1

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;

answered Jan 21, 2021 at 16:11

Sergey Afinogenov

2,2326 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user8446864 Over a year ago

Thanks! This is the exactly what I thought also after I wrote the question.

Collectives™ on Stack Overflow

Change JSON hierarchy by SQL query in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related