Skip to Main Content
  • Questions
  • Getting Value from JSON array using PL/SQL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Paapa.

Asked: July 09, 2021 - 6:31 pm UTC

Last updated: July 30, 2021 - 11:03 am UTC

Version: 12c

Viewed 100+ times

You Asked

I need help. I do I get data from the stats object in the JSON array using PL/SQL?
{
"items": [
{
" stats": {
"m_date": "2019-05-31T00:00:00",
"v_num": "0040012",
"pk_num": "0562",
"amt": 94,
"bal": 75,
"disc": 13
}
}
}

Thanks

and we said...

You can use the JSON_*_T object types to access the attributes or JSON_table to do it in SQL:

declare
  jdata varchar2(1000) := '{
  "items": [
    {
      "stats": {
        "m_date": "2019-05-31T00:00:00",
        "v_num": "0040012",
        "pk_num": "0562",
        "amt": 94,
        "bal": 75,
        "disc": 13
      }
    }
  ]
}';
  jobj json_object_t;
  jarr json_array_t;
begin
  jobj := json_object_t.parse ( jdata );
  jarr := jobj.get_array ( 'items' );
  for i in 0 .. jarr.get_size - 1 loop
    dbms_output.put_line ( 
      treat ( 
        jarr.get(i) as json_object_t 
      ).get_object ( 'stats' ).get_string ( 'm_date' )
    );
  end loop;
  
  for rws in ( 
    select * from json_table (
      jdata, '$.items[*].stats'
      columns (
        m_date path '$.m_date'
      )
    )
  ) loop
    dbms_output.put_line ( rws.m_date );
  end loop;
end;
/
2019-05-31T00:00:00
2019-05-31T00:00:00


PS - it looks like you're missing a closing ] for the items array in the example.

Rating

  (10 ratings)

Comments

A reader, July 13, 2021 - 8:24 am UTC

Thanks for the solution. It works.
Chris Saxon
July 13, 2021 - 10:13 am UTC

Great

could you please help us to understand what are we missing here?

Rajeshwaran Jeyabal, July 13, 2021 - 12:15 pm UTC

demo@XEPDB1> declare
  2     jdata long := '{
  3    "items": [
  4      {
  5        "stats": {
  6          "m_date": "2019-05-31T00:00:00",
  7          "v_num": "0040012",
  8          "pk_num": "0562",
  9          "amt": 94,
 10          "bal": 75,
 11          "disc": 13
 12        }
 13      }
 14    ]
 15  }';
 16     jobj json_object_t;
 17     jarr json_array_t;
 18  begin
 19     jobj := json_object_t.parse(jdata);
 20     jarr := jobj.get_array('items');
 21     for i in 1..jarr.get_Size
 22     loop
 23             dbms_output.put_line(
 24                     treat( jarr.get(i) as json_object_t).get_Object('stats').get_string('m_date') );
 25     end loop;
 26  end;
 27  /
declare
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at line 23

Chris Saxon
July 13, 2021 - 2:39 pm UTC

JSON arrays are zero-indexed; you're searching for a non-existent element.

thanks.

Rajeshwaran Jeyabal, July 13, 2021 - 4:58 pm UTC

....JSON arrays are zero-indexed....

thanks, completely forgot that point.

A reader, July 23, 2021 - 9:07 am UTC

I'm trying to get the data from a a table json_tbl with clob column name jval but the query is not returning any value.

for rws in (
select * from json_tbl,json_table (
jval, '$.items[*].stats'
columns (
m_date path '$.m_date'
)
)
) loop
dbms_output.put_line ( rws.m_date );
end loop;

how do I go about it?
Chris Saxon
July 23, 2021 - 4:54 pm UTC

What does the JSON document look like? You need to provide paths to the attributes you want to extract.

A reader, July 25, 2021 - 1:02 am UTC

it looks exactly like the json used above.
{
"items": [
{
"stats": {
"m_date": "2019-05-31T00:00:00",
"v_num": "0040012",
"pk_num": "0562",
"amt": 94,
"bal": 75,
"disc": 13
}
}
]
}

just that now I'm bringing the json data in from an api and storing it in a json_tbl before I access it and insert into a different table. If you have a better way, please show me.
Chris Saxon
July 26, 2021 - 10:59 am UTC

Insert the document in a table and use that table in the query.

create table t (
  jval varchar2(1000)
);

insert into t values ( '{
  "items": [ {
      "stats": {
        "m_date": "2019-05-31T00:00:00",
        "v_num": "0040012",
        "pk_num": "0562",
        "amt": 94,
        "bal": 75,
        "disc": 13
      }
    }
  ]
}' );

select j.* from t, json_table (
  jval, '$.items[*].stats'
  columns (
  m_date path '$.m_date'
  )
) j;

M_DATE                
2019-05-31T00:00:00 

A reader, July 25, 2021 - 1:09 am UTC

If you have a way which will help me insert into my relational tables straight from an API, you can show me that also.
{
"items": [
{
"stats": {
"m_date": "2019-05-31T00:00:00",
"v_num": "0040012",
"pk_num": "0562",
"amt": 94,
"bal": 75,
"disc": 13
}
}
]
}

let's say I want to insert into my_real_table(m_date,v_num,pk_num,amt,bal,disc)
select m_date,v_num,pk_num,amt,bal,disc
from "json from api".
Thanks.
Chris Saxon
July 26, 2021 - 11:00 am UTC

JSON_table is the API!

A reader, July 26, 2021 - 9:27 am UTC

I tried the code below but I get an error like .... " ORA-30625: method dispatch on NULL SELF argument is disallowed"

declare
jdata clob;
jobj json_object_t;
jarr json_array_t;
m_date_v varchar2(20);
v_num_v varchar2(10);
pk_num_v varchar2(10);
amt_v number;
bal_v number;
disc_v number;

begin

SELECT jval
INTO jdata
FROM json_tbl ;

jobj := json_object_t.parse ( jdata );
jarr := jobj.get_array ( 'items' );
for i in 0 .. jarr.get_size - 1 loop

m_date_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'm_date' );
v_num_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'v_num' );
pk_num_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'pk_num' );
amt_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'amt' );
bal_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'bal' );
disc_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'disc' );

insert into my_real_table (m_date,v_num,pk_num,amt,bal,disck)
values( TRUNC (SYS_EXTRACT_UTC (TO_UTC_TIMESTAMP_TZ (m_date_v))),v_num_v,pk_num_v,amt_v,bal_v,disc_v) ;

end loop;
end;
Chris Saxon
July 26, 2021 - 11:02 am UTC

You're accessing attributes/elements not present in the JSON.

I don't know where - using the table and data above I get no error:

declare
  jdata clob;
  jobj json_object_t;
  jarr json_array_t;
  m_date_v varchar2(20);
  v_num_v varchar2(10);
  pk_num_v varchar2(10);
  amt_v number;
  bal_v number;
  disc_v number;

begin

  SELECT jval
  INTO jdata
  FROM t ;
  
  jobj := json_object_t.parse ( jdata );
  jarr := jobj.get_array ( 'items' );
  for i in 0 .. jarr.get_size - 1 loop
  
    m_date_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'm_date' );
    v_num_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'v_num' );
    pk_num_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'pk_num' );
    amt_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'amt' );
    bal_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'bal' );
    disc_v := treat ( jarr.get(i) as json_object_t ).get_object ( 'stats' ).get_string ( 'disc' );
    
    dbms_output.put_line ( m_date_v );
  
  end loop;
end; 

2019-05-31T00:00:00

A reader, July 28, 2021 - 11:34 am UTC

I've tried it several times .. still same error. I'm getting the data from the url below. please check and see. I may be missing something that's preventing the thing from working. I really need it to work.

https://apex.oracle.com/pls/apex/gm/apimodule/clientdata
Chris Saxon
July 28, 2021 - 12:45 pm UTC

Somewhere there's a mismatch between the attributes you're searching for and the structure of the document.

Check the JSON very carefully and ensure the attribute names exactly match what you're searching for.

If you still need help please paste the exact JSON document you're processing & the code to do it.

A reader, July 28, 2021 - 6:28 pm UTC

This is the exact json document.


{
"items": [
{
" stats": {
"m_date": "2020-12-31T00:00:00",
"v_num": "004001110153460118",
"pk_num": "015346",
"amt": 123,
"bal": 122,
"disc": 12
}
},
{
" stats": {
"m_date": "2019-01-31T00:00:00",
"v_num": "004001110153460118",
"pk_num": "015346",
"amt": 117,
"bal": 117,
"disc": 12
}
},
{
" stats": {
"m_date": "2018-10-31T00:00:00",
"v_num": "004001110153460118",
"pk_num": "015346",
"amt": 116,
"bal": 116,
"disc": 12
}
},
{
" stats": {
"m_date": "2019-12-31T00:00:00",
"v_num": "004001110153460118",
"pk_num": "015346",
"amt": 120,
"bal": 119,
"disc": 12
}
},
{
" stats": {
"m_date": "2020-03-31T00:00:00",
"v_num": "004001110153460118",
"pk_num": "015346",
"amt": 120,
"bal": 120,
"disc": 12
}
}
],
"hasMore": false,
"limit": 25,
"offset": 0,
"count": 5,
"links": [
{
"rel": "self",
"href": " https://apex.oracle.com/pls/apex/gm/apimodule/clientdata"
},
{
"rel": "describedby",
"href": " https://apex.oracle.com/pls/apex/gm/metadata-catalog/apimodule/item"
},
{
"rel": "first",
"href": " https://apex.oracle.com/pls/apex/gm/apimodule/clientdata"
}
]
}
Chris Saxon
July 29, 2021 - 9:59 am UTC

Look very carefully at how the attributes are named. The paths you provide must match what's between the double quotes exactly.

A reader, July 29, 2021 - 4:37 pm UTC

I've seen the problem. there is a space before the stats object in the json that I was ignoring. once I corrected it, it worked.

THANK YOU!
Chris Saxon
July 30, 2021 - 11:03 am UTC

Great :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.