3 ms·

> Unlike SQL, which can only manipulate normalized data, JSONiq natively works on the entire normalization spectrum: textual, heterogeneous, deeply nested Mayb

> Unlike SQL, which can only manipulate normalized data, JSONiq natively works on the entire normalization spectrum: textual, heterogeneous, deeply nested Maybe I'm misunderstanding something, but isn't SQL perfectly capable of acting on denormalized data? We often talk about the degree of normalization that a RDMS has. Perhaps this is in reference to nested data structures (which would normally be represented in RDMS via FK relations), but even there it's implementation dependent.

chippiewill 5y ago
> Perhaps this is in reference to nested data structures If you have nested data structures then it violates 1NF, i.e. the data isn't normalised. Any "pure" RDBMS won't even be able to represent that denormalised data in a table, let alone query it with SQL. Normally this is worked around by straight up serialising it. Obviously some implementations have extensions, like Postgres has JSON columns.
- e-master 5y ago
  To be honest, I’m unsure how many ‘pure’ RDBMS are out there, and doesn’t seem like the parent comment had them in mind. I’d rather suggest that mainstream RDBMS in general don’t have trouble working with data formats such as json or xml. SQL Server for one has no trouble querying into deeply nested XML/JSON columns and it does it surprisingly quickly based on my experience.
- maweki 5y ago
  As there are various degrees of "not normalized", this was a very valid question (and answer). SQL databases do indeed work with data not in Boyce-Codd-Normal-Form. :D
- user3939382 5y ago
  Yes, MySQL also has functions dedicated to traversing and selecting JSON
ghislainfourny 5y ago
Thank you for your comment. There exist indeed recent extensions of SQL that add support for denormalized data (arrays, objects), for example in Spark SQL and PostgreSQL, however SQL was originally designed for tables and it remains cumbersome to write complex queries on denormalized data (lateral views, etc). A deeper and more detailed analysis of query languages for nested data can be found in our recent paper with a concrete use case in high energy physics, to be presented at VLDB 2022: https://arxiv.org/abs/2104.12615 https://arxiv.org/abs/2104.12615

chippiewill 5y ago

> Perhaps this is in reference to nested data structures If you have nested data structures then it violates 1NF, i.e. the data isn't normalised. Any "pure" RDBMS won't even be able to represent that denormalised data in a table, let alone query it with SQL. Normally this is worked around by straight up serialising it. Obviously some implementations have extensions, like Postgres has JSON columns.

e-master 5y ago
To be honest, I’m unsure how many ‘pure’ RDBMS are out there, and doesn’t seem like the parent comment had them in mind. I’d rather suggest that mainstream RDBMS in general don’t have trouble working with data formats such as json or xml. SQL Server for one has no trouble querying into deeply nested XML/JSON columns and it does it surprisingly quickly based on my experience.
maweki 5y ago
As there are various degrees of "not normalized", this was a very valid question (and answer). SQL databases do indeed work with data not in Boyce-Codd-Normal-Form. :D
user3939382 5y ago
Yes, MySQL also has functions dedicated to traversing and selecting JSON

ghislainfourny 5y ago

Thank you for your comment. There exist indeed recent extensions of SQL that add support for denormalized data (arrays, objects), for example in Spark SQL and PostgreSQL, however SQL was originally designed for tables and it remains cumbersome to write complex queries on denormalized data (lateral views, etc). A deeper and more detailed analysis of query languages for nested data can be found in our recent paper with a concrete use case in high energy physics, to be presented at VLDB 2022: https://arxiv.org/abs/2104.12615 https://arxiv.org/abs/2104.12615