I would like to unnest JSON with similar semantics to the Presto UNNEST function. So for example, if I had a JSON blob like {foo: "bar"} then I would like to generate table with all the keys in one column and all the values in the other.
Anything that allows dynamic listing of JSON keys would probably be fine. Looking at the documentation, this doesn’t seem possible.
We are thinking about this feature area and will take your request into consideration. Thanks for the feedback.
Eric Hanson, Principal Product Manager, MemSQL
OK, thanks.
Just further confirming it is not possible to call out to a script, e.g. Python, to handle this. From the docs, it looks like this is only possible with pipelines but want to make sure.
That’s right, you can’t call out to a script from SQL. You can only invoke scripts from pipelines.
Hi There, its November 2020(almost 2 years from original question) and the function is still missing. MySQL has for example JSON_KEYS to return the keys. DB obviously know to get keys, as it allows column::json_key query. I’m getting thousands json from some service and it looks like varying schema, or 2-3 schemas with partially populates keys/values. It would be great having this internal function available, so that I can extract all keys seen in particular json object or nested object out. Is there something planned? OR workaround available?
I need to analyse some 85.000+ records to see what key we observe. Any suggestion?
SingleStore 7.6 supports this now as listed under their release notes and doc JSON_KEYS · SingleStore Documentation