Recherche de valeurs dans les tableaux JSON

Afin de déterminer si une valeur spécifique existe dans un tableau codé au format JSON, utilisez la fonction json_array_contains.

La requête suivante répertorie les noms des utilisateurs qui participent à « 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')

Cette requête renvoie une liste d'utilisateurs.

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

L'exemple de requête suivant répertorie les noms des utilisateurs qui ont terminé des projets, ainsi que le nombre total de projets terminés. La requête effectue les actions suivantes :

• Utilise les instructions imbriquées SELECT pour plus de clarté.

• Extrait le tableau de projets.

• Convertit le tableau en un tableau natif de paires clé-valeur utilisant CAST.

• Extrait chaque élément individuel du tableau à l'aide de l'opérateur UNNEST.

• Filtre les valeurs obtenues en fonction des projets terminés et en fournit un décompte.

Note

Lorsque vous utilisez CAST pour MAP, vous pouvez spécifier VARCHAR en tant qu'élément clé (chaîne native dans Presto), mais laisser JSON pour la valeur, car les valeurs de MAP sont de types différents : chaîne pour la première paire clé-valeur, et booléen pour la seconde.

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

Cette requête renvoie le résultat suivant :

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