Estrazione di dati JSON dalle stringhe

È possibile che alcuni dei dati di origine che contengono stringhe codificate JSON non debbano essere necessariamente deserializzate in una tabella Athena. In tal caso, è comunque possibile eseguire operazioni SQL su questi dati utilizzando le funzioni JSON disponibili in Presto.

Considera questa stringa JSON come un set di dati di esempio.

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"name":"project1", "completed":false},
     {"name":"project2", "completed":true}
    ]
}

Esempi: proprietà di estrazione

Per estrarre le proprietà name e projects dalla stringa JSON, utilizza la funzione json_extract, come nell'esempio seguente. La funzione json_extract analizza la colonna contenente la stringa JSON e vi esegue la ricerca impiegando un'espressione LIKE JSONPath con la notazione del punto ..

Nota

JSONPath esegue un attraversamento semplice della struttura. utilizzando il simbolo $ per indicare la radice del documento JSON, seguita da un punto e da un elemento nidificato direttamente sotto il livello radice, ad esempio $.name.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract(myblob, '$.name') AS name,
  json_extract(myblob, '$.projects') AS projects
FROM dataset

Il valore restituito è una stringa con codifica JSON e non un tipo di dati Athena nativo.

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

Per estrarre il valore scalare dalla stringa JSON, utilizza la funzione json_extract_scalar. Quest'ultima è simile a json_extract, ma restituisce solo valori scalari (booleani, numeri o stringhe).

Nota

Non utilizzare la funzione json_extract_scalar su matrici, mappe o strutture.

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract_scalar(myblob, '$.name') AS name,
  json_extract_scalar(myblob, '$.projects') AS projects
FROM dataset

Questa query restituisce:

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

Per ottenere il primo elemento della proprietà projects nella matrice di esempio, utilizza la funzione json_array_get e specifica la posizione indice.

WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item
FROM dataset

Restituirà il valore nella posizione indice specificata nella matrice codificata JSON.

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

Per restituire un tipo di stringa Athena, utilizza l'operatore [] all'interno di un'espressione JSONPath, quindi utilizza la funzione json_extract_scalar. Per ulteriori informazioni su [], consulta Accesso agli elementi della matrice.

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

Tale operazione restituisce questo risultato:

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+