Previous Next JavaScript must be enabled to correctly display this content

## 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