Fork me on GitHub

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


{:select [ [ :x_id] :x.value ]
 :cross-join [[[:jsonb_to_recordset :foo.json_value]
                 [[:raw "x(id text, value jsonb)"]]]]
   :from [:foo]}
{:select [ [ :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, AS x_id, x.value
FROM foo
CROSS JOIN lateral, (JSONB_TO_RECORDSET(foo.json_value), (x(id text, value jsonb)))
instead of
SELECT, AS x_id, x.value
FROM foo
CROSS JOIN LATERAL JSONB_TO_RECORDSET(foo.json_value) x(id text, value jsonb)


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


@orestis Is this what you’re after?

dev=> (sql/format {:select [ [ :x_id] :x.value ]
 #_=>    :cross-join [[[:lateral
 #_=>                 [[:jsonb_to_recordset :foo.json_value]
 #_=>                   [:raw "x(id text, value jsonb)"]]]]]
 #_=>    :from [:foo]})
["SELECT, 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.


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

dev=> (sql/format {:select [ [ :x_id] :x.value ]
 #_=>    :cross-join [[[:lateral
 #_=>                   [:jsonb_to_recordset :foo.json_value]]]
 #_=>                 [[:raw "x(id text, value jsonb)"]]]
 #_=>    :from [:foo]})
["SELECT, 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.


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 ..] ]


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…


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 🙂


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.


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).


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


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


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.


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


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


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.


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


OK, here it is:

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


: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 ..]].


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


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 🙂


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.


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...


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 🙂


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


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


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.


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 🙂


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 🙂


Rich Comment Form.


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 🙂


Ah yes. Same here 🙂


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).


I knew the term but not the acronym 🙂