Using Searched Case
A searched case expression can be helpful in identifying specific problems with the JSON data in your JSON columns. The example data we have been using in this chapter sometimes provides a JSONPersons.address field, and sometimes it does not. When an address is present, sometimes it provides a zipcode, and sometimes it does not. We can use a searched case expression to identify and describe the specific problem with each row.
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 We can improve the report by adding a third column that uses a second searched case expression:
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 Finally, it is possible to nest search case expressions. Our sample data also has a spurious null in the phones array (see id 4). We can report that in the following way (output is modified slightly to fit in the space allowed):
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