SELECT * FROM

    JSON_TABLE('[{"id": 1,"first_name": "John", "last_name": "Smith","email": ["john.smith@example.com","johnsmith@example.com"], "contact_since": 2010},

    {"id": 2,"first_name": "Jon", "last_name": "Smith","email": ["jon.smith@example.com","jonsmith@example.com"], "new_contact": true, "contact_since": 2015},

    {"id": 3,"first_name": "Johnny", "last_name": "Smith","email": ["johnny.smith@example.com","johnnysmith@example.com"], "new_contact": true, "contact_since": 2017}]',

      "$[*]"

      COLUMNS(

        rowid FOR ORDINALITY,

        email JSON PATH "$.email" DEFAULT '[]' ON EMPTY

       )

    ) AS contact;

+-------+---------------------------------------------------------+

| rowid | email                                                   |

+-------+---------------------------------------------------------+

|     1 | ["john.smith@example.com", "johnsmith@example.com"]     |

|     2 | ["jon.smith@example.com", "jonsmith@example.com"]       |

|     3 | ["johnny.smith@example.com", "johnnysmith@example.com"] |

+-------+---------------------------------------------------------+

+-------+-------+

| rowid  | email  |

+-------+-------+

|     1     | NULL   |

|     2     | NULL   |

|     3     | NULL   |

+-------+-------+