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