Format "json", object with varying value types

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 “” 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"] } }


For posterity, this does not appear to be possible at the moment: see here.