select unnest(col) from t

[Julian Backes]

Hi all,

in Postgres, I can write a query like

select unnest(col) from t;

(col is an array column) The result is all selected arrays as rows. Is this possible using jooq? I found some DSL.unnest functions but they don't seem to fit. Is there a different way to achieve the same result? Or am I doing something wrong? :-)

Regards

Julian

[Lukas Eder]

So far, we don't explicitly support this syntax for PostgreSQL. While we could support it, I'd strongly re-consider using it, because the syntax is a "lie". It is short for:

SELECT unnest FROM t CROSS JOIN LATERAL unnest(string_array);

Except that the SQL standard LATERAL JOIN wasn't introduced until PostgreSQL 9.3:

http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html#QUERIES-LATERAL

I strongly suggest using LATERAL instead of the PostgreSQL-specific unnesting in the SELECT clause. It makes no sense for the SELECT clause to produce additional cartesian product factors, especially in larger queries.

jOOQ supports lateral join, although I just noticed that the DSL.unnest() implementation is a bit hairy. Will have to review this - I guess for now, you'll have to resort to using plain SQL:

http://www.jooq.org/doc/latest/manual/sql-building/plain-sql

The easiest solution would be:

DSL.using(configuration)

   .select(DSL.field("unnest({0})", String.class, t.col)) // Assuming String here

   .from(t)

   .fetch();

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Max Kremer]

Hi,

  I'd like to revive this thread if I may.

I think it would be really handy to have DSL.unnest work as described by Julian even tho it is syntactic sugar for later join.

Here's why. Lets say you're building the queries dynamically in jooq by mapping a set of fields in your domain model to jooq Field<>.

When a field is of type array and you  know you're going to unnest it universally then why make me muck about with my join and from clauses?

For example

class Foo {

..

   Field<?> convertToJooqField() {

}

ArrayList<Foo> foos;

I want to be able to build my select list by mapping over foos and 

d.select(foos.map( f - f->convertToJooqField))

if the convertToJooqField has some logic that wraps a tags field for example with an UNNEST the above would brake.

[Lukas Eder]

Hi Max,

Thanks for your message. Indeed, it would be handy - I don't deny that at all. The problem with jOOQ's current API is that we cannot implement this without breaking the API. Or, we could use a dummy name instead of unnest - so we don't have to touch the existing API. The existing API reads:

Table<?> unnest(Field<?>)

Notice 1) it returns a Table, 2) We don't have any <T> type information.

Note also that you can always work around this limitation easily by writing:

public static Field<T> myUnnest(Field<? extends T[]> array) {

    return DSL.field("unnest({0})", (Class<T>) array.getType().getComponentType(), array);

}

In any case, I'm curious about your "universal" unnesting use-case. This is probably only a good idea if you have at most one array column, because unnest creates a cartesian product between the row and the array(s). Would you mind explaining your use-case a bit more? Perhaps we can find an entirely different solution to this...

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Max Kremer]

Yes I hear you, probably not worth it then.

 You are right we are only using unset on a single column. The use case is the common "tags" field where you have an array of tags on a record.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.