Interesting discussion today in sync wrt complex types

[Jacques Nadeau]

Hey All,

Thought it was interesting discussion today about unstructured type support in Substrait

I did a quick scan of unstructured types in different dbs and found the following (initial glance, not well researched)

Postgres => JSON unstructured type, limits to only json types

SQL Server => No unstructured data type

Oracle => "JSON" unstructured type, supports all SQL types, not just those in json

Snowflake => Variant unstructured type, supports all SQL types, not just those in json

Redshift => Super unstructured type, supports all SQL types, not just those in json

Multiple systems also support JSON functions against text strings.

[Ingo Müller]

Hey Jacques, hey all!

Jacques, thanks for relaying the discussion to the group! This is a topic that is dear to my heart. Maybe I can add some additional pointers.

One is to look at languages built for JSON other than SQL in order to understand what dealing with JSON inherently consists of. One study is that around SQL++ (https://arxiv.org/abs/1405.3631, in particular v4, which is more extensive than the latest), which tried to capture all possible features that a number of JSON-related languages express. My personal favorite is JSONiq (https://www.jsoniq.org/) but I am biased due to previous work on that language (https://arxiv.org/abs/1910.11582, https://arxiv.org/abs/2104.12615).

Another one is Apache Drill (https://drill.apache.org/docs/json-data-model), which I think is an interesting addition to Jacques' list.

Finally, one question: I have seen that there was "transcribe service" or similar attached to the community meeting. Are these transcriptions available somewhere? (I do know the agenda document, which already provides a good summary, but if we do have transcripts, I might take a look there, too.)

All the best,

Ingo

--
You received this message because you are subscribed to the Google Groups "substrait" group.
To unsubscribe from this group and stop receiving emails from it, send an email to substrait+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/substrait/CAJ9XdSqk4ELWY8510gp5Tg3%3DdfM_Wtxd6hBotBuHA_zJzoCj_Q%40mail.gmail.com.

[Ingo Müller]

Maybe I should also say that BigQuery has quite extensive JSON support (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions).

All the best,

Ingo