Extraer datos JSON de cadenas

Es posible que tenga datos de origen que contengan cadenas con codificación JSON que no necesariamente desee deserializar en una tabla en Athena. En este caso, puede seguir ejecutando operaciones SQL en estos datos utilizando las funciones de JSON disponibles en Presto.

Considere esta cadena JSON como un conjunto de datos de ejemplo.

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

Ejemplos: extracción de propiedades

Para extraer las propiedades name y projects de la cadena de JSON, utilice la función json_extract tal y como se muestra en el siguiente ejemplo. La función json_extract toma la columna que contiene la cadena de JSON y realiza una búsqueda en ella utilizando una expresión del tipo JSONPath con la notación de punto .

nota

JSONPath realiza un sencillo recorrido del árbol. Utiliza el signo $ para indicar la raíz del documento JSON, seguido de un punto y un elemento anidado directamente en la raíz como, por ejemplo, $.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

El valor devuelto es una cadena con codificación JSON y no un tipo de datos nativo de Athena.

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

Para extraer el valor escalar de la cadena JSON, utilice la función json_extract_scalar. Es similar a json_extract, pero devuelve únicamente valores escalares (booleanos, números o cadenas).

nota

No utilice la función json_extract_scalar en matrices, mapas o estructuras.

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

Esta consulta devuelve:

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

Para obtener el primer elemento de la propiedad projects de la matriz de ejemplo, utilice la función json_array_get y especifique la posición del índice.

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

Devuelve el valor en la posición de índice especificada en la matriz codificada con JSON.

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

Para obtener un tipo de cadena de Athena, utilice el operador [] dentro de una expresión JSONPath y, a continuación, utilice la función json_extract_scalar. Para obtener más información acerca de [], consulte Acceso a los elementos de la matriz.

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

Devuelve este resultado:

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