4 ms·

100%. The shame, to me, is that SQL is _unnecessarily_ clunky with producing query results with nested/heirarchical data. The relational model allows for any g

100%. The shame, to me, is that SQL is _unnecessarily_ clunky with producing query results with nested/heirarchical data. The relational model allows for any given value (field or cell value) to be itself a relation, but SQL doesn't make it easy to express such a query or return such a value from the database (as Jamie says - often the API server has to "perform the join again" to put it in nested form, due to this limitation).

bazoom42 1y ago
> The relational model allows for any given value (field or cell value) to be itself a relation First normal form explicitly forbids nested relations though. Relational algebra does not support nested relations for this reason. But perhaps nesting relations might make sense as the final step, just like sorting, which is not supported by the pure relational model either.
- marcosdumay 1y ago
  If your relational algebra comes with normative opinions about data normalization, there's something really strange with the entire way you think about mathematics. Usually, relational algebra doesn't have many restrictions about the type of the atomic values it deals with, what makes sequences and relations perfectly valid candidates. But yeah, there are many reasons for you to normalize your data at rest.
  - taffer 1y ago
    AFAIK relational algebra and relational calculus are based on FOL (first-order logic) which predicates atomic elements. For nested elements you would need HOL (higher-order logic) which is mathematically hard to optimize. As FOL is as powerful as HOL but easier to optimise, it has become the basis for relational databases. The trade-off is that you have to normalise your data so that you can efficiently query and apply constraints to it. But yes, just because your data at rest should be flat relations, that doesn't mean that the results of your queries need to be flat. I think querying a well-normalised database and returning nested JSON makes a lot of sense.
  - bazoom42 1y ago
    Sure, treating nested relations as “atoms” is supported naturally, but presumably you would want to query the content of these nested relations, which is where it gets complicated. The straighforward translation of a hierarchical database into relational form is to represent nested structures as nested relation. But if you cant query these nested relations the database is useless. So you either need to normalize to 1NF or extend the query language to query nested relations.
    - marcosdumay 1y ago
      You could reasonably want to evaluate data in those relations, to compose data to use at the top level. You could also reasonably run queries within the internal relations. You could probably be able to promote those relations up on the hierarchy by using them in joins. But I don't think it's reasonable to mix queries of several levels. (I can't even imagine how the syntax for that would look like.)
strbean 1y ago
SQL is unnecessarily clunky in just about every respect. It's mind boggling that the syntax of a language designed in the 1970s has so many ardent defenders.
- dotancohen 1y ago
  Probably because us old geezers are still using CLI interfaces on our servers and, often, on our desktops. We still use VIM. And we still listen to Led Zeppelin. Your new fad NoSQL may or may not be relevant tomorrow. SQL has inertia. I have no problem finding experienced SQL developers. Do there even exist experienced developers for technologies that have been invented yesterday? Will you be able to find somebody to maintain it tomorrow?
  - setr 1y ago
    One of the things I find most annoying about DBAs is they don’t understand just how utterly awful their tooling is. The error messages alone should be enough to throw a hissy fit, but as a group they’ve never dreamed of a life better than the misery of ERROR ON LINE 1: <entire query> The aggressively common pipe-delimited sproc output (the standard hack to get around commas being common in strings, instead of finding a legitimate data format) is another clear example of the brain damage the poor DBA incurs through constant investment in “modern” relational databases.
    - lucketone 1y ago
      I ran away from comfy DB-centered job, just because of the quality of the tooling. Technically it is good enough to perform the function, but at the same time it’s just soul-crushing how much quality of life is being wasted.
    - TristanBall 1y ago
      Larping as a dba now, and trust me many of us do know, and its a source of ongoing suffering, but we're often not developers and don't necessarily have the choice of tooling. Right now the DB I'm paid to babysit provides cli tools that flip between tabular and k/v list output without making that configurable, or a tabular form that doesn't include headers, or a 3rd party tool that will, but has the spectacularly annoying "error on line 1 : multi-line query" issue. Or things that are slow or only talk via generic protocols like odbc etc etc etc I think you're being a little unfair about the pipe delimited thing though, it's a least worst compromise based on who we're providing the data too - non technical business people, the vast majority of whom use excel or similar tools and couldn't even tell you what "data format" means, let alone configure their systems to parse something else. Personally I had a bit of an epiphany around the ascii delimiters (us/fs/rs/gs) which work extremely well when the data is ascii/utf-8, and make data interchange between shell cli tools very easy. But they've also invisible and little business software supports them in a friendly way. Telling someone in accounts or market to "use octal 034" helps no one. And I've resigned myself to using multiple tools, dev with tool 1 with decent error messages, tool 2 for production use because it can actually produce sane output formats. What I don't have a choice on is which db we use, and it's not modern or cool and honest most things don't even have drivers for it
zozbot234 1y ago
> SQL is _unnecessarily_ clunky with producing query results with nested/heirarchical data. Property Graph Query (PGQ) is now a part of SQL and is expected to help with expressing these complex queries.
- twoodfin 1y ago
  Has anyone implemented it yet?
mycall 1y ago
There are lots of ways in SQL to provide nested/heirarchical data, each with their own strengths and weaknesses. * Adjacency List Model * Path Enumeration Model, also known as the Materialized Path * Closure Table Model (or Bridge Table) * Nested Set Model * Recursive CTEs

bazoom42 1y ago

> The relational model allows for any given value (field or cell value) to be itself a relation First normal form explicitly forbids nested relations though. Relational algebra does not support nested relations for this reason. But perhaps nesting relations might make sense as the final step, just like sorting, which is not supported by the pure relational model either.

marcosdumay 1y ago
If your relational algebra comes with normative opinions about data normalization, there's something really strange with the entire way you think about mathematics. Usually, relational algebra doesn't have many restrictions about the type of the atomic values it deals with, what makes sequences and relations perfectly valid candidates. But yeah, there are many reasons for you to normalize your data at rest.
- taffer 1y ago
  AFAIK relational algebra and relational calculus are based on FOL (first-order logic) which predicates atomic elements. For nested elements you would need HOL (higher-order logic) which is mathematically hard to optimize. As FOL is as powerful as HOL but easier to optimise, it has become the basis for relational databases. The trade-off is that you have to normalise your data so that you can efficiently query and apply constraints to it. But yes, just because your data at rest should be flat relations, that doesn't mean that the results of your queries need to be flat. I think querying a well-normalised database and returning nested JSON makes a lot of sense.
- bazoom42 1y ago
  Sure, treating nested relations as “atoms” is supported naturally, but presumably you would want to query the content of these nested relations, which is where it gets complicated. The straighforward translation of a hierarchical database into relational form is to represent nested structures as nested relation. But if you cant query these nested relations the database is useless. So you either need to normalize to 1NF or extend the query language to query nested relations.
  - marcosdumay 1y ago
    You could reasonably want to evaluate data in those relations, to compose data to use at the top level. You could also reasonably run queries within the internal relations. You could probably be able to promote those relations up on the hierarchy by using them in joins. But I don't think it's reasonable to mix queries of several levels. (I can't even imagine how the syntax for that would look like.)

strbean 1y ago

SQL is unnecessarily clunky in just about every respect. It's mind boggling that the syntax of a language designed in the 1970s has so many ardent defenders.

dotancohen 1y ago
Probably because us old geezers are still using CLI interfaces on our servers and, often, on our desktops. We still use VIM. And we still listen to Led Zeppelin. Your new fad NoSQL may or may not be relevant tomorrow. SQL has inertia. I have no problem finding experienced SQL developers. Do there even exist experienced developers for technologies that have been invented yesterday? Will you be able to find somebody to maintain it tomorrow?
- setr 1y ago
  One of the things I find most annoying about DBAs is they don’t understand just how utterly awful their tooling is. The error messages alone should be enough to throw a hissy fit, but as a group they’ve never dreamed of a life better than the misery of ERROR ON LINE 1: <entire query> The aggressively common pipe-delimited sproc output (the standard hack to get around commas being common in strings, instead of finding a legitimate data format) is another clear example of the brain damage the poor DBA incurs through constant investment in “modern” relational databases.
  - lucketone 1y ago
    I ran away from comfy DB-centered job, just because of the quality of the tooling. Technically it is good enough to perform the function, but at the same time it’s just soul-crushing how much quality of life is being wasted.
  - TristanBall 1y ago
    Larping as a dba now, and trust me many of us do know, and its a source of ongoing suffering, but we're often not developers and don't necessarily have the choice of tooling. Right now the DB I'm paid to babysit provides cli tools that flip between tabular and k/v list output without making that configurable, or a tabular form that doesn't include headers, or a 3rd party tool that will, but has the spectacularly annoying "error on line 1 : multi-line query" issue. Or things that are slow or only talk via generic protocols like odbc etc etc etc I think you're being a little unfair about the pipe delimited thing though, it's a least worst compromise based on who we're providing the data too - non technical business people, the vast majority of whom use excel or similar tools and couldn't even tell you what "data format" means, let alone configure their systems to parse something else. Personally I had a bit of an epiphany around the ascii delimiters (us/fs/rs/gs) which work extremely well when the data is ascii/utf-8, and make data interchange between shell cli tools very easy. But they've also invisible and little business software supports them in a friendly way. Telling someone in accounts or market to "use octal 034" helps no one. And I've resigned myself to using multiple tools, dev with tool 1 with decent error messages, tool 2 for production use because it can actually produce sane output formats. What I don't have a choice on is which db we use, and it's not modern or cool and honest most things don't even have drivers for it

zozbot234 1y ago

> SQL is _unnecessarily_ clunky with producing query results with nested/heirarchical data. Property Graph Query (PGQ) is now a part of SQL and is expected to help with expressing these complex queries.

twoodfin 1y ago
Has anyone implemented it yet?

mycall 1y ago

There are lots of ways in SQL to provide nested/heirarchical data, each with their own strengths and weaknesses. * Adjacency List Model * Path Enumeration Model, also known as the Materialized Path * Closure Table Model (or Bridge Table) * Nested Set Model * Recursive CTEs