Estrarre JSON da MariaDB/MySQL con JSON_TABLE

JSON_TABLE è una funzione built-in di MariaDB e MySQL per fare il parsin del JSON.

Non so da quali versioni dei db trovate la funzione, ed è possibile che ci siano anche delle leggere differenze di comportamento.

Gli esempi qui sotto si riferiscono nello specifico a MariaDB.

La tabella si chiama json_test, ed un unico campo testo.

Nel primo esempio c'è solo questo:

{
  "userId": 1,
  "id": 1,
  "title": "delectus aut autem",
  "completed": false
}

Questa la query che trasforma il JSON in tabella:

SELECT jt.userId,
       jt.id,
       jt.title,
       jt.completed
FROM test_json t,
     JSON_TABLE(
             t.testo, '$'
             COLUMNS (
                 userId INT PATH '$.userId',
                 id INT PATH '$.id',
                 title VARCHAR(255) PATH '$.title',
                 completed BOOLEAN PATH '$.completed'
                 )
     ) AS jt;

E se invece avessi un array tipo questo?

[
    {
        "userId": 1,
        "id": 1,
        "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
        "body": "quia et suscipitnsuscipit recusandae consequuntur expedita et cumnreprehenderit molestiae ut ut quas totamnnostrum rerum est autem sunt rem eveniet architecto"
    },
    {
        "userId": 1,
        "id": 2,
        "title": "qui est esse",
        "body": "est rerum tempore vitaensequi sint nihil reprehenderit dolor beatae ea dolores nequenfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendisnqui aperiam non debitis possimus qui neque nisi nulla"
    },
    {
        "userId": 1,
        "id": 3,
        "title": "ea molestias quasi exercitationem repellat qui ipsa sit aut",
        "body": "et iusto sed quo iurenvoluptatem occaecati omnis eligendi aut adnvoluptatem doloribus vel accusantium quis pariaturnmolestiae porro eius odio et labore et velit aut"
    }
]

Possiamo fare così:

SELECT jt.userId,
       jt.id_json,
       jt.title,
       jt.body
FROM test_json t,
     JSON_TABLE(
             t.testo, '$[*]'
             COLUMNS (
                 userId INT PATH '$.userId',
                 id_json INT PATH '$.id',
                 title VARCHAR(255) PATH '$.title',
                 body TEXT PATH '$.body'
                 )
     ) AS jt;

Enjoy!