We have tried multiple ways to find a solution around list of jsons in a column. But unformtunately none seems to be working.
Dremio is reading this column as a varchar/string.
Attaching the example data:
“[{“providingSource”:{“id”:“1234”,“url”:“NULL”,“name”:“sysgen”,“displayName”:“Sysgen”,“naicsVersion”:“2007”},“isUpdated”:1,“value”:{“email”:“leif.jackson@ucalgary.ca”,“isDerived”:1,“emailStatus”:1,“overridenStatus”:0,“lastUpdatedDate”:“2020-05-28T08:13:25.000Z”,“emailPattern”:“F.L”,“catchAll”:1}},{“modifiedTime”:“2020-01-28T02:54:56.498Z”,“providingSource”:{“id”:“1111”,“url”:“NULL”,“name”:“sysgen”,“displayName”:“Sysgen”,“naicsVersion”:“2007”},“isUpdated”:1,“value”:{“email”:“jackson@ucalgary.ca”,“isDerived”:1,“emailStatus”:1,“overridenStatus”:2,“lastUpdatedDate”:“2017-11-19T19:25:17.000Z”,“emailPattern”:“L”,“siResponseCode”:“210and211”,“catchAll”:1}},{“providingSource”:{“id”:“2222”,“url”:“NULL”,“name”:“sysgen”,“displayName”:“Sysgen”,“naicsVersion”:“2007”},“isUpdated”:1,“value”:{“email”:“ljackson@ucalgary.ca”,“isDerived”:1,“emailStatus”:4,“overridenStatus”:2,“emailPattern”:“F1L”,“catchAll”:0}}]”
Please let me know how can I unest or expload the table so that I can have access to “email”,“isDerived”,“emailStatus” at each email level.
You can find few other details in the below image.
