Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

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

Last updated: December 01, 2021 - 2:49 am 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

  (6 ratings)

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)

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