Hi there,
We have an S3 partitioned source containing JSON files.
The files each contain a single JSON object.
Each object contains a “metadata” property, which is an arbitrary JSON payload in and of itself. Sometimes, keys have scalar values; sometimes, they have list values.
When registering the format as JSON on a folder that contains these mixed-value-types and doing a simple select * on the table, we receive an error:
Unable to coerce from the file’s data type “list” to the column’s data type “varchar” in table “Dev S3.x.y.z”, column “metadata.one” and file “/bucket/key.json”
Is there anyway I can workaround this without pre-processing the data in S3? Any way to cajole whatever is parsing the JSON to return the raw json string that I can then convert from?
To be clear, some objects in S3 have:
{ "metadata" : { "one" : "foo" } }
And others have:
{ "metadata" : { "one" : ["foo", "bar"] } }
Thanks