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
演算子を使用して配列の各要素を抽出します。 -
取得した値をフィルタ処理して完了済みプロジェクトに絞り込み、カウントします。
注記
CAST
を使用して MAP
する場合、キー要素を VARCHAR
(Presto のネイティブ文字列) として指定できますが、値は JSON のままにします。MAP
の値は、最初のキー/値ペアが文字列型で、2 番目のペアがブール型であるなど、さまざまな型になるためです。
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 |
+----------------------------------+