IRIS 2024.1 Preview - Nouvelle fonctionnalité

Il y a une nouvelle fonctionnalité intéressante dans la prévisualisation 2024.1 récemment annoncée, JSON_TABLE. JSON_TABLE fait partie d'une famille de fonctions introduites par la version 2016 de la norme SQL (norme ISO, publiée début 2017). Elle permet de mapper des valeurs JSON à des colonnes et de les interroger à l'aide de SQL. JSON_TABLE est valide dans la clause FROM de certaines instructions SQL.

La syntaxe de JSON_TABLE est assez large, permettant des conditions exceptionnelles où les valeurs JSON fournies ne correspondent pas aux attentes, des structures imbriquées, etc.

La syntaxe de base est assez simple. Le premier argument transmis à JSON_TABLE est la valeur JSON à interroger. Il peut s'agir d'un littéral, d'une référence de colonne ou d'un appel de fonction. Le deuxième argument est une expression JPL (JSON path language) qui permet de filtrer la valeur fournie. Vient ensuite la clause COLUMNS, en plein milieu d'un appel de fonction. La clause COLUMNS ressemble beaucoup aux définitions de colonnes d'une déclaration CREATE TABLE, mais avec un ajout indispensable : le PATH. PATH est une expression JPL qui est appliquée à la valeur JSON pour produire la valeur de la colonne.

Il s'agit d'un exemple visant à démontrer un mappage simple.

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 permet également à une colonne de fournir la valeur JSON. Cette valeur peut ensuite être reliée à la ligne qui la contient à l'aide de ce que l'on appelle une *jointure latérale*. Cet exemple crée une table simple et la remplit avec quelques lignes contenant des valeurs intéressantes.

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