I need to query a collection of json data for specific data values contained. In this example, I'm trying to count the number of json objects where the LNAME attribute = 'Mallard'. Based on the documentation at:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/simple-dot-notation-access-to-json-data.html#GUID-7249417B-A337-4854-8040-192D5CEFD576 I would think this code should work, but nope.
create table PERSON (
PERSON_KEY number(19),
FNAME varchar2(20 char),
LNAME varchar2(20 char)
);
insert into PERSON
select '1','Jeremy','Mallard' from dual union
select '2','Marilyn','Foster' from dual;
commit;
create or replace type String4000List as table of varchar2(4000);
declare
vPersonList String4000List;
vMallard number;
begin
vPersonList := String4000List();
select json_object (
'PERSON_KEY' value p.PERSON_KEY,
'FNAME' value min(p.FNAME),
'LNAME' value min(p.LNAME)
) PERSON
bulk collect into vPersonList
from PERSON p
group by p.PERSON_KEY;
for i in (
select column_value
from table(vPersonList)
)
loop
DBMS_OUTPUT.put_line (i.column_value);
end loop i;
select count(*)
into vMallard
from table(vPersonList) pl
where pl.column_value.LNAME = 'Mallard';
-- where pl.column_value like '%Mallard%';
DBMS_OUTPUT.put_line (vMallard);
end;
Am I missing something simple, or have a I missed the boat entirely?
Thanx, D
The database doesn't know the text is a JSON document. Which is why you can't use dot-notation.
If you want to search within JSON documents, you need to use the JSON* functions.
You could use either JSON_value or JSON_exists to find documents that contain Mallard in the lname attribute:
with jsondocs as (
select json_object (
'PERSON_KEY' value p.PERSON_KEY,
'FNAME' value min(p.FNAME),
'LNAME' value min(p.LNAME)
) PERSON
from PERSON p
group by p.PERSON_KEY
)
select count(*) from jsondocs j
where json_exists (
j.person, '$?(@.LNAME == "Mallard")'
);
COUNT(*)
1
with jsondocs as (
select json_object (
'PERSON_KEY' value p.PERSON_KEY,
'FNAME' value min(p.FNAME),
'LNAME' value min(p.LNAME)
) PERSON
from PERSON p
group by p.PERSON_KEY
)
select count(*) from jsondocs j
where json_value (
j.person, '$.LNAME'
) = 'Mallard';
COUNT(*)
1
Unless you're luckY enough to be on 18c that is :)
Here you can use the treat ( ... as json ) function on the column_value to enable dot-noation access:
create table PERSON (
PERSON_KEY number(19),
FNAME varchar2(20 char),
LNAME varchar2(20 char)
);
insert into PERSON
select '1','Jeremy','Mallard' from dual union
select '2','Marilyn','Foster' from dual;
commit;
create or replace type String4000List as table of varchar2(4000);
/
declare
vPersonList String4000List;
vMallard number;
begin
vPersonList := String4000List();
select json_object (
'PERSON_KEY' value p.PERSON_KEY,
'FNAME' value min(p.FNAME),
'LNAME' value min(p.LNAME)
) PERSON
bulk collect into vPersonList
from PERSON p
group by p.PERSON_KEY;
with j as (
select treat ( t.column_value as json ) as doc
from table(vPersonList) t
)
select count(*)
into vMallard
from j j
where j.doc.LNAME = 'Mallard';
DBMS_OUTPUT.put_line (vMallard);
end;
/
1