Building JSON from tables

Web apps, REST APIs and message queues all speak JSON. Oracle can build it straight from rows, so the app gets a finished document instead of a pile of rows to stitch together.

• JSON_OBJECT('key' VALUE expr, ...) makes {"key": value, ...} from one row.
• JSON_ARRAYAGG(expr ORDER BY ...) is an aggregate: it turns many rows into one [...] array.

The test case

A scorecard document for a match: its id, the winner's code, and an array of innings, each with the batting team, runs and wickets. Show matches 10 (the tie) and 32 (the 2026 final).

Expected result

Match 10 has "winner":null: a tie, 194 apiece. JSON has a word for "nobody"; cricket has a whole Super Over.

How it's built

Way 1: JSON_OBJECT and JSON_ARRAYAGG

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
  where  b.match_id in (10, 32)
  group  by b.match_id, b.innings_no, t.team_code
)
select m.match_id,
       json_object(
         'match'   value m.match_id,
         '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 varchar2(4000))
                          from   inn i
                          where  i.match_id = m.match_id) format json
         returning varchar2(4000)) as doc
from   cr_matches m
left   join cr_teams w on w.team_id = m.winner_id
where  m.match_id in (10, 32)
order  by m.match_id;

• FORMAT JSON tells the outer JSON_OBJECT that the subquery's text is already JSON, so it's embedded as an array, not as a quoted string full of backslashes.
• RETURNING VARCHAR2(4000) asks for plain text. Without it, 26ai returns its native JSON type (page 11.2), which tools display differently.
• A null value comes out as null. Add ABSENT ON NULL to leave the key out instead.

Way 2: gluing strings together

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
  where  b.match_id in (10, 32)
  group  by b.match_id, b.innings_no, t.team_code
)
select m.match_id,
       '{"match":' || m.match_id
       || ',"winner":' || nvl2(w.team_code, '"' || w.team_code || '"', 'null')
       || ',"innings":['
       || (select listagg('{"no":' || i.innings_no || ',"team":"' || i.team_code
                          || '","runs":' || i.runs || ',"wickets":' || i.wickets || '}', ',')
                  within group (order by i.innings_no)
           from   inn i where i.match_id = m.match_id)
       || ']}' as doc
from   cr_matches m
left   join cr_teams w on w.team_id = m.winner_id
where  m.match_id in (10, 32)
order  by m.match_id;

Same text, and you'll see this in older code. It works until a team is called Leeds "Larks" or a name contains a backslash, and then the JSON is broken. JSON_OBJECT escapes everything for you.

19c and 26ai

Way 1 and 2 run on both. 26ai adds a shorter constructor, JSON {'match' : m.match_id, ...}, and JSON_OBJECT(*) to turn a whole row into an object.

Gotchas

• The 4000-byte limit. VARCHAR2(4000) is enough for a scorecard; for big documents use RETURNING CLOB.
• Numbers vs strings. JSON_OBJECT('runs' VALUE '160') gives "160" (a string); pass a number to get 160.
• Key order is what you write, but JSON consumers shouldn't depend on it.