Database |> Language |>
Pipe Operator
Contents:
Overview
If you’ve dealt with convoluted CTEs or tried to nest multiple EXISTS clauses, then you’ll know how complex SQL can sometimes look similar to a deeply stacked LISP program. As a means of remedying this, we took inspiration from a paper recently published by Google where they go over adding a pipe operator syntax to their database language.
The pipe operator syntax enables developers to write functional SQL to transform web data in a way that feels less like hand-holding an automated browser and more like remixing data to reach your desired structure.
Translating from standard SQL
To translate from standard SQL, break up the clauses into expressions and separate them with a pipe operator while starting with a FROM. For example, let’s take the following query in standard syntax:
SELECT
a AS post
, a@href AS link
FROM
https://news.ycombinator.com
GROUP BY
span.titleline;
These are the clauses that make up the statement:
[SELECT
a AS post
, a@href AS link]
[FROM
https://news.ycombinator.com]
[GROUP BY
span.titleline;]
So in pipe operator syntax that could look like
FROM https://news.ycombinator.com
|> SELECT a AS post, a@href AS link
|> GROUP BY span.titleline;
The order of expressions can be interchangeable so long as they don’t alter the page you’re interested in meaning the below statement returns the same data as the one above.
FROM https://news.ycombinator.com
|> GROUP BY span.titleline
|> SELECT a AS post, a@href AS link;
However the same is not true for the order of expressions in the following two queries.
FROM https://news.ycombinator.com
|> CLICK ON a[href="front"]
|> SELECT a AS post, a@href AS link
|> GROUP BY span.titleline;
Note in this one we obtain data before clicking on a link.
FROM https://news.ycombinator.com
|> GROUP BY span.titleline
|> SELECT a AS post, a@href AS link
|> CLICK ON a[href="front"];