Pesquisar valores em matrizes JSON
Para determinar se um valor específico existe dentro de uma matriz codificada em JSON, use a função json_array_contains
.
A consulta a seguir lista os nomes dos usuários que estão participando de "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')
Essa consulta retorna uma lista de usuários.
+-------------+
| user |
+-------------+
| Susan Smith |
+-------------+
| Jane Smith |
+-------------+
O exemplo de consulta a seguir lista os nomes de usuários que concluíram projetos com o número total de projetos realizados. Ele realiza estas ações:
-
Usa instruções
SELECT
aninhadas para fins de clareza. -
Extrai a matriz de projetos.
-
Converte a matriz em uma matriz nativa de pares de chave/valor usando
CAST
. -
Extrai cada elemento de matriz individual usando o operador
UNNEST
. -
Filtra valores obtidos por projetos concluídos e os conta.
nota
Ao usar CAST
em MAP
, você pode especificar o elemento de chave como VARCHAR
(String nativa no Presto), mas deixar o valor como JSON, porque os valores no MAP
são de tipos diferentes: string para o primeiro par de chave/valor e Booliano para o 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 retorna o seguinte resultado:
+----------------------------------+
| name | completed_projects |
+----------------------------------+
| Susan Smith | 2 |
+----------------------------------+
| Jane Smith | 1 |
+----------------------------------+