IRIS 2024.1 Preview - New Feature

There is an interesting new feature in the recently announced 2024.1 preview, JSON_TABLE. JSON_TABLE is one of a family of functions introduced by the 2016 version of the SQL Standard (ISO Standard, published in early 2017). It allows for JSON values to be mapped to columns and queried using SQL. JSON_TABLE is valid in the FROM clause of some SQL statements.

The syntax of JSON_TABLE is quite large, allowing for exceptional conditions where the provided JSON values don't match expectations, nested structures and so on.

The basic syntax is quite straightforward. The first argument passed to the JSON_TABLE is the JSON value that is to be queried. It can be a literal, a column reference, or a function call. The second argument is a JPL (JSON path language) expression that allows for filtering the provided value. Then comes the COLUMNS clause - right in the middle of a function call. The COLUMNS clause looks very much like the columns definitions in a CREATE TABLE statement but with one very necessary addition - the PATH. PATH is a JPL expression that is applied to the JSON value to produce the column value.

This is an example just to demonstrate a simple mapping.

SELECT * 
    FROM JSON_TABLE('[{"name":"New York","capital":"Albany"},{"name":"Wyoming","capital":"Cheyenne"}]', '$'
            COLUMNS(
                state_name VARCHAR(40) PATH '$.name',
                capital_name VARCHAR(40) PATH '$.capital'
            )
        ) as s
SQL
SQL

2 Rows(s) Affected

JSON_TABLE also allows for a column to provide the JSON value. This value can then be joined back to its containing row using something called a lateral join. This example creates a simple table and populates it with a few rows with interesting values.

DROP TABLE demo_intersystems.Person;

CREATE TABLE demo_intersystems.Person (
                                          name VARCHAR(40),
                                          street VARCHAR(60),
                                          city VARCHAR(30),
                                          state VARCHAR(4),
                                          postal_code VARCHAR(10),
                                          phone_nbrs VARCHAR(2000)
);

INSERT INTO demo_intersystems.Person (name, street, city, state, postal_code, phone_nbrs)
    VALUES (
        'Caroline',
        'One Memorial',
        'Cambridge',
        'MA', '02142',
        '[{"type":"office","number":"(617) 225-5555"},{"type":"mobile","number":"(617) 555-1111"}]'
    );

INSERT INTO demo_intersystems.Person (name, street, city, state, postal_code, phone_nbrs)
    VALUES (
        'Doe, John',
        '123 Main Street',
        'Cambridge',
        'MA',
        '02142',
        '[{"type":"mobile","country_code":"1","number":"999-999-9999"},{"type":"work","country_code":"1","number":"888-888-8888"}]'
    );

INSERT INTO demo_intersystems.Person (name, street, city, state, postal_code, phone_nbrs)
VALUES (
    'Smith, Georgia',
    '100 Privet Lane, Unit 20',
    'Melrose',
    'MA',
    '02176',
    '[{"type":"mobile","country_code":"1","number":"555-867-5309"},{"type":"home","country_code":"1","number":"555-123-1234"},{"type":"office","number":"555-000-0000"}]'
    );

INSERT INTO demo_intersystems.Person (name, street, city, state, postal_code, phone_nbrs)
VALUES (
    'Jones, Thomas',
    '63 North Park Way',
    'Princeton',
    'NJ',
    '08540',
    '[{"type":"mobile","country_code":"1","number":"555-555-5555"},{"type":"work","country_code":"1","number":"555-BR5-4949"}]'
    );

SELECT p.%ID, p.name, p.street, p.city, p.state, p.postal_code,
    pn.phone_type, pn.country_code, pn.phone_nbr
FROM demo_intersystems.Person p,
    JSON_TABLE(p.phone_nbrs, '$'
    COLUMNS(
    phone_type VARCHAR(10) path '$.type',
    country_code VARCHAR(8) path '$."country_code"',
    phone_nbr VARCHAR(12) path '$.number'
    )
    ) pn;
SQL
SQL

9 Rows(s) Affected