CODEWITHSUNDEEP
oracle12cjsonpath
dropofahat
dropofahat

Reputation: 403

Using Oracle Path Expressions on JSON arrays

I am attempting to write a simple query with path expressions against an Oracle JSON data structure that will return the Student Name and the the name of their CS220 teacher (if they are taking that class).

The JSON:

{
  'studentName': 'John Smith',
  'classes': [
    {
      'className': 'CS115',
      'teacherName': 'Sally Wilson'
    },
    {
      'className': 'CS220',
      'teacherName': 'Jason Wu'
    }
  ]
}

Expected Output

Student Name      Professor
John Smith        Jason Wu
Jane Doe                             << Not taking CS220
Ajay Kumar        Robert Kroll

The query I would hope to write:

Select 
    jsonfield.studentName,
    jsonfield.classes.<some path expression to find the CS220 professor here>
from mytable

The only solution I have found is to project out the nested 'classes' into a table and join that to the query above to get the professor. I would have thought that Oracle's json path implementation would be able to solve this without the overhead/complexity of a second query.

Upvotes: 0

Views: 1995

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

In 12cR1 you could do something like:

select jt.studentname,
  max(case when jt.classname = 'CS220' then jt.teachername end) as teachername
from mytable mt
cross join json_table (
  mt.jsonfield,
  '$'
  columns (
    studentname varchar2(30) path '$.studentName',
    nested path '$.classes[*]' columns (
      classname varchar2(30) path '$.className',
      teachername varchar2(30) path '$.teacherName'
    )
  )
) jt
group by jt.studentname;

The json_table() splits the JSON into relational columns; the nested path means you get one row per class (per student), with the relevant class names and teacher names.

The select list then uses a case expression to change the teacher name to null for any other classes - so John Smith gets one row with CS220 and Jason Wu, and one row with CS115 and null. Aggregating that with max() collapses those so all the irrelevant teachers are ignored.

With some expanded sample data:

create table mytable (jsonfield clob check (jsonfield is json));
insert into mytable a(jsonfield) values (q'#{
  'studentName': 'John Smith',
  'classes': [
    {
      'className': 'CS115',
      'teacherName': 'Sally Wilson'
    },
    {
      'className': 'CS220',
      'teacherName': 'Jason Wu'
    }
  ]
}#');
insert into mytable a(jsonfield) values (q'#{
  'studentName': 'Jane Doe',
  'classes': [
    {
      'className': 'CS115',
      'teacherName': 'Sally Wilson'
    }
  ]
}#');
insert into mytable a(jsonfield) values (q'#{
  'studentName': 'Ajay Kumar',
  'classes': [
    {
      'className': 'CS220',
      'teacherName': 'Robert Kroll'
    }
  ]
}#');

the basic json_table() call gets:

select jt.*,
  case when jt.classname = 'CS220' then jt.teachername end as adjusted_teachername
from mytable mt
cross join json_table (
  mt.jsonfield,
  '$'
  columns (
    studentname varchar2(30) path '$.studentName',
    nested path '$.classes[*]' columns (
      classname varchar2(30) path '$.className',
      teachername varchar2(30) path '$.teacherName'
    )
  )
) jt;

STUDENTNAME                    CLASSNAME                      TEACHERNAME                    ADJUSTED_TEACHERNAME         
------------------------------ ------------------------------ ------------------------------ ------------------------------
John Smith                     CS115                          Sally Wilson                                                 
John Smith                     CS220                          Jason Wu                       Jason Wu                      
Jane Doe                       CS115                          Sally Wilson                                                 
Ajay Kumar                     CS220                          Robert Kroll                   Robert Kroll

Adding the aggregation step gets:

select jt.studentname,
  max(case when jt.classname = 'CS220' then jt.teachername end) as teachername
from mytable mt
cross join json_table (
  mt.jsonfield,
  '$'
  columns (
    studentname varchar2(30) path '$.studentName',
    nested path '$.classes[*]' columns (
      classname varchar2(30) path '$.className',
      teachername varchar2(30) path '$.teacherName'
    )
  )
) jt
group by jt.studentname;

STUDENTNAME                    TEACHERNAME                  
------------------------------ ------------------------------
John Smith                     Jason Wu                      
Jane Doe                                                     
Ajay Kumar                     Robert Kroll

In 12cR2 I think thought you might be able to do something like this instead, with a filter inside the JSON path (which isn't allowed in 12cR1):

select jt.*
from mytable mt
cross join json_table (
  mt.jsonfield,
  '$'
  columns (
    studentname varchar2(30) path '$.studentName',
    nested path '$.classes[*]?(@.className=="CS220")' columns (
      teachername varchar2(30) path '$.teacherName'
    )
  )
) jt;

... but I don't have a suitable DB to test that against.

... but it turns out that gets "ORA-40553: path expression with predicates not supported in this operation" and "Only JSON_EXISTS supports predicates".

Upvotes: 2

Related Questions