Access JSON Values with Dot Notation

Let's say you've got some data:

create table student(id number, data CLOB, check (data IS JSON));
insert into student values (1, '{"first": "Jason", "last": "Stortz", "age": 47}');
insert into student values (2, '{"first": "Tim", "last": "Wetzel", "age": 31}');
insert into student values (3, '{"first": "Frank", "last": "Simmons", "age": 41}');        

Now, if you want to show the IDs and the first name in a query you COULD do this:

SELECT
  s.id,
  jt.*
FROM
  student s,
  JSON_TABLE(s.data, '$' 
  columns(
    first path '$.first'
  )) jt;        

But this is so much cleaner:

SELECT
  s.id, s.data.first
FROM
  student s;        

I first learned about access JSON data in Oracle Database columns using JSON_TABLE, but dot notation is so much cleaner and simpler!

Note: You need to use the table alias or it won't work.

Working with JSON data in your #orclAPEX tables? Give this a try for cleaner queries.