Versión de prueba de IRIS 2024.1 - Nueva funcionalidad

Hay una nueva funcionalidad muy interesante en la recientemente anunciada versión de prueba 2024.1 de InterSystems IRIS - JSON_TABLE. JSON_TABLE forma parte de una familia de funciones introducidas en la versión 2016 del estándar SQL (ISO Standard, publicado a principios de 2017). Permite que valores JSON se mapeen a columnas y se consulten usando SQL. JSON_TABLE puede usarse en la cláusula FROM de algunas sentencias SQL.

La sintaxis de JSON_TABLE es bastante extensa, permitiendo condiciones excepcionales cuando los valores JSON proporcionados no cumplen las expectativas, estructuras anidadas, etc.

La sintaxis básica es bastante sencilla. El primer argumento pasado a JSON_TABLE es el valor JSON que se va a consultar. Puede ser un literal, una referencia a una columna o una llamada a una función. El segundo argumento es una expresión JPL (JSON Path Language), que permite filtrar el valor proporcionado. Después va la cláusula COLUMNS - justo en medio de la llamada a la función. La cláusula COLUMNS se parece mucho a las definiciones de columnas en una sentencia CREATE TABLE pero con un añadido muy necesario - el PATH. PATH es una expresión JPL que se aplica al valor JSON para producir el valor de la columna.

Este es un ejemplo para demostrar un mapeo sencillo:

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

2 Rows(s) Affected

JSON_TABLE también permite que una columna proporcione el valor JSON. Este valor después se puede volver a unir a la fila que lo contenía, usando algo llamado una lateral join (unión lateral). Este ejemplo crea una tabla sencilla y la rellena con unas pocas filas con valores interesantes.

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;

9 Rows(s) Affected