A reader, July 13, 2021 - 8:24 am UTC
Thanks for the solution. It works.
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
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?
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.
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.
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;
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
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" }
]
}
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!
July 30, 2021 - 11:03 am UTC
Great :)