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: November 25, 2022 - 5:24 pm UTC

Version: 21.0

Viewed 1000+ times

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

  (10 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

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