Búsqueda de valores en matrices JSON

Modo de enfoque

Búsqueda de valores en matrices JSON - Amazon Athena

Para determinar si un valor concreto existe dentro de una matriz con codificación JSON, utilice la función json_array_contains.

En la siguiente consulta se enumeran los nombres de los usuarios que participan en "project2".


WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}')
  ) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_array_contains(json_extract(users, '$.projects'), 'project2')

Esta consulta devuelve una lista de usuarios.


+-------------+
| user        |
+-------------+
| Susan Smith |
+-------------+
| Jane Smith  |
+-------------+

El siguiente ejemplo de consulta muestra los nombres de los usuarios que han completado proyectos junto con el número total de proyectos completados. Realiza las siguientes acciones:

Utiliza instrucciones SELECT anidadas para facilitar la claridad.
Extrae la matriz de proyectos.
Convierte la matriz en una matriz nativa de pares de clave-valor utilizando CAST.
Extrae cada elemento individual de la matriz utilizando el operador UNNEST.
Filtra los valores obtenidos por proyectos completados y los cuenta.

nota

Cuando utilice CAST para ejecutar MAP, puede especificar el elemento clave como VARCHAR (cadena nativa en Presto), pero dejar el valor como JSON, ya que los valores de MAP son de tipos diferentes: cadena para el primer par clave-valor y booleano para el segundo.


WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith",
             "org": "legal",
             "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith",
             "org": "engineering",
             "projects": [{"name":"project2", "completed":true},
                          {"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith",
             "org": "finance",
             "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
),
employees AS (
  SELECT users, CAST(json_extract(users, '$.projects') AS
    ARRAY(MAP(VARCHAR, JSON))) AS projects_array
  FROM dataset
),
names AS (
  SELECT json_extract_scalar(users, '$.name') AS name, projects
  FROM employees, UNNEST (projects_array) AS t(projects)
)
SELECT name, count(projects) AS completed_projects FROM names
WHERE cast(element_at(projects, 'completed') AS BOOLEAN) = true
GROUP BY name

Esta consulta devuelve el siguiente resultado:


+----------------------------------+
| name        | completed_projects |
+----------------------------------+
| Susan Smith | 2                  |
+----------------------------------+
| Jane Smith  | 1                  |
+----------------------------------+