Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: October 22, 2021 - 3:18 am UTC

Last updated: April 18, 2023 - 3:23 am UTC

Version: 21.0

Viewed 10K+ times! This question is

You Asked

Team,

Given this JSON, how do i increment the quantity by two in each level?
tried the below using json_transform but ended up with error.
is that possible using json_transform function? kinldy help.

demo@XEPDB1> select json_serialize(y pretty) y
  2  from t
  3  where x =2;

Y
----------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


demo@XEPDB1> select json_serialize(
  2     json_transform( y, set '$.produce[*].quantity' =
  3             '$.produce[*].quantity' + 2 ) pretty )
  4  from t
  5  where x =2;
                '$.produce[*].quantity' + 2 ) pretty )
                *
ERROR at line 3:
ORA-01722: invalid number


demo@XEPDB1>

Was able to get this done using JSON object types from PL/SQL.
would like to know if the same can be done using JSON_TRANSFROM function in sql?
demo@XEPDB1> create or replace function update_json( p_input json )
  2  return json
  3  as
  4      l_data json_object_t;
  5      l_size number := 0;
  6      l_ele json_element_t;
  7      l_array json_array_t;
  8      l_obj json_object_t;
  9      l_qty number := 0;
 10  begin
 11      l_data := json_object_t( p_input );
 12
 13      if l_data.has('produce') then
 14          l_ele := l_data.get('produce');
 15          if l_ele.is_array then
 16              l_size := l_ele.get_size()-1;
 17              l_array := json_array_t( l_ele );
 18              for i in 0..l_size
 19              loop
 20                  l_obj := treat( l_array.get(i) as json_object_t );
 21                  l_qty := l_obj.get_Number('quantity');
 22                  l_obj.put( 'quantity', l_qty+2 );
 23              end loop;
 24          end if;
 25      end if;
 26      return l_data.to_json;
 27  end;
 28  /

Function created.

demo@XEPDB1> select json_serialize(y)
  2  from t t1
  3  where x =2;

JSON_SERIALIZE(Y)
--------------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]}

demo@XEPDB1> select update_json(y)
  2  from t t1
  3  where x =2;

UPDATE_JSON(Y)
--------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":12},{"fruit":"orange","quantity":17}]}

demo@XEPDB1>

and Connor said...

These might be useful

SQL> create table employee(
  2        id number,
  3        created timestamp,
  4        cdata JSON);

Table created.

SQL>
SQL> insert into employee
  2      values (1, systimestamp,
  3      '{"name":"Connor", "details":{"city":"Perth", "sal":1000}}');

1 row created.

SQL>
SQL>
SQL> update employee c
  2  set cdata = JSON_Transform(cdata,SET '$.details.sal'= 2000)
  3  where id = 1;

1 row updated.

SQL>
SQL>
SQL> update employee c
  2  set cdata = JSON_Transform(cdata,SET '$.details.sal'= JSON_VALUE(c.cdata,'$.details.sal')+100)
  3  where id = 1;

1 row updated.

SQL>
SQL> update employee c
  2  set cdata = JSON_Transform(cdata,SET '$.details.sal'= c.cdata.details.sal.number()+100)
  3  where id = 1;

1 row updated.

SQL>
SQL>
SQL> select JSON_Serialize(cdata PRETTY) from employee;

JSON_SERIALIZE(CDATAPRETTY)
--------------------------------------------------------------------------------------------------------
------------
{
  "name" : "Connor",
  "details" :
  {
    "city" : "Perth",
    "sal" : 2200
  }
}


Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using JSON_ARRAY.

Rajeshwaran Jeyabal, October 27, 2021 - 6:34 am UTC

Thanks, the example you got is full of json_objects. so it went well.
However the example provided above is on json_Array, which we need to navigate into each path of array element and do the updates.
drop table t purge;
create table t(x json);
insert into t(x) values ('{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}');
commit;

demo@XEPDB1> select json_serialize(x pretty) from t;

JSON_SERIALIZE(XPRETTY)
-------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}

demo@XEPDB1> select json_serialize( json_transform(x, set
  2             '$.produce[*].quantity' =
  3             json_value( t1.x,'$.produce[*].quantity' returning number)+10 )
  4             pretty ) val
  5  from t t1
  6  /

VAL
----------------------------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : null
    },
    {
      "fruit" : "orange",
      "quantity" : null
    }
  ]
}

demo@XEPDB1> select json_serialize( json_transform( x, set
  2     '$.produce[*].quantity' = t1.x.produce.quantity.number()+100 )
  3     pretty ) val
  4  from t t1
  5  /

VAL
-------------------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : null
    },
    {
      "fruit" : "orange",
      "quantity" : null
    }
  ]
}

so please let us know what should we need to tweek in the above sql in place of json_value
Chris Saxon
November 23, 2021 - 11:24 am UTC

There are a couple of issues here:

- t1.x.produce.quantity.number() returns null! This is an array, but you're accessing it as a single value
- I don't know if there's a way to pass the element you're changing on the left of the expression to the selector on the right

That said, it's possible to increment all the elements in an array by:

- Using JSON_table to extract the array elements
- Apply JSON_transform to these to change each one
- JSON_arrayagg the result 

select json_arrayagg (
         json_transform (
           prod, set '$.quantity' = jt.prod.quantity.number() + 100
         )
       )
from   t t1, json_table ( 
  x, '$.produce[*]' columns ( prod json path '$' ) 
) jt;

[{"fruit":"apple","quantity":110},{"fruit":"orange","quantity":115}]


You can then JSON_transform it back to the main document:

with rws as (
  select json_arrayagg ( 
           json_transform (
             prod, set '$.quantity' = jt.prod.quantity.number() + 100
           )
         ) j
  from   t t1, json_table ( 
    x, 
    '$.produce[*]' columns ( prod json path '$' ) 
  ) jt
)
select json_serialize (
         json_transform (
           x, set '$.produce' = 
             ( select j from rws )
         ) pretty
       )
from   t t1;

{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 110
    },
    {
      "fruit" : "orange",
      "quantity" : 115
    }
  ]
}


I'm unsure if there's an easier method

eltiT

emaN, November 24, 2021 - 3:18 am UTC 

with function js_do(jsn varchar2, js varchar2, num number) return clob as
  ctx dbms_mle.context_handle_t;
  res clob := empty_clob()||'';
begin
  ctx := dbms_mle.create_context();
  dbms_mle.export_to_mle(ctx, 'jsn', jsn);
  dbms_mle.export_to_mle(ctx, 'num', num);
  dbms_mle.eval(ctx, 'JAVASCRIPT', q'<
    const bind = require("mle-js-bindings");
    const jsn = JSON.parse(bind.importValue("jsn"));
    const num = parseInt(bind.importValue("num"));
    >');
  dbms_mle.eval(ctx, 'JAVASCRIPT', js);
  dbms_mle.eval(ctx, 'JAVASCRIPT', 'jsn', res);
  dbms_mle.drop_context(ctx);
  return res;
end;
select js_do('{"produce":[{"fruit" : "apple", "quantity" : 10},
                          {"fruit" : "orange","quantity" : 15}]}',
             'jsn.produce.forEach(function(x){x.quantity=x.quantity+num});',
             100) "21c"
from dual
/

21c                                                                             
--------------------------------------------------------------------------------
{"produce":[{"fruit":"apple","quantity":110},{"fruit":"orange","quantity":115}]}

Chris Saxon
November 24, 2021 - 2:12 pm UTC

Thanks for sharing, not sure if this qualifies as easier though!

MLE

Rajeshwaran, Jeyabal, November 25, 2021 - 7:36 am UTC

but when i tried running the above code on my local XE instance - got this error. should we need to enable any setting to get this MLE to work? 

demo@XEPDB1> with function js_do(jsn varchar2, js varchar2, num number) return clob as
  2    ctx dbms_mle.context_handle_t;
  3    res clob := empty_clob()||'';
  4  begin
  5    ctx := dbms_mle.create_context();
  6    dbms_mle.export_to_mle(ctx, 'jsn', jsn);
  7    dbms_mle.export_to_mle(ctx, 'num', num);
  8    dbms_mle.eval(ctx, 'JAVASCRIPT', q'<
  9      const bind = require("mle-js-bindings");
 10      const jsn = JSON.parse(bind.importValue("jsn"));
 11      const num = parseInt(bind.importValue("num"));
 12      >');
 13    dbms_mle.eval(ctx, 'JAVASCRIPT', js);
 14    dbms_mle.eval(ctx, 'JAVASCRIPT', 'jsn', res);
 15    dbms_mle.drop_context(ctx);
 16    return res;
 17  end;
 18  select js_do('{"produce":[{"fruit" : "apple", "quantity" : 10},
 19                            {"fruit" : "orange","quantity" : 15}]}',
 20               'jsn.produce.forEach(function(x){x.quantity=x.quantity+num});',
 21               100) "21c"
 22  from dual
 23  /
ERROR:
ORA-04259: cannot load Multilingual Engine (MLE)
ORA-06512: at "SYS.DBMS_MLE", line 360
ORA-06512: at line 18



no rows selected

Chris Saxon
November 25, 2021 - 3:28 pm UTC

Does your user have the EXECUTE DYNAMIC MLE privilege?

118.2 DBMS_MLE Security Model

Access to MLE features is protected by database privileges. The user must have the EXECUTE DYNAMIC MLE privilege while calling any of its functions and procedures that pertain to MLE execution. In addition to this, the user must also have the EXECUTE privilege on an MLE language (for example, EXECUTE ON JAVASCRIPT) to execute code in that language. An ORA-01031 error is raised if the user calling any of the DBMS_MLE subprograms does not have the appropriate privileges. See the summary of DBMS_MLE subprograms for the privileges required to call each DBMS_MLE subprogram.

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/dbms_mle.html#GUID-27E5F232-1199-4D90-BA1D-9483FB2EDDAA

Privilege

Rajeshwaran, Jeyabal, November 26, 2021 - 6:35 am UTC

yes we do the privilege available.

demo@XEPDB1> select * from session_privs where privilege like 'EXECUTE%DYNAMIC%';

PRIVILEGE
----------------------------------------
EXECUTE DYNAMIC MLE


so what else could be the problem here.

Platform?

MLErr, November 26, 2021 - 9:48 am UTC

OERR:
04259, 00000, "cannot load Multilingual Engine (MLE)"
// *Cause: An error happened when loading Multilingual Engine (MLE).
// *Action: Check trace files for errors.

DBMS_MLE:
- ORA-04259 if running on unsupported (non-LINUX.X64) platform
Connor McDonald
December 01, 2021 - 2:49 am UTC

Linux X64 only presently.

Platform

Rajeshwaran, Jeyabal, November 27, 2021 - 7:58 am UTC

that is from XE instance running on windows platform (windows 10)

option to check if this can be done using json_transform function

Rajeshwaran, Jeyabal, November 11, 2022 - 8:57 am UTC

Team,

do you see the below questions/requirements can be doing using JSON_TRANSFORM in SQL? was able to get this done using PL/SQL, just want to know if this can achieved using JSON_TRANSFORM, if yes can you show me how ?

demo@PDB1> create table t(x json);

Table created.

demo@PDB1> insert into t(x) values('[
  2      {
  3          "site": "site1",
  4          "categories": [
  5              "site1 cat1",
  6              "site1 cat2",
  7              "site1 cat3"
  8          ]
  9      },
 10      {
 11          "site": "site2",
 12          "categories": [
 13              "site2 cat1",
 14              "site2 cat2"
 15          ]
 16      }
 17  ]');

1 row created.


Questions
----------
a) How to add one another array element "site1 cat4" where site = site1?
b) How to remove array value from categories "site1 cat1" where site = site1?

a) 
demo@PDB1> create or replace function foo(p_in json)
  2  return json
  3  as
  4     l_array json_array_t;
  5     l_ele json_element_t;
  6     l_obj json_object_t;
  7     l_category json_array_t;
  8  begin
  9     l_array := json_array_t.load(p_in);
 10
 11     for i in 0..l_array.get_size()-1
 12     loop
 13             l_ele := l_array.get(i);
 14             if l_ele.is_Object then
 15                     l_obj := json_object_t(l_ele);
 16                     if l_obj.has('site') and l_obj.get_String('site') = 'site1' then
 17                             l_category := l_obj.get_Array('categories');
 18                             l_category.append('site1 cat4');
 19                             l_obj.put('categories',l_category);
 20                     end if;
 21             end if;
 22     end loop;
 23     return l_array.to_json ;
 24  end;
 25  /

Function created.

demo@PDB1> select * from t;

X
--------------------------------------------------------------------------------
[{"site":"site1","categories":["site1 cat1","site1 cat2","site1 cat3"]},{"site":
"site2","categories":["site2 cat1","site2 cat2"]}]


demo@PDB1> select foo(x) from t;

FOO(X)
--------------------------------------------------------------------------------
[{"site":"site1","categories":["site1 cat1","site1 cat2","site1 cat3","site1 cat
4"]},{"site":"site2","categories":["site2 cat1","site2 cat2"]}]


demo@PDB1>


b)
demo@PDB1> create or replace function remove_me(p_in json)
  2  return json
  3  as
  4     l_array json_array_t ;
  5     l_ele json_element_t;
  6     l_obj json_object_t;
  7     l_category json_array_t;
  8     i_idx number;
  9  begin
 10     l_array := json_array_t.load(p_in);
 11
 12     for i in 0..l_array.get_size()-1
 13     loop
 14             l_ele := l_array.get(i);
 15             if l_ele.is_Object then
 16                     l_obj := json_object_t(l_ele);
 17                     if l_obj.has('site') and l_obj.get_String('site') ='site1' then
 18                             l_category := l_obj.get_Array('categories');
 19                             for k in 0..l_category.get_size()-1
 20                             loop
 21                                     if l_category.get(k).stringify = '"site1 cat1"' then
 22                                             i_idx := k;
 23                                     end if;
 24                             end loop;
 25                             l_category.remove(i_idx);
 26                             l_obj.put('categories',l_category);
 27                     end if;
 28             end if;
 29     end loop ;
 30     return l_array.to_json ;
 31  end;
 32  /

Function created.

demo@PDB1> select * from t;

X
--------------------------------------------------------------------------------
[{"site":"site1","categories":["site1 cat1","site1 cat2","site1 cat3"]},{"site":
"site2","categories":["site2 cat1","site2 cat2"]}]


demo@PDB1> select remove_me(x) from t;

REMOVE_ME(X)
--------------------------------------------------------------------------------
[{"site":"site1","categories":["site1 cat2","site1 cat3"]},{"site":"site2","cate
gories":["site2 cat1","site2 cat2"]}]


demo@PDB1>

Chris Saxon
November 21, 2022 - 2:48 pm UTC

You can use JSON search expressions in the path, for example:

select json_serialize ( 
  json_transform (
    x, append '$[*]?(@.site == "site1").categories' = 'test'
  ) pretty 
)
from   t;

JSON_SERIALIZE(JSON_TRANSFORM(X,APPEND'$[*]?(@.SITE=="SITE1").CATEGORIES'='TEST')PRETTY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------------------------------------------------------------------------
[
  {
    "site" : "site1",
    "categories" :
    [
      "site1 cat1",
      "site1 cat2",
      "site1 cat3",
      "test"
    ]
  },
  {
    "site" : "site2",
    "categories" :
    [
      "site2 cat1",
      "site2 cat2"
    ]
  }
]

option to check if this can be done using json_transform function

Rajeshwaran, Jeyabal, November 22, 2022 - 7:34 am UTC

Thanks that helps, was nice to know about the JSON search expressions in the path

do you see an option to get (b) done using SQL/JSON function or JSON_TRANSFORM function calls?
Chris Saxon
November 23, 2022 - 1:24 pm UTC

What have you tried?

option to check if this can be done using json_transform function

Rajeshwaran, Jeyabal, November 23, 2022 - 2:46 pm UTC

Here is what i tried, but if fails for (b)

(b) How to remove array value from categories "site1 cat1" where site = site1?

was looking for a way to map "site1 cat1" into that "categories" array, but not working as expected.
demo@PDB1> select json_transform(x, remove '$[.]?(@.site=="site1").categories[*]' = 'site1 cat1')
  2  from t;
select json_transform(x, remove '$[.]?(@.site=="site1").categories[*]' = 'site1 cat1')
*
ERROR at line 1:
ORA-40598: JSON path expression contained an invalid array index ('$[.]?(@.site=="site1").categories[*]')
JZN-00215: Array step contains unexpected characters
at position 3


demo@PDB1> select json_transform(x, remove '$[*]?(@.site=="site1" && @.categories=="site1 cat1")')
  2  from t;

JSON_TRANSFORM(X,REMOVE'$[*]?(@.SITE=="SITE1"&&@.CATEGORIES=="SITE1CAT1")')
--------------------------------------------------------------------------------
[{"site":"site2","categories":["site2 cat1","site2 cat2"]}]

Chris Saxon
November 24, 2022 - 1:39 pm UTC

Have you checked the syntax for REMOVE?

$[.]
is an invalid path

option to check if this can be done using json_transform function

Rajeshwaran, Jeyabal, November 24, 2022 - 2:29 pm UTC

Yes, i did checked the syntax and given the second sql there , which got the valid syntax but not producing the expected output. hence need your inputs or direction on this.
Chris Saxon
November 25, 2022 - 5:24 pm UTC

The syntax is invalid both for the JSON path (you can't use [.]) and the remove clause

JSON_TRANSFORM and "json_passing_clause"

Rajeshwaran, Jeyabal, January 13, 2023 - 2:28 pm UTC

Team,

the below demo was from ATP 21c database, the docs shows the json_passing_clause is possible in JSON_TRANSFORM function, but it doesn't work here.

Questions
a) can you please help me to understand what i am missing here?
b) if "passing" clause is not supported, then what is the work around?

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_TRANSFORM.html#GUID-DD2A821B-C688-4310-81B5-5F45090B9366 

demo@ATP21C> create table t(x json);

Table created.

demo@ATP21C> insert into t
  2  select json_arrayagg( json_object(*) ) from emp;

1 row created.

demo@ATP21C> select json_transform( x, keep '$[*]?(@.EMPNO==7499)') from t;

JSON_TRANSFORM(X,KEEP'$[*]?(@.EMPNO==7499)')
--------------------------------------------------------------------------------
[{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-2
0T00:00:00","SAL":1600,"COMM":300,"DEPTNO":30}]


demo@ATP21C> select json_transform( x, keep '$[*]?(@.EMPNO==$b1)' passing 7499 as "b1" ) from t;
select json_transform( x, keep '$[*]?(@.EMPNO==$b1)' passing 7499 as "b1" ) from t
                                                     *
ERROR at line 1:
ORA-00907: missing right parenthesis


demo@ATP21C>

Connor McDonald
January 16, 2023 - 3:36 am UTC

I would suspect it is a doc bug and has not yet been implemented in JSON_TRANSFORM

SQL> create table t(x json);

Table created.

SQL> insert into t
  2  select json_arrayagg(json_object(*)) from emp;

1 row created.

SQL>
SQL> select json_transform( x, keep '$[*]?(@.EMPNO==7499)') from t;

JSON_TRANSFORM(X,KEEP'$[*]?(@.EMPNO==7499)')
--------------------------------------------------------------------------------
[{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-2

SQL>
SQL> select json_transform( x, keep '$[*]?(@.EMPNO == $b1)' passing 7499 as "b1" ) from t;
select json_transform( x, keep '$[*]?(@.EMPNO == $b1)' passing 7499 as "b1" ) from t
                                                       *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>
SQL> select * from t
  2  where json_exists(x, '$[*]?(@.EMPNO == $b1)' passing 7499 as "b1");

X
--------------------------------------------------------------------------------
[{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"1980-12-17T0


I'll ask around

Enhancements in 23c

Rajeshwaran Jeyabal, April 17, 2023 - 3:08 pm UTC

Team,

was playing with 23c Free version and came to know a couple of things

1) "passing" clause is now supported in the 23c version
demo@FREEPDB1> select json_transform( x, keep '$[*]?(@.EMPNO == $b1)' passing 7499 as "b1" ) from t;

JSON_TRANSFORM(X,KEEP'$[*]?(@.EMPNO==$B1)'PASSING7499AS"B1")
--------------------------------------------------------------------------------
[
  {
    "EMPNO" : 7499,
    "ENAME" : "ALLEN",
    "JOB" : "SALESMAN",
    "MGR" : 7698,
    "HIREDATE" : "1981-02-20T00:00:00",
    "SAL" : 1600,
    "COMM" : 300,
    "DEPTNO" : 30
  }
]


2) the initial question from this page - can be simplified with "nested path" feature in 23c version - something like this 

demo@FREEPDB1> select x from t;

X
--------------------------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


demo@FREEPDB1> select json_transform( x,nested path '$.produce[*]'
  2          ( set '@.quantity' = path '@.quantity + 2' ) )
  3  from t
  4  /

JSON_TRANSFORM(X,NESTEDPATH'$.PRODUCE[*]'(SET'@.QUANTITY'=PATH'@.QUANTITY+2'))
--------------------------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 12
    },
    {
      "fruit" : "orange",
      "quantity" : 17
    }
  ]
}


demo@FREEPDB1>


Overall thanks to 23c it looks awesome.
Connor McDonald
April 18, 2023 - 3:23 am UTC

thanks for the info

JSON Transformations in 23c

Rajeshwaran Jeyabal, April 20, 2023 - 6:39 am UTC

Team,

was playing with Oracle 23c feature of json_transform - here is my sample data.
CREATE TABLE REPOSITORY_SETTINGS (
            REPOSITORY_ID VARCHAR2(256) NOT NULL PRIMARY KEY,
            REPOSITORY_SETTINGS json );
   
insert into REPOSITORY_SETTINGS values(
'ocid1.devopsrepository.dev.dev.amaaaaaavuj6teyapwt3576zequ4nuk5cffr3kyggxxe3nc3uipnagaw27ka',
'{
"mergeChecks": {
    "lastBuildSucceeded": "ENABLED"
},
"mergeSettings": {
    "defaultMergeStrategy": "FAST_FORWARD",
    "allowedMergeStrategies": [
        "FAST_FORWARD",
        "SQUASH"
    ]
},
"approvalRules": {
    "items": [
     {
         "name": "rule1",
            "destinationBranch": "main",
            "reviewerIds": ["SYS", "SYSTEM"],
            "minApprovalsCount": 1
    },
    {
         "name": "rule2",
            "destinationBranch": "master",
            "reviewerIds": ["SYS"],
            "minApprovalsCount": 1
    }
]
}}');

CREATE TABLE PRINCIPAL (
      PRINCIPAL_ID VARCHAR2(256) NOT NULL PRIMARY KEY,
      PRINCIPAL_NAME VARCHAR2(256),
      PRINCIPAL_TYPE VARCHAR2(256) NOT NULL CHECK ("PRINCIPAL_TYPE" IN ('USER', 'SERVICE')),
      EMAIL VARCHAR2(256),
      DOMAIN_ID VARCHAR2(256),
      TIME_UPDATED TIMESTAMP(6)
      );
insert into PRINCIPAL values(
'SYS','Sys_name','USER',
'fake@gamil.com','domain',null);

insert into PRINCIPAL values(
'SYSTEM','Sys_name','USER',
'fake@gamil.com','domain',null);

and the requirement is " we are trying to get back the original record in “REPOSITORY_SETTINGS” table like this by join reviewerId with the principalId in Principal Table"

and the final output should be like this
'ocid1.devopsrepository.dev.dev.amaaaaaavuj6teyapwt3576zequ4nuk5cffr3kyggxxe3nc3uipnagaw27ka',
'{
"mergeChecks": {
    "lastBuildSucceeded": "ENABLED"
},
"mergeSettings": {
    "defaultMergeStrategy": "FAST_FORWARD",
    "allowedMergeStrategies": [
        "FAST_FORWARD",
        "SQUASH"
    ]
},
"approvalRules": {
    "items": [
        {
         "name": "rule1",
            "destinationBranch": "main",
            "reviewers": [
                  {"userId":"SYS",
                    "userName":"Sys_name",
                    "email":"fake@gamil.com"} ,
     
                  { "userId":"SYSTEM",
                    "userName":"Sys_name",
                    "email":"fake@gamil.com"}
     ],
            "minApprovalsCount": 1
    }, 
    {
         "name": "rule2",
            "destinationBranch": "master",
            "reviewers": [
                  {
                    "userId":"SYS",
                    "userName":"Sys_name",
                    "email":"fake@gamil.com"}],
            "minApprovalsCount": 1
    }
]
}}'

utilizing the json_transform "nested path" feature & works fine with hard coding the values
demo@FREEPDB1> select json_transform( REPOSITORY_SETTINGS, nested path '$.approvalRules.items[*]'
  2              ( set '@.reviewerIds[*]' = (select json_object( 'userId' value p.PRINCIPAL_ID , 'userName' value p.PRINCIPAL_NAME , 'email' value p.email
  3                          from PRINCIPAL p where p.PRINCIPAL_ID = 'SYS' ) ) )
  4  from REPOSITORY_SETTINGS ;

JSON_TRANSFORM(REPOSITORY_SETTINGS,NESTEDPATH'$.APPROVALRULES.ITEMS[*]'(SET'@.RE
--------------------------------------------------------------------------------
{
  "mergeChecks" :
  {
    "lastBuildSucceeded" : "ENABLED"
  },
  "mergeSettings" :
  {
    "defaultMergeStrategy" : "FAST_FORWARD",
    "allowedMergeStrategies" :
    [
      "FAST_FORWARD",
      "SQUASH"
    ]
  },
  "approvalRules" :
  {
    "items" :
    [
      {
        "name" : "rule1",
        "destinationBranch" : "main",
        "reviewerIds" :
        [
          {
            "userId" : "SYS",
            "userName" : "Sys_name",
            "email" : "fake@gamil.com"
          },
          {
            "userId" : "SYS",
            "userName" : "Sys_name",
            "email" : "fake@gamil.com"
          }
        ],
        "minApprovalsCount" : 1
      },
      {
        "name" : "rule2",
        "destinationBranch" : "master",
        "reviewerIds" :
        [
          {
            "userId" : "SYS",
            "userName" : "Sys_name",
            "email" : "fake@gamil.com"
          }
        ],
        "minApprovalsCount" : 1
      }
    ]
  }
}


demo@FREEPDB1>

but not working with dynamic handling of "$.approvalRules.items[*].reviewerIds[*]" in the RHS of json_transform function call - any possible solution to this using json_transform(..) ?
demo@FREEPDB1> select json_transform( REPOSITORY_SETTINGS, nested path '$.approvalRules.items[*]'
  2              ( set '@.reviewerIds[*]' = (select json_object( 'userId' value p.PRINCIPAL_ID , 'userName' value p.PRINCIPAL_NAME , 'email' value p.email )
  3                          from PRINCIPAL p where p.PRINCIPAL_ID = '$.approvalRules.items[*].reviewerIds[*]' ) ) )
  4  from REPOSITORY_SETTINGS ;

JSON_TRANSFORM(REPOSITORY_SETTINGS,NESTEDPATH'$.APPROVALRULES.ITEMS[*]'(SET'@.RE
--------------------------------------------------------------------------------
{
  "mergeChecks" :
  {
    "lastBuildSucceeded" : "ENABLED"
  },
  "mergeSettings" :
  {
    "defaultMergeStrategy" : "FAST_FORWARD",
    "allowedMergeStrategies" :
    [
      "FAST_FORWARD",
      "SQUASH"
    ]
  },
  "approvalRules" :
  {
    "items" :
    [
      {
        "name" : "rule1",
        "destinationBranch" : "main",
        "reviewerIds" :
        [
          null,
          null
        ],
        "minApprovalsCount" : 1
      },
      {
        "name" : "rule2",
        "destinationBranch" : "master",
        "reviewerIds" :
        [
          null
        ],
        "minApprovalsCount" : 1
      }
    ]
  }
}

JSON Transformations in 23c

Rajeshwaran Jeyabal, April 21, 2023 - 8:07 am UTC

please ignore the above request, got it working

demo@FREEPDB1> select json_transform( t1.REPOSITORY_SETTINGS,
  2      set '$.approvalRules.items' = json[
  3          select json_transform( items, set '$.reviewerIds' = json[
  4              select json{ 'userId' value p.principal_id, 'userName' value p.principal_name,'email' value p.email }
  5              from json_table(t2.items,'$.reviewerIds[*]'
  6                  columns( x1 varchar2(10) path '$')) t3
  7              left outer join principal p on t3.x1 = p.principal_id
  8          ] )
  9          from json_table( t1.REPOSITORY_SETTINGS.approvalRules.items[*]
10              columns( items json path '$' ) ) t2
11      ] )
12  from REPOSITORY_SETTINGS t1 ;

JSON_TRANSFORM(T1.REPOSITORY_SETTINGS,SET'$.APPROVALRULES.ITEMS'=JSON[SELECTJSON
--------------------------------------------------------------------------------
{
  "mergeChecks" :
  {
    "lastBuildSucceeded" : "ENABLED"
  },
  "mergeSettings" :
  {
    "defaultMergeStrategy" : "FAST_FORWARD",
    "allowedMergeStrategies" :
    [
      "FAST_FORWARD",
      "SQUASH"
    ]
  },
  "approvalRules" :
  {
    "items" :
    [
      {
        "name" : "rule1",
        "destinationBranch" : "main",
        "reviewerIds" :
        [
          {
            "userId" : "SYS",
            "userName" : "Sys_name",
            "email" : fake@gamil.com
          },
          {
            "userId" : "SYSTEM",
            "userName" : "Sys_name",
            "email" : fake@gamil.com
          }
        ],
        "minApprovalsCount" : 1
      },
      {
        "name" : "rule2",
        "destinationBranch" : "master",
        "reviewerIds" :
        [
          {
            "userId" : "SYS",
            "userName" : "Sys_name",
            "email" : fake@gamil.com
          }
        ],
        "minApprovalsCount" : 1
      }
    ]
  }
}


demo@FREEPDB1>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library