Seeking NULLS in Arrays

All arrays found in a JSON input stream are stored internally as ARRAY(JSON). This means that it is possible for the array to have inconsistent types for its members.

In our example, the phones array for user id 4 contains a null element:

sql-> SELECT j.person.address.phones FROM JSONPersons j WHERE j.id=4;
 +--------------------+
 |       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    |
 +--------------------+ 

A way to discover this in your table is to examine the phones array for null values:

sql-> SELECT id, j.person.address.phones FROM JSONPersons j 
WHERE j.person.address.phones[] =any null;
 +----+--------------------+
 | id |       phones       |
 +----+--------------------+
 |  4 | 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    |
 +----+--------------------+

1 row returned 

Notice the use of the array filter step ([]) in the previous query. This is needed to unpack the array into a sequence so that the =any comparison operator can be used with it.