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.

DROPTABLE demo_intersystems.Person;
CREATETABLE demo_intersystems.Person (
nameVARCHAR(40),
street VARCHAR(60),
city VARCHAR(30),
state VARCHAR(4),
postal_code VARCHAR(10),
phone_nbrs VARCHAR(2000)
);
INSERTINTO 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"}]'
);
INSERTINTO 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"}]'
);
INSERTINTO 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"}]'
);
INSERTINTO 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