5 ms·

What I really want from SQL is the ability to do a single select with one or more parts of each returned record being an array (defined by a one-to-many join) a

What I really want from SQL is the ability to do a single select with one or more parts of each returned record being an array (defined by a one-to-many join) and others simple scalars. It looks like Postgres can do it [1] with its JSON capabilities, and to be honest its being a little while since I've dug into it, so things might have changed (I'm actually not even sure I'm phrasing the question correctly), but the old way of doing a single select and then looping over it in whatever code is calling the SQL and doing N more selects, one for each row to get the sub-array is horrible. [1] http://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/ http://bender.io/2013/09/22/returning-hierarchical-data-in-a...

hamilyon2 11y ago
What you need is good old array_agg(expression) with group by. Json capabilities may be needed for more complex hierarchical structures.
quizotic 11y ago
In the old days, SQL operated on rectangular tables and produced rectangular tables ... which doesn't work so well for data that's better modelled as a tree or graph. But there now lots of ways to produced multi-valued output and to operate on graph-structured data. In addition to PG's JSON, you might checkout recursive unions - which supports a subset of graph navigation. But while SQL may have grown just a tad more expressive, I'm not sure that performance is up to snuff. And even if it's now possible to express some things in SQL, it doesn't mean it's easy or readable.
vkjv 11y ago
It works pretty well and fairly quickly. We use the array_agg and other json features in a Postgres to Elasticsearch ETL. Postgres is able to spit out the exact format that we need, so the application code isn't really doing much more than coordinating the reads and writes.
garyclarke27 11y ago
Recursive CTE can be very fast in Postgres. I can create a closure table of 2 Million rows for indirect and direct relations i.e. All paths from 15000 source direct relations in about 12 seconds on my MBPro 1Tb ssd 16 Ram. The makes it practical to just recreate all paths as a materialzed view refresh instead of worring about complicated incremental graph logic such as moving branches. With such a closure view - any graph traversal query is lightning fast - no need for neo4j !
- ajw0100 11y ago
  Can you post the code for this somewhere? Thanks!
masklinn 11y ago
> It looks like Postgres can do it [1] with its JSON capabilities Postgres could do it long before its JSON capabilities, array_agg was added in Postgres 8.4 (2009) and hstore in 8.3 (2008)
- colanderman 11y ago
  Also, ARRAY(), since 7.4 in 2003.
colanderman 11y ago
You mean like SELECT key, value1, ARRAY(SELECT value2 FROM bar WHERE bar.key = foo.key) AS value2_array FROM foo; ? (In Postgres; no "JSON capabilities" needed.)

hamilyon2 11y ago

What you need is good old array_agg(expression) with group by. Json capabilities may be needed for more complex hierarchical structures.

quizotic 11y ago

In the old days, SQL operated on rectangular tables and produced rectangular tables ... which doesn't work so well for data that's better modelled as a tree or graph. But there now lots of ways to produced multi-valued output and to operate on graph-structured data. In addition to PG's JSON, you might checkout recursive unions - which supports a subset of graph navigation. But while SQL may have grown just a tad more expressive, I'm not sure that performance is up to snuff. And even if it's now possible to express some things in SQL, it doesn't mean it's easy or readable.

vkjv 11y ago

It works pretty well and fairly quickly. We use the array_agg and other json features in a Postgres to Elasticsearch ETL. Postgres is able to spit out the exact format that we need, so the application code isn't really doing much more than coordinating the reads and writes.

garyclarke27 11y ago

Recursive CTE can be very fast in Postgres. I can create a closure table of 2 Million rows for indirect and direct relations i.e. All paths from 15000 source direct relations in about 12 seconds on my MBPro 1Tb ssd 16 Ram. The makes it practical to just recreate all paths as a materialzed view refresh instead of worring about complicated incremental graph logic such as moving branches. With such a closure view - any graph traversal query is lightning fast - no need for neo4j !

ajw0100 11y ago
Can you post the code for this somewhere? Thanks!

masklinn 11y ago

> It looks like Postgres can do it [1] with its JSON capabilities Postgres could do it long before its JSON capabilities, array_agg was added in Postgres 8.4 (2009) and hstore in 8.3 (2008)

colanderman 11y ago
Also, ARRAY(), since 7.4 in 2003.

colanderman 11y ago

You mean like SELECT key, value1, ARRAY(SELECT value2 FROM bar WHERE bar.key = foo.key) AS value2_array FROM foo; ? (In Postgres; no "JSON capabilities" needed.)