New post
Article
· Feb 22 4m read

IRIS 2024.1 Preview - New Feature

#JSON #SQL #InterSystems IRIS

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

 

state_name capital_name
New York Albany
Wyoming Cheyenne

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;

 

ID name street city state postal_code phone_type country_code phone_nbr
1 Caroline One Memorial Cambridge MA 02142 office (617) 225-5555
1 Caroline One Memorial Cambridge MA 02142 mobile (617) 555-1111
2 Doe, John 123 Main Street Cambridge MA 02142 mobile 1 999-999-9999
2 Doe, John 123 Main Street Cambridge MA 02142 work 1 888-888-8888
3 Smith, Georgia 100 Privet Lane, Unit 20 Melrose MA 02176 mobile 1 555-867-5309
3 Smith, Georgia 100 Privet Lane, Unit 20 Melrose MA 02176 home 1 555-123-1234
3 Smith, Georgia 100 Privet Lane, Unit 20 Melrose MA 02176 office 555-000-0000
4 Jones, Thomas 63 North Park Way Princeton NJ 08540 mobile 1 555-555-5555
4 Jones, Thomas 63 North Park Way Princeton NJ 08540 work 1 555-BR5-4949

9 Rows(s) Affected

Discussion (4)2
Log in or sign up to continue
Pierre LaFay · Feb 23

Is it possible to use value for search condition like this :

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'
    )
    )
WHERE country_code = 1

If yes, I think this might not be very efficient, as it has to interpret JSON expressions for each row in the table, without using indexes.

0 0
Dan Pasco · Feb 23

Yes, of course. JSON_TABLE in the FROM clause is just another table and can be used as such. If you have wish to use indexes on JSON values stored in columns then you can always define indexes on computed values. That has been possible even before JSON_TABLE. Keep in mind that dynamic data does not always follow expectations as a field in a dynamic object can be a literal, another object or an array of values.

I previously posted about ASQ. You can use ASQ to derive a computed value in your class definition.

0 0