JSON 배열에서 값 검색

특정 값이 JSON 인코딩 배열 내에 있는지 알아보려면 json_array_contains 함수를 사용합니다.

다음 쿼리는 "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')

이 쿼리는 사용자 목록을 반환합니다.

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

다음 쿼리 예제는 완료된 프로젝트의 총 개수와 함께 프로젝트를 완료한 사용자의 이름을 나열합니다. 그리고 이러한 작업을 수행합니다.

• 명확성을 위해 중첩 SELECT 설명을 사용합니다.

• 프로젝트의 배열을 추출합니다.

• CAST를 사용하여 배열을 키-값 페어의 기본 배열로 변환합니다.

• UNNEST 연산자를 사용하여 각각의 배열 요소를 추출합니다.

• 획득한 값을 완료된 프로젝트별로 필터링하고 개수를 셉니다.

참고

MAP에 CAST를 사용할 때 키 요소를 VARCHAR(Presto의 기본 문자열)로 지정할 수 있지만 값은 JSON으로 남겨 둡니다. MAP의 값 유형이 서로 다르기 때문입니다(첫 번째 키-값 페어는 문자열, 두 번째는 부울).

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

이 쿼리는 다음 결과를 반환합니다.

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