Fork me on GitHub
#honeysql
<
2021-06-18
>
orestis13:06:12

Should there be support for CROSS JOIN LATERAL? I tried the [:lateral ...] wrapper but didn't get what I expected...

orestis13:06:38

{:select [:foo.id [ :x.id :x_id] :x.value ]
 :cross-join [[[:jsonb_to_recordset :foo.json_value]
                 [[:raw "x(id text, value jsonb)"]]]]
   :from [:foo]}
vs
{:select [:foo.id [ :x.id :x_id] :x.value ]
   :cross-join [:lateral
                [[[:jsonb_to_recordset :foo.json_value]
                  [[:raw "x(id text, value jsonb)"]]]]]
   :from [:foo]}
The latter returns:
SELECT foo.id, x.id AS x_id, x.value
FROM foo
CROSS JOIN lateral, (JSONB_TO_RECORDSET(foo.json_value), (x(id text, value jsonb)))
instead of
SELECT foo.id, x.id AS x_id, x.value
FROM foo
CROSS JOIN LATERAL JSONB_TO_RECORDSET(foo.json_value) x(id text, value jsonb)

orestis13:06:48

Perhaps there should be a :cross-join-lateral keyword? Can I add this on my own?

seancorfield17:06:14

@orestis Is this what you’re after?

dev=> (sql/format {:select [:foo.id [ :x.id :x_id] :x.value ]
 #_=>    :cross-join [[[:lateral
 #_=>                 [[:jsonb_to_recordset :foo.json_value]
 #_=>                   [:raw "x(id text, value jsonb)"]]]]]
 #_=>    :from [:foo]})
["SELECT foo.id, x.id AS x_id, x.value FROM foo CROSS JOIN LATERAL (JSONB_TO_RECORDSET(foo.json_value), x(id text, value jsonb))"]
:cross-join takes a sequence of SQL entities; each can be a simple name or a pair of expression/alias — like :select — so when you want a “function call” there, you need :cross-join [ [ [:fn-call :arg1 :arg2] ] ] just like in a SELECT.

seancorfield17:06:12

Looking at your desired result more carefully, I think you want this:

dev=> (sql/format {:select [:foo.id [ :x.id :x_id] :x.value ]
 #_=>    :cross-join [[[:lateral
 #_=>                   [:jsonb_to_recordset :foo.json_value]]]
 #_=>                 [[:raw "x(id text, value jsonb)"]]]
 #_=>    :from [:foo]})
["SELECT foo.id, x.id AS x_id, x.value FROM foo CROSS JOIN LATERAL JSONB_TO_RECORDSET(foo.json_value), x(id text, value jsonb)"]
Again, the :raw part needs to be treated as a function call in the sequence of CROSS JOIN expressions.

seancorfield17:06:09

So we have [:lateral [:jsonb..]] as one function call, wrapped as an alias (with the actual alias omitted): [ [:lateral [:jsonb..]] ] and then the same for [ [:raw ..] ]

seancorfield17:06:51

I notice you don’t have a , in your desired expression: CROSS JOIN LATERAL JSONB_TO_RECORDSET(foo.json_value) x(id text, value jsonb) — is that intentional? I’m not quite sure what to make of that syntax…

orestis17:06:04

Thanks for answering @seancorfield - first of all, putting the lateral as in your example works. I was putting it surrounding the entire expression but I was confused 🙂

orestis17:06:41

Regarding the raw placement: my example is actually what I'm after. In this case, the x(id text, value jsonb) is a cast expression. jsonb_to_recordset takes a postgres jsonb array and creates a "set" (or table, or relation) out of it. But since SQL is statically typed you need to provide the columns and their types as a cast expression.

orestis17:06:18

I discovered that technically for postgres the LATERAL is not needed in a table-returning-function (it can refer to other tables already in the FROM clause).

seancorfield17:06:39

So you’re aiming for JSONB_TO_RECORDSET(foo.json_value) AS x(id text, value jsonb) essentially?

seancorfield17:06:04

(i.e., x is the name you are trying to give to the record set?)

orestis17:06:18

If you're interested, given a column containing this JSONB data (using EDN for type laziness): [{:id "foo" :value "bar"}, [{:id "qoo" :value "cux"}]it would return 2 rows with the columns id, value.

orestis17:06:33

Yes, x is the name of the recordset that you can use in the SELECT clause.

orestis17:06:12

AS is optional as in many other places. SQL is soooo weird 🙂

orestis17:06:58

So, no worries from my end: honeysql provides what I want, and turns out I didn't need it anyway 🙂 Thanks for taking the time.

orestis17:06:36

Between honeysql queries-as-data and PostgreSQL's power I can create pivot tables quite easily. Dynamically generate column definitions etc 🙂

seancorfield17:06:53

OK, here it is:

dev=> (sql/format {:select [:foo.id [ :x.id :x_id] :x.value ]
 #_=>    :cross-join [[[:lateral
 #_=>                   [:jsonb_to_recordset :foo.json_value]]
 #_=>                   [[:raw "x(id text, value jsonb)"]]]]
 #_=>    :from [:foo]})
["SELECT foo.id, x.id AS x_id, x.value FROM foo CROSS JOIN LATERAL JSONB_TO_RECORDSET(foo.json_value) x(id text, value jsonb)"]

seancorfield17:06:40

:cross-join now has a single expression: an alias pair. The first part of that alias pair is [:lateral ..] and the second part is the nested “function call” of [[:raw ..]].

seancorfield17:06:24

:cross-join [ [expr [AS] alias] ] where [AS] is just intended to show how the aliased expression gets created.

orestis17:06:02

Gotcha, I ended up with the same result. I was looking for "lateral" in the docs and found a couple of hits but I I was tired 🙂

orestis17:06:34

BTW, honeysql is CLJC, right? Would be a nice project to put together a webpage that takes a honeysql query on the left and gives you SQL on the right.

orestis17:06:14

I think malli has something similar with also some persistence that makes it for a nice playground and easier for creating issues and so on...

seancorfield17:06:26

The “problem” is that pretty much any combination of expressions with [..`]` is “valid” as far as the DSL is concerned and most of them produce valid SQL with various meanings, but not all of the possible SQL outputs are going to be valid 🙂

orestis17:06:46

(hoping someone likes the idea and takes it up, I don't have time myself 😄)

seancorfield17:06:06

I don’t do cljs/frontend at all so it won’t be me either!

orestis17:06:46

I think that's a valid tradeoff. SQL is infinitely complicated and getting a DSL able to express all of it correctly while still being data will be impossible.

orestis17:06:42

You do end up using the REPL quite a lot during query development, and for the more complex stuff you do have to run the query against a live DB anyway -- but that's what we do in Clojure anyway 🙂

seancorfield17:06:55

Yeah, I tend to build my more complex queries up in RCFs with dummy data values, to ensure I’m getting what I want. But I also use MySQL so life is a lot simpler 🙂

seancorfield17:06:12

Rich Comment Form.

orestis17:06:36

Hehe most of our queries are pretty simple too. But we're migrating a MongoDB database with an actual valid use case of using schema-less in some areas. And these areas we have to dig into Postgres JSON support which is great but also weird 🙂

orestis17:06:45

Ah yes. Same here 🙂

seancorfield17:06:06

A name coined by Stu Halloway for (comment ..) forms used while developing/exploring problem solutions — because Rich Hickey does it that way (you can see some such forms at the bottom of some namespaces in Clojure).

orestis17:06:30

I knew the term but not the acronym 🙂