Using Oracle Path Expressions on JSON arrays

Question

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.

Release 1 or release 2? In the latter you can probably do this with json_table() and a nested path with a filter... — Alex Poole
– Alex Poole, Commented Dec 6, 2017 at 18:58
On release 1 currently but moving to release 2 shortly. Even with release 2 I don't see a path expression that lets me describe this. The challenge with a 'single json_table / filter by classname = CS220' solution is that 'Jane Doe' would drop out of the result. — dropofahat
– dropofahat, Commented Dec 6, 2017 at 19:10
I was thinking of something like nested path '$.classes[*]?(@.className=="CS220")' rather than a where-clause filter, but as that isn't valid in release 1 that doesn't help much... — Alex Poole
– Alex Poole, Commented Dec 6, 2017 at 19:19
I'm good with a Release 2 solution. What I was looking for was a path that resolved to the professor's name qualified by a peer classname='CS220'. — dropofahat
– dropofahat, Commented Dec 6, 2017 at 19:54
The path expression I'd like to describe would be something like: $.classes[(@.className='CS220')].teacherName which resolved to the teacherName for the first CS220 class. This is how I would have described the query in xpath. — dropofahat
– dropofahat, Commented Dec 6, 2017 at 20:03

Alex Poole · Accepted Answer · 2017-12-07 14:56:25Z

2

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".

edited Dec 7, 2017 at 14:56

answered Dec 6, 2017 at 19:32

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dropofahat Over a year ago

Agreed Alex that's the way to solve it with a join. What does not feel right to me is that the original query already accesses all of the data needed to satisfy the results but because Oracle's path expression syntax is incomplete, a second query of the same records is needed to from the join. I was hoping it was a gap in my understanding of path expressions. Release 3 perhaps?

Alex Poole Over a year ago

It isn’t doing a second query though; the jsonfield value is exploded by the json_table() but it doesn’t join back to the original table again. The cross join is the same mechanism used for XMLTable(). This may be more efficient than multiple json_query() calls (which dot notation would do anyway, I believe).

Alex Poole Over a year ago

Looks like my 12cR2 guess isn't right; gets ORA-40553, that syntax only seems to be allowed in json_exists() calls. The other version still works of course.

dropofahat Over a year ago

The Release 1 solution worked well and the query plan confirmed that there is no additional I/O joining to the json_table. Thanks again.

Collectives™ on Stack Overflow

Using Oracle Path Expressions on JSON arrays

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related