JSON in PL/SQL: object types and 26ai features

Order A-17 reaches a package as JSON text with two items of quantity 1 and 2, and the package must add "total_qty": 3 before it passes the order on. JSON in PL/SQL does that with the JSON object types, which parse the text into an object the code can read and change, then turn it back into text.

You will total the order with them step by step, then meet the 26ai features that PL/SQL code uses first: SQL BOOLEAN, IF [NOT] EXISTS, SELECT without FROM, VALUES rows and records that become JSON, each with what a 19c site uses instead.

JSON object types turn text into a tree you can change

JSON object typesJSON object types: The PL/SQL object types for JSON: JSON_ELEMENT_T and its subtypes JSON_OBJECT_T, JSON_ARRAY_T and JSON_SCALAR_T, which hold a parsed document in memory so that code can read, change and serialise it. hold a JSON document in memory. JSON_OBJECT_T.parse reads the order’s text and returns an object; get functions read its fields, put changes them and to_String turns the whole object back into text. Oracle’s JSON Data Structures page lists five types:

Totalling the order takes six calls, two of them inside a loop. Step through them:

Positions in a JSON_ARRAY_T start at 0: Oracle’s own example walks an array with FOR i IN 0 .. get_size - 1. get(i) returns each item as a JSON_ELEMENT_T, so TREAT makes it a JSON_OBJECT_T before get_Number('qty') reads it, and put adds total_qty as the last key. Here is the block, then a Python model of the same steps that prints each position it reads. Press Edit, set FIRST_POSITION = 1 and run it again:

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Order A-17 arrives as JSON text. The block parses it into JSON object types, reads every item of the "items"
-- array, adds the quantities and puts the total back into the document as "total_qty", then serialises it again.
-- It changes no table, so it can run at any time.
SET SERVEROUTPUT ON
SET LINESIZE 120

DECLARE
  v_order JSON_OBJECT_T;
  v_items JSON_ARRAY_T;
  v_item  JSON_OBJECT_T;
  v_total NUMBER := 0;
BEGIN
  v_order := JSON_OBJECT_T.parse(
    '{"order":"A-17","items":[{"sku":"RICE-5K","qty":1},{"sku":"TEA-250","qty":2}]}');
  v_items := v_order.get_Array('items');

  -- Array positions start at 0, as Oracle's own examples loop: 0 .. get_size - 1.
  FOR i IN 0 .. v_items.get_size - 1 LOOP
    v_item  := TREAT(v_items.get(i) AS JSON_OBJECT_T);
    v_total := v_total + v_item.get_Number('qty');
  END LOOP;

  v_order.put('total_qty', v_total);
  DBMS_OUTPUT.PUT_LINE('items: ' || v_items.get_size);
  DBMS_OUTPUT.PUT_LINE(v_order.to_String);
END;
/
-- Expected:
-- items: 2
-- {"order":"A-17","items":[{"sku":"RICE-5K","qty":1},{"sku":"TEA-250","qty":2}],"total_qty":3}

"""order_json.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("json/order_json.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @json/order_json.sql
items: 2
{"order":"A-17","items":[{"sku":"RICE-5K","qty":1},{"sku":"TEA-250","qty":2}],"total_qty":3}

PL/SQL procedure successfully completed.

"""Order A-17 through the steps of order_json.sql, modelled in Python.

A model, not PL/SQL: Python's json module stands in for JSON_OBJECT_T and JSON_ARRAY_T. It parses the text, reads the
"items" array from position FIRST_POSITION to the last one (positions start at 0, as in Oracle's own examples), adds
the quantities, puts "total_qty" at the end of the object, as JSON_OBJECT_T.put does for a new key, and serialises the
document without spaces, as to_String does.

Press Edit, change a quantity in ORDER, or set FIRST_POSITION = 1 (the slip of counting from 1), and run it again.
"""

import json

ORDER = '{"order":"A-17","items":[{"sku":"RICE-5K","qty":1},{"sku":"TEA-250","qty":2}]}'
FIRST_POSITION = 0

order = json.loads(ORDER)
items = order["items"]
total = 0
for position in range(FIRST_POSITION, len(items)):
    item = items[position]
    total += item["qty"]
    print(f"position {position}: {item['sku']} qty {item['qty']}, running total {total}")

order["total_qty"] = total
print(f"items: {len(items)}")
print(json.dumps(order, separators=(",", ":")))
done = True

position 0: RICE-5K qty 1, running total 1
position 1: TEA-250 qty 2, running total 3
items: 2
{"order":"A-17","items":[{"sku":"RICE-5K","qty":1},{"sku":"TEA-250","qty":2}],"total_qty":3}

The 19c reference documents the same five types, so JSON in PL/SQL written with them runs on both releases. To look at a document before you write code for it, format it in the JSON formatter.

What 26ai adds that PL/SQL code meets first

Oracle’s 26ai New Features guide and the 26ai PL/SQL changes list five features that everyday PL/SQL code meets. Each does one plain thing, and the last column gives what a 19c database offers instead:

A VALUES clause like that is a table value constructortable value constructor: A VALUES clause that builds rows on the fly without a table, such as VALUES (1), (2), (4). Oracle accepts it in SELECT, INSERT and MERGE statements from 26ai., which Oracle accepts in SELECT, INSERT and MERGE. The releases, in the order they brought these features:

Three larger 26ai features belong to other tracks: SQL domains declare what a column holds, such as an email address; JSON-relational duality views show the same data as documents or as rows; AI Vector Search queries data by meaning. Three topics of this lesson go deeper. A record or collection becomes JSON in a shape that depends on its kind. A get call has a fixed behaviour when a key is missing or a value has the wrong type. And conditional compilation lets one source compile on both releases.

Records and collections as JSON, kind by kind, and the checks on the way back Pro: opens with a Pro, Premium or Ultimate pass

Silent NULLs, shared references, and one source for 19c and 26ai Pro: opens with a Pro, Premium or Ultimate pass

Practise: read the tree and pick the release

Each question gives a call, a loop or a statement. Say what it returns, or which release runs it:

Interview questions

Warm-up (fresher to 2–5 years): when do you reach for JSON_OBJECT_T.parse, and when for JSON_VALUE?

Parse when PL/SQL must read or change several parts of one document: JSON_OBJECT_T.parse builds the whole tree, and the code walks it, tests keys with has, changes it with put and serialises it with to_String. Use JSON_VALUE when one scalar is enough: it is a SQL/JSON function that takes a path such as '$.order' and returns that single value, in a query or in PL/SQL. Building a document piece by piece needs the object types, because JSON_VALUE only reads.

Three longer questions follow: a renamed field that valued orders at zero, one package for 19c and 26ai sites, and a batch that parses the same document again for every field:

Key takeaways

• JSON in PL/SQL starts with JSON_OBJECT_T.parse: get functions read the tree, put changes it and to_String turns it back into text.
• Array positions start at 0, and get(i) returns a JSON_ELEMENT_T that TREAT turns into the subtype you need.
• 26ai adds SQL BOOLEAN, SELECT without FROM, VALUES rows and JSON for PL/SQL records and collections, while IF [NOT] EXISTS reaches 19c from Release Update 19.28.
• The JSON object types are documented on 19c too, so code written with them runs on both releases.