Using WHERE EXISTS with JSON
As we saw in the previous section, different rows in the same table can have dissimilar information in them when a column type is JSON. To identify whether desired information exists for a given JSON column, use the EXISTS
operator.
For example, some of the JSON persons have a zip code entered for their address, and others do not. Use this query to see all the users with a zipcode:
sql-> SELECT id, j.person.address AS Address FROM JSONPersons j
WHERE EXISTS j.person.address.zipcode;
+----+--------------------------------+
| id | Address |
+----+--------------------------------+
| 2 | city | Beloit |
| | phones |
| | areacode | 339 |
| | number | 1684972 |
| | type | home |
| | state | WI |
| | street | 187 Hill Street |
| | zipcode | 53511 |
+----+--------------------------------+
| 1 | city | Antioch |
| | phones |
| | areacode | 423 |
| | number | 8634379 |
| | type | home |
| | state | TN |
| | street | 150 Route 2 |
| | zipcode | 37013 |
+----+--------------------------------+
2 rows returned
When querying data for inconsistencies, it is often more useful to see all rows where information is missing by using WHERE NOT EXISTS
:
sql-> SELECT * FROM JSONPersons j WHERE NOT EXISTS j.person.lastname;
+----+-------------------+
| id | person |
+----+-------------------+
| 7 | myarray |
| | 1 |
| | 2 |
| | 3 |
| | 4 |
| | mynumber | 5 |
+----+-------------------+
| 6 | myarray |
| | 1 |
| | 2 |
| | 3 |
| | 4 |
| | mynumber | 5 |
+----+-------------------+
1 row returned