検索Caseの使用方法
検索case式は、JSON列のJSONデータに関する特定の問題を識別するのに役立ちます。この章で使用しているサンプル・データでは、JSONPersons.addressフィールドが提供される場合と、そうでない場合があります。住所が存在する場合、郵便番号が含まれている場合と、そうでない場合があります。検索case式を使用して、各行に関する特定の問題を識別して記述できます。
sql-> SELECT id,
CASE
WHEN NOT EXISTS j.person.address
THEN j.person.keys()
WHEN NOT EXISTS j.person.address.zipcode
THEN "No Zipcode"
ELSE j.person.address.zipcode
END
FROM JSONPersons j;
+----+-----------------------+
| id | Column_2 |
+----+-----------------------+
| 4 | No Zipcode |
+----+-----------------------+
| 3 | No Zipcode |
+----+-----------------------+
| 5 | No Zipcode |
+----+-----------------------+
| 1 | 37013 |
+----+-----------------------+
| 7 | myarray |
| | mynumber |
+----+-----------------------+
| 6 | myarray |
| | mynumber |
+----+-----------------------+
| 2 | 53511 |
+----+-----------------------+
7 rows returned 2番目の検索case式を使用する3番目の列を追加して、レポートを改善できます。
sql-> SELECT id,
CASE
WHEN NOT EXISTS j.person.address
THEN "No Address"
WHEN NOT EXISTS j.person.address.zipcode
THEN "No Zipcode"
ELSE j.person.address.zipcode
END,
CASE
WHEN NOT EXISTS j.person.address
THEN j.person.keys()
ELSE j.person.address
END
FROM JSONPersons j;
+----+-----------------------+------------------------------------+
| id | Column_2 | Column_3 |
+----+-----------------------+------------------------------------+
| 3 | No Zipcode | city | Middleburg |
| | | phones |
| | | areacode | 305 |
| | | number | 1234079 |
| | | type | work |
| | | |
| | | areacode | 305 |
| | | number | 2066401 |
| | | type | home |
| | | state | FL |
| | | street | 187 Aspen Drive |
+----+-----------------------+------------------------------------+
| 2 | 53511 | city | Beloit |
| | | phones |
| | | areacode | 339 |
| | | number | 1684972 |
| | | type | home |
| | | state | WI |
| | | street | 187 Hill Street |
| | | zipcode | 53511 |
+----+-----------------------+------------------------------------+
| 5 | No Zipcode | city | Monroe Township |
| | | phones |
| | | areacode | 201 |
| | | number | 3213267 |
| | | type | work |
| | | |
| | | areacode | 201 |
| | | number | 8765421 |
| | | type | work |
| | | |
| | | areacode | 339 |
| | | number | 3414578 |
| | | type | home |
| | | state | NJ |
| | | street | 427 Linden Avenue |
+----+-----------------------+------------------------------------+
| 1 | 37013 | city | Antioch |
| | | phones |
| | | areacode | 423 |
| | | number | 8634379 |
| | | type | home |
| | | state | TN |
| | | street | 150 Route 2 |
| | | zipcode | 37013 |
+----+-----------------------+------------------------------------+
| 7 | No Address | myarray |
| | | mynumber |
+----+-----------------------+------------------------------------+
| 4 | No Zipcode | city | Leominster |
| | | phones |
| | | areacode | 339 |
| | | number | 4120211 |
| | | type | work |
| | | |
| | | areacode | 339 |
| | | number | 8694021 |
| | | type | work |
| | | |
| | | areacode | 339 |
| | | number | 1205678 |
| | | type | home |
| | | null |
| | | |
| | | areacode | 305 |
| | | number | 8064321 |
| | | type | home |
| | | state | MA |
| | | street | 364 Mulberry Street |
+----+-----------------------+------------------------------------+
| 6 | No Address | myarray |
| | | mynumber |
+----+-----------------------+------------------------------------+
7 rows returned 最後に、検索case式をネストできます。このサンプル・データには、phones配列に偽のnullも含まれています(id 4を参照)。これを次のようにレポートできます(出力はスペースに収まるように少し変更されています)。
sql-> SELECT id,
CASE
WHEN EXISTS j.person.address
THEN
CASE
WHEN EXISTS j.person.address.zipcode
THEN
CASE
WHEN j.person.address.phones[] =any null
THEN "Zipcode exists but null in the phones array"
ELSE j.person.address.zipcode
END
WHEN j.person.address.phones[] =any null
THEN "No zipcode and null in phones array"
ELSE "No zipcode"
END
ELSE "No Address"
END,
CASE
WHEN NOT EXISTS j.person.address
THEN j.person.keys()
ELSE j.person.address
END
FROM JSONPersons j;
+----+------------------------+------------------------------------+
| id | Column_2 | Column_3 |
+----+------------------------+------------------------------------+
| 3 | No zipcode | city | Middleburg |
| | | phones |
| | | areacode | 305 |
| | | number | 1234079 |
| | | type | work |
| | | |
| | | areacode | 305 |
| | | number | 2066401 |
| | | type | home |
| | | state | FL |
| | | street | 187 Aspen Drive |
+----+------------------------+------------------------------------+
| 2 | 53511 | city | Beloit |
| | | phones |
| | | areacode | 339 |
| | | number | 1684972 |
| | | type | home |
| | | state | WI |
| | | street | 187 Hill Street |
| | | zipcode | 53511 |
+----+------------------------+------------------------------------+
| 5 | No zipcode | city | Monroe Township |
| | | phones |
| | | areacode | 201 |
| | | number | 3213267 |
| | | type | work |
| | | |
| | | areacode | 201 |
| | | number | 8765421 |
| | | type | work |
| | | |
| | | areacode | 339 |
| | | number | 3414578 |
| | | type | home |
| | | state | NJ |
| | | street | 427 Linden Avenue |
+----+------------------------+------------------------------------+
| 1 | 37013 | city | Antioch |
| | | phones |
| | | areacode | 423 |
| | | number | 8634379 |
| | | type | home |
| | | state | TN |
| | | street | 150 Route 2 |
| | | zipcode | 37013 |
+----+------------------------+------------------------------------+
| 7 | No Address | myarray |
| | | mynumber |
+----+------------------------+------------------------------------+
| 4 | No zipcode and null | city | Leominster |
| | in phones array | phones |
| | | areacode | 339 |
| | | number | 4120211 |
| | | type | work |
| | | |
| | | areacode | 339 |
| | | number | 8694021 |
| | | type | work |
| | | |
| | | areacode | 339 |
| | | number | 1205678 |
| | | type | home |
| | | null |
| | | |
| | | areacode | 305 |
| | | number | 8064321 |
| | | type | home |
| | | state | MA |
| | | street | 364 Mulberry Street |
+----+------------------------+------------------------------------+
| 6 | No Address | myarray |
| | | mynumber |
+----+------------------------+------------------------------------+
7 rows returned