Reading JSON: JSON_VALUE, JSON_TABLE and dot notation

The other direction: documents arrive (from an API, a queue, a file) and you need rows and columns.

The documents

One scorecard per match, built the way page 11.0 does it, stored in a column checked with IS JSON:

Setup

begin
  for t in (select table_name from user_tables where table_name = 'D111_DOCS') loop
    execute immediate 'drop table d111_docs purge';
  end loop;
end;
/

Setup

create table d111_docs (
  match_id number primary key,
  doc      clob constraint d111_docs_json check (doc is json)
);

Setup

insert into d111_docs (match_id, doc)
with inn as (
  select b.match_id, b.innings_no, t.team_code, sum(b.total_runs) as runs, sum(b.is_wicket) as wickets
  from   cr_balls_v b
  join   cr_teams t on t.team_id = b.batting_team_id
  group  by b.match_id, b.innings_no, t.team_code
)
select m.match_id,
       json_object('match' value m.match_id, 'season' value m.season, 'winner' value w.team_code,
                   'innings' value (select json_arrayagg(json_object('no' value i.innings_no, 'team' value i.team_code,
                                                                     'runs' value i.runs, 'wickets' value i.wickets)
                                                         order by i.innings_no returning clob)
                                    from inn i where i.match_id = m.match_id) format json
                   returning clob)
from   cr_matches m
left   join cr_teams w on w.team_id = m.winner_id;

32 documents like {"match":32,"season":2026,"winner":"LDS","innings":[{"no":1,"team":"CHN",...},...]}.

Test 1: one value per document

For the last four matches of 2026: the winner, and the first innings' team and runs.

Way 1: JSON_VALUE

select match_id,
       json_value(doc, '$.winner')                          as winner,
       json_value(doc, '$.innings[0].team')                 as first_team,
       json_value(doc, '$.innings[0].runs' returning number) as first_runs
from   d111_docs
where  match_id >= 29
order  by match_id;

Paths start at $ (the whole document). Arrays count from 0, so innings[0] is the first innings.

Way 2: JSON_TABLE

select d.match_id, j.winner, j.first_team, j.first_runs
from   d111_docs d,
       json_table(d.doc, '$'
         columns (winner     varchar2(3) path '$.winner',
                  first_team varchar2(3) path '$.innings[0].team',
                  first_runs number      path '$.innings[0].runs')) j
where  d.match_id >= 29
order  by d.match_id;

One pass over each document for all the columns, instead of one JSON_VALUE per column.

Way 3: dot notation

select d.match_id, d.doc.winner as winner, d.doc.innings[0].team as first_team,
       d.doc.innings[0].runs as first_runs
from   d111_docs d
where  d.match_id >= 29
order  by d.match_id;

The shortest. It needs the table alias (d.) and the IS JSON check constraint, and returns text (on 19c) whatever the value looks like.

Test 2: one row per array element

Both innings of the two finals (matches 16 and 32): team, runs and wickets.

Way 1: JSON_TABLE with NESTED PATH

select j.*
from   d111_docs d,
       json_table(d.doc, '$'
         columns (match_id number path '$.match',
                  nested path '$.innings[*]'
                    columns (innings_no number      path '$.no',
                             team       varchar2(3) path '$.team',
                             runs       number      path '$.runs',
                             wickets    number      path '$.wickets'))) j
where  d.match_id in (16, 32)
order  by j.match_id, j.innings_no;

NESTED PATH '$.innings[*]' makes one row per innings, repeating the outer columns (match_id) on each, like a join between the document and its own array.

Way 2: JSON_TABLE straight at the array

select d.match_id, j.innings_no, j.team, j.runs, j.wickets
from   d111_docs d,
       json_table(d.doc, '$.innings[*]'
         columns (innings_no number      path '$.no',
                  team       varchar2(3) path '$.team',
                  runs       number      path '$.runs',
                  wickets    number      path '$.wickets')) j
where  d.match_id in (16, 32)
order  by d.match_id, j.innings_no;

If you only need the array's fields (plus columns from the table itself), start the row path at the array.

Test 3: filtering inside documents

Matches where some innings reached 190 runs.

Way 1: JSON_EXISTS with a filter

select match_id, json_value(doc, '$.winner') as winner
from   d111_docs
where  json_exists(doc, '$.innings[*]?(@.runs >= 190)')
order  by match_id;

?( ... ) is a filter: @ is the current innings. "Is there any innings with runs ≥ 190?"

Way 2: EXISTS over JSON_TABLE

select d.match_id, json_value(d.doc, '$.winner') as winner
from   d111_docs d
where  exists (select 1
               from   json_table(d.doc, '$.innings[*]' columns (runs number path '$.runs')) j
               where  j.runs >= 190)
order  by d.match_id;

The same question in plain SQL terms. JSON_EXISTS is shorter and can use a JSON search index.

19c and 26ai

The same on both. On 26ai, dot notation returns typed values from a JSON column, and paths gain more item methods.

Gotchas

• A missing path gives null, silently. $.inings[0].runs (typo) returns null, not an error. Add ERROR ON ERROR while developing.
• JSON_VALUE returns VARCHAR2(4000) by default. Use RETURNING NUMBER (or DATE) when you'll compare or sort, or 10 sorts before 9.
• Without IS JSON on the column, dot notation doesn't work (ORA-00904), and nothing stops bad documents getting in.