Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Don.

Asked: August 06, 2018 - 9:12 pm UTC

Last updated: August 09, 2018 - 3:52 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

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


with LiveSQL Test Case:

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, August 09, 2018 - 3:43 pm UTC

That is excellent. I was sure I was close, and this is extremely useful in my shop.

I must say that, at least in this case, I prefer the second syntax
json_value (j.person, '$.LNAME') = 'Mallard'
to the first
json_exists (j.person, '$?(@.LNAME == "Mallard")')


Chris Saxon
August 09, 2018 - 3:52 pm UTC

Great to hear. Yes, the syntax for json_exists is a little... icky. It's more useful if you want to check if a document has the attribute at all.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.