<story><title>I don&apos;t need your query language</title><url>https://antonz.org/fancy-ql/</url></story><parent_chain><item><author>nicoburns</author><text>This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)</text></item><item><author>DaiPlusPlus</author><text>SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows.&lt;p&gt;And there are often problems when using multiple SELECT queries in a batched statement: you can&amp;#x27;t re-use existing CTE queries. Not all client libraries support multiple result-sets. It&amp;#x27;s essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn&amp;#x27;t even support named result sets...), which means you can&amp;#x27;t opportunistically skip or omit a SELECT query in a batch because your client reader won&amp;#x27;t know how to parse&amp;#x2F;interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you&amp;#x27;ll run into concurrency issues if data changes between SELECT queries in the same batch ()&lt;p&gt;[1] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;performance&amp;#x2F;efficient-querying#avoid-cartesian-explosion-when-loading-related-entities&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;performance&amp;#x2F;effici...&lt;/a&gt; and &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;querying&amp;#x2F;single-split-queries&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;querying&amp;#x2F;single-sp...&lt;/a&gt;</text></item></parent_chain><comment><author>erikpukinskis</author><text>&amp;gt; &lt;i&gt;You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)&lt;/i&gt;&lt;p&gt;For anyone (like me) who is not quite able to visualize this, here is an example:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    SELECT json_agg(trips)
    FROM (
        SELECT 
            json_agg(
                json_build_object(
                    &amp;#x27;recorded_at&amp;#x27;, created_at, 
                    &amp;#x27;latitude&amp;#x27;, latitude, 
                    &amp;#x27;longitude&amp;#x27;, longitude
                )
            ) as trips
        FROM data_tracks
        GROUP by trip_log_id
    )s
&lt;/code&gt;&lt;/pre&gt;
From StackOverflow user S-man &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;stackoverflow.com&amp;#x2F;a&amp;#x2F;53087015&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;stackoverflow.com&amp;#x2F;a&amp;#x2F;53087015&lt;/a&gt;</text></comment>
<story><title>I don&apos;t need your query language</title><url>https://antonz.org/fancy-ql/</url></story><parent_chain><item><author>nicoburns</author><text>This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)</text></item><item><author>DaiPlusPlus</author><text>SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows.&lt;p&gt;And there are often problems when using multiple SELECT queries in a batched statement: you can&amp;#x27;t re-use existing CTE queries. Not all client libraries support multiple result-sets. It&amp;#x27;s essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn&amp;#x27;t even support named result sets...), which means you can&amp;#x27;t opportunistically skip or omit a SELECT query in a batch because your client reader won&amp;#x27;t know how to parse&amp;#x2F;interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you&amp;#x27;ll run into concurrency issues if data changes between SELECT queries in the same batch ()&lt;p&gt;[1] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;performance&amp;#x2F;efficient-querying#avoid-cartesian-explosion-when-loading-related-entities&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;performance&amp;#x2F;effici...&lt;/a&gt; and &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;querying&amp;#x2F;single-split-queries&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;learn.microsoft.com&amp;#x2F;en-us&amp;#x2F;ef&amp;#x2F;core&amp;#x2F;querying&amp;#x2F;single-sp...&lt;/a&gt;</text></item></parent_chain><comment><author>tomnipotent</author><text>Absolutely. JSON serialization is so well optimized in most RDBMS vendors now it puts very little additional CPU work on the database, and duplicating data in the output for large graph-based results is a small price to pay to breaking out of set-based constraints.</text></comment>