Reading JSON with SQL on IBM i

A field in a file that holds JSON can be read with SQL, without writing a program to take it apart. The functions are already in the database: JSON_VALUE extracts one value, JSON_TABLE extracts many at once and turns lists into rows.

The article is split into four levels. Each one starts where the previous one ends, and you can stop once you have what you need.

The example file

Throughout the article the file is called WEB_ORDERS and has two fields: ID, a number, and DOC, the JSON of an order that came in from the website. Names and values are made up. There are two records.

ID 1:

{"order":"W-1001",
 "date":"2026-09-28",
 "customer":{"code":"C0042"},
 "lines":[{"item":"A-17","qty":2,"price":12.50},
          {"item":"B-03","qty":1,"price":99.00}]}

ID 2:

{"order":"W-1002",
 "date":"2026-09-29",
 "customer":{"code":"C0007"}}

The second order has no lines: that matters later.

Level 1: getting started

The path

To tell a function which value you want, you write a path: a string that starts at $, the whole document, and goes down from key to key with a dot.

Keys are written exactly as in the JSON, upper and lower case included: $.Order does not find "order".

Reading one value

SELECT ID,
       JSON_VALUE(DOC, '$.order'         RETURNING VARCHAR(10)) AS ORDER_NO,
       JSON_VALUE(DOC, '$.customer.code' RETURNING VARCHAR(10)) AS CUSTOMER
  FROM WEB_ORDERS;

JSON_VALUE takes the field and the path. RETURNING says which type you want the result in: VARCHAR, INTEGER, DECIMAL, DATE and the other SQL types.

Always write RETURNING. Without it, the result is a 2 GB CLOB.

A number is asked for as a number, and a date as a date:

SELECT ID,
       JSON_VALUE(DOC, '$.date'            RETURNING DATE)          AS ORDER_DT,
       JSON_VALUE(DOC, '$.lines[0].price' RETURNING DECIMAL(9, 2)) AS FIRST_PRICE
  FROM WEB_ORDERS;

The second order has no lines, so the price is NULL: a value that is not there is not an error.

Level 2: intermediate

Many values with JSON_TABLE

With JSON_VALUE every value is a call. When there is more than one value, IBM recommends JSON_TABLE, which takes the document apart only once and returns a table with the columns you ask for.

SELECT O.ID, J.*
  FROM WEB_ORDERS O,
       JSON_TABLE(O.DOC, '$'
         COLUMNS (
           ORDER_NO  VARCHAR(10)  PATH '$.order',
           ORDER_DT  DATE         PATH '$.date',
           CUSTOMER  VARCHAR(10)  PATH '$.customer.code'
         )) AS J;

How to read the query:

• JSON_TABLE sits in the FROM next to the file, and receives the O.DOC field of the row being read.
• '$' is the starting point of each result row: here the whole document, so one row per order.
• In COLUMNS each column has a name, a type and a path, which starts from the starting point.
• The normal fields of the file, such as ID, go alongside.

Always write the PATH, even when the key has the same name as the column. Without it, JSON_TABLE looks for the key named after the column in upper case: order_no VARCHAR(10) looks for "ORDER_NO", and the column stays NULL.

Lists: one row per element

To get one row per order line, add NESTED PATH, which goes into the lines list. [*] means "all the elements".

SELECT O.ID, J.*
  FROM WEB_ORDERS O,
       JSON_TABLE(O.DOC, '$'
         COLUMNS (
           ORDER_NO  VARCHAR(10)  PATH '$.order',
           NESTED PATH '$.lines[*]' COLUMNS (
             LINE_NO   FOR ORDINALITY,
             ITEM      VARCHAR(10)   PATH '$.item',
             QTY       INTEGER       PATH '$.qty',
             PRICE     DECIMAL(9, 2) PATH '$.price'
           )
         )) AS J;

• The order data is repeated on each of its lines.
• LINE_NO FOR ORDINALITY numbers the elements of the list, from 1, and restarts for each order.
• Order W-1002 has no lines and appears anyway, once, with the line fields NULL. To leave it out, write WHERE J.ITEM IS NOT NULL. The filter goes on the item and not on LINE_NO, which is 1 there.

Using the result

The result of JSON_TABLE is used like any table: WHERE, ORDER BY, GROUP BY. When only the lines are needed, the starting point can be the list itself. The total per item:

SELECT J.ITEM, SUM(J.QTY * J.PRICE) AS TOTAL
  FROM WEB_ORDERS O,
       JSON_TABLE(O.DOC, '$.lines[*]'
         COLUMNS (
           ITEM   VARCHAR(10)   PATH '$.item',
           QTY    INTEGER       PATH '$.qty',
           PRICE  DECIMAL(9, 2) PATH '$.price'
         )) AS J
 GROUP BY J.ITEM;

Here the order without lines produces nothing: the starting point is the list, and a list that is not there gives no rows.

Reading it efficiently

• One JSON_TABLE instead of many JSON_VALUE. The document is taken apart only once.
• Normal fields first. JSON_TABLE is called once for each row of the file that is read. If the selection can be done on a normal field of the file, such as a load date or a status, do it there: fewer documents need taking apart.
• Extract once, read many times. If the same values are needed every day, copy them once into a normal file, with an INSERT ... SELECT over the same query, and read them from there with indexes, instead of taking the JSON apart on every read.

Level 3: advanced

Errors do not stop the query

With the default settings the JSON functions raise no errors. A value they cannot find, or cannot convert to the requested type, becomes NULL, and a badly written document produces no rows at all. It is convenient, but a wrong value looks like a missing one.

The sections that follow go through the cases one by one.

Finding invalid documents

SELECT ID
  FROM WEB_ORDERS
 WHERE DOC IS NOT JSON;

It returns the records whose JSON is not valid: they are the ones missing from the result of JSON_TABLE. A NULL DOC field does not show up, neither here nor among the valid ones.

Turning errors into errors

ERROR ON ERROR, written after COLUMNS (...), makes the query fail instead of returning NULL or no rows:

SELECT O.ID, J.*
  FROM WEB_ORDERS O,
       JSON_TABLE(O.DOC, '$'
         COLUMNS (
           ORDER_NO  VARCHAR(10)  PATH '$.order',
           ORDER_DT  DATE         PATH '$.date'
         )
         ERROR ON ERROR) AS J;

A missing key stays NULL even so: to turn it into an error, write ERROR ON EMPTY on the column.

When instead you need to tell a missing value from a wrong one without stopping the query, put a fallback value on the column:

QTY INTEGER PATH '$.qty' NULL ON EMPTY DEFAULT -1 ON ERROR

NULL means the key is not there, -1 that it is there but is not an integer.

Dates

JSON_TABLE and JSON_VALUE accept four date formats: ISO yyyy-mm-dd, USA mm/dd/yyyy, EUR dd.mm.yyyy and JIS. The day-first format with slashes, common in Europe, is not among them, and has the same shape as the USA one:

Timestamps in ISO-8601 format come in several variants, and not all of them get through:

The trailing Z, which means UTC, is very common. A timestamp like that is read as a string and converted separately:

SELECT TIMESTAMP(REPLACE(REPLACE(
         JSON_VALUE('{"t":"2026-09-30T10:15:00Z"}', '$.t' RETURNING VARCHAR(40)),
         'T', ' '), 'Z', ''))
  FROM SYSIBM.SYSDUMMY1;

It returns 2026-09-30 10:15:00, the UTC time. In JSON_TABLE the column is declared VARCHAR(40) and the same expression is applied in the SELECT. Before comparing it with the system time, bring it to the system time zone.

Numbers

• Extra decimals are truncated, not rounded: 12.345 read into DECIMAL(9, 2) is 12.34.
• Numbers with a fractional part stop at 15 significant digits. An amount such as 1234567.89 comes back exact, but 1234567890.123456789 read into DECIMAL(31, 9) becomes 1234567890.123460000, and 99999999999999.99, which has 16 digits, becomes 100000000000000.00. Integers come back exact, even at twenty digits, and a decimal written in quotes in the JSON, such as "1234567890.123456789", comes back whole.
• A number that does not fit the type is an error, so by default NULL.
• true and false in a numeric column are 1 and 0, in a character column they are the strings true and false.

The path that finds two values

A path can be written in two modes: lax, the default, and strict. The word goes at the start of the path, in lower case: 'LAX $.order' raises no error, it gives NULL.

lax is forgiving. If a key is applied to a list, it applies it to each element. On the first order lax $.lines.item therefore finds two items, JSON_VALUE can return only one, and the result is NULL with no explanation.

strict is not forgiving, and together with ERROR ON ERROR it says what is wrong:

SELECT JSON_VALUE(DOC, 'strict $.lines.item'
                  RETURNING VARCHAR(10) ERROR ON ERROR)
  FROM WEB_ORDERS;

The answer is SQLSTATE 2203A, SQ16410 SQL/JSON member not found.: the item key is looked up on the list, which has none. The right path is $.lines[0].item for the first item, or JSON_TABLE with NESTED PATH '$.lines[*]' to get them all.

Level 4: expert

What follows is needed less often. These are oddities, tested on a system, that you find out while working with it.

JSON in an IFS file

If the JSON is an IFS file rather than a field in a database file, read it with IFS_READ_UTF8 and pass it to JSON_TABLE:

SELECT J.*
  FROM TABLE(QSYS2.IFS_READ_UTF8(PATH_NAME   => '/exchange/orders.json',
                                 END_OF_LINE => 'NONE')) AS F,
       JSON_TABLE(F.LINE, '$.orders[*]'
         COLUMNS (
           ORDER_NO  VARCHAR(10)  PATH '$.order',
           ORDER_DT  DATE         PATH '$.date'
         )) AS J;

• END_OF_LINE => 'NONE' is essential. Without it, IFS_READ_UTF8 returns one row for each line of the file: a JSON written over several lines arrives in pieces, none of them valid, and JSON_TABLE answers with zero rows.
• IFS_READ_UTF8 does not convert the characters to the job CCSID. IFS_READ and GET_CLOB_FROM_FILE do, and GET_CLOB_FROM_FILE must run under commitment control.

• A BOM breaks the read. Some editors put three bytes, EF BB BF, at the start of UTF-8 files. With those three bytes the JSON is not valid and nothing comes out. You can see them like this:

  SELECT HEX(SUBSTR(F.LINE, 1, 3))
    FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/exchange/orders.json')) AS F;
  

  If the result is EFBBBF, read the file as binary skipping the three bytes, and declare FORMAT JSON because a binary value would be read as BSON:

  SELECT JSON_VALUE(CASE WHEN HEX(SUBSTR(F.LINE, 1, 3)) = 'EFBBBF'
                         THEN SUBSTR(F.LINE, 4) ELSE F.LINE END FORMAT JSON,
                    '$.orders[0].order' RETURNING VARCHAR(10))
    FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/exchange/orders.json')) AS F;
  

  The same expression, with its FORMAT JSON, goes as the first argument of JSON_TABLE.

JSON from a web service

QSYS2.HTTP_GET calls an address and returns the response, which goes straight into JSON_TABLE:

SELECT J.*
  FROM JSON_TABLE(
         QSYS2.HTTP_GET('https://api.example.com/orders?from=2026-09-01',
                        '{"headers":{"Accept":"application/json"},
                          "signalErrors":"true",
                          "connectTimeout":"10",
                          "ioTimeout":"30"}'),
         '$.orders[*]'
         COLUMNS (ORDER_NO VARCHAR(10) PATH '$.order')) AS J;

The second parameter holds the options, themselves written as JSON. Three defaults need changing almost every time:

Other things found out on the first call:

• Options 3 and 34 of 5770SS1 are required. The QSYS2 functions do not start a JVM, unlike the SYSTOOLS ones such as HTTPGETCLOB.
• For HTTPS the default certificate store is /QIBM/USERDATA/ICSS/CERT/SERVER/DEFAULT.KDB, which by default does not exist: create it with Digital Certificate Manager, or point to another one with sslCertificateStoreFile.
• HTTP_GET_BLOB returns binary: reading it as JSON needs FORMAT JSON.
• To send JSON with HTTP_POST, set the Content-Type header: without it, text/xml is sent.
• Who can use these functions is decided by the authority on the QSYS/QSQAXISC service program.

Path oddities

And three things that raise no error:

• There are no filters. A path such as $.lines[*]?(@.qty > 1), which other tools accept, returns NULL here. The filter goes in the WHERE.
• Duplicate keys get through. If an object has the same key twice only one value is read; in the test it was the second, but IBM does not guarantee which. IS JSON WITH UNIQUE KEYS finds them.
• IS JSON accepts a trailing comma, as in {"a":[1,2], }, which the JSON standard does not allow. A document that passes this check may be rejected by another system.

A list of values in one parameter

A JSON can also be just a list, such as ["C0042","C0007","C0113"]. That gives a way to pass a program a variable-length list in a single variable:

SELECT ID
  FROM WEB_ORDERS
 WHERE JSON_VALUE(DOC, '$.customer.code' RETURNING VARCHAR(10))
       IN (SELECT C
             FROM JSON_TABLE(:LIST, '$[*]'
                    COLUMNS (C VARCHAR(10) PATH '$')) AS J);

Instead of an IN built by concatenating strings there is a single statement, with the values passed as data.

Two lists at the same level

If each order also had a notes list, read with a second NESTED PATH next to the lines one, the result would not be the product of the two: IBM combines them with a UNION. Each row has either the order-line columns or the note columns, never both.

JSON_QUERY: a piece of JSON

JSON_VALUE returns only simple values. To get a whole object or list, as JSON text, there is JSON_QUERY:

SELECT JSON_QUERY(DOC, '$.customer' RETURNING VARCHAR(200))
  FROM WEB_ORDERS;

It returns {"code":"C0042"} on the first order. Strings come back with their quotes, and OMIT QUOTES removes them; if the path finds more than one value, WITH ARRAY WRAPPER puts them in a list.

Keeping JSON as BSON

There is no JSON data type: JSON sits in a character field, with any CCSID except 65535. IBM also offers another form, BSON, which is JSON in binary, in a VARBINARY or BLOB field, converted with JSON_TO_BSON. The JSON functions work on BSON internally, so a document already in that form saves one conversion per read. Three caveats: it takes no less space, a decimal converted to BSON stops at 15 digits, and it must never go in a fixed-length binary field.

Which releases it works on

JSON_TABLE, JSON_VALUE, JSON_QUERY, IS JSON, IFS_READ_UTF8 and HTTP_GET are documented for IBM i 7.4, 7.5 and 7.6. The pages do not say which Technology Refresh brought them; for the QSYS2 services the system catalog does:

SELECT ROUTINE_NAME
  FROM QSYS2.SYSROUTINES
 WHERE ROUTINE_SCHEMA = 'QSYS2'
   AND ROUTINE_NAME IN ('IFS_READ_UTF8', 'HTTP_GET');

A missing name is a service that, on that system and at that PTF level, is not there.

Sources

• JSON concepts, IBM i 7.6
• Using JSON_TABLE, IBM i 7.6
• JSON_TABLE, IBM i 7.6
• JSON_VALUE, IBM i 7.6
• JSON_QUERY, IBM i 7.6
• IS JSON predicate, IBM i 7.6
• sql-json-path-expression, IBM i 7.6
• JSON_TO_BSON, IBM i 7.6
• GET_CLOB_FROM_FILE, IBM i 7.6
• IFS_READ, IFS_READ_BINARY and IFS_READ_UTF8, IBM i 7.6
• HTTP functions overview, IBM i 7.6
• HTTP_GET and HTTP_GET_BLOB, with the options table, IBM i 7.6