Clojurians Log v2

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

{: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]}

{: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)))

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)

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 [: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))"]

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)"]

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 [: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 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 🙂

RCF?

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

https://github.com/seancorfield/honeysql/issues/332

I knew the term but not the acronym 🙂