Postgres - json_to_recordset and aliasing rows

[Emrul Islam]

Hi,

I need to construct this type of query:

SELECT
  t.id, array_agg("data"."_rowId"), t.name
FROM "data"
    CROSS JOIN
    json_to_recordset("data"."habitas") as t("id" text, "name" text)
GROUP BY t.id, t.name

All fine except for the 'json_to_recordset("data"."habitas") as t("id" text, "name" text)' bit which I can't work out how to achieve in jOOQ.

Are there any examples on how to call a Postgres function that returns a recordset or rowtyped value?

[Emrul Islam]

I think I'm on the right track with this (Kotlin):

fun crossJoinSpec(): TableLike<Record> {
    val _sb = StringBuilder("{json_to_recordset}({0}) as {1}(")
    columns.forEachIndexed { i, selectField ->
        if ( i != 0 ) _sb.append(", ")
        _sb.append(selectField)
        _sb.append(" ")
        _sb.append(selectField.dataType.castTypeName)
    }
    _sb.append(")")
    return DSL.table(_sb.toString(), sourceField, columns.first())
}

[Lukas Eder]

Hello,

Yes, your solution looks reasonable. jOOQ supports the derived column list syntax "as t(id, name)", but not with data types associated with the columns. That's an interesting PostgreSQL specific extension. Perhaps we should support that too. I've registered a feature request for this:

https://github.com/jOOQ/jOOQ/issues/5926

What's the reason why you explicitly name the "text" data types?

If you didn't need that, you could also write:

table("json_to_recordset({0})", DATA.HABITAS).as("t", "id", "name")

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.
For more options, visit https://groups.google.com/d/optout.

[Emrul Islam]

Hi Lukas

Thanks for creating a Github issue for this.  The reason I have to list the types is given in the Postgres documentation for the jsonb_to_recordset function: "As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause." (see https://www.postgresql.org/docs/current/static/functions-json.html).

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

[Lukas Eder]

Oh, I see. Yes, that's a good reason - I overlooked that. So, definitely worth support in jOOQ - will have to think about the syntax though. I've seen other cases where a column name / data type pair can be specified in similar ways, e.g. when writing CREATE TABLE .. AS SELECT in some databases.

Will think about it.

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

[Emrul Islam]

Cool, thanks!

One more question - just to validate that I'm doing things in the best way.  When I reference these columns, e.g. in my GroupBy clause, I am doing so as follows:

Where 'columns' is an ArrayList containing a list of Fields ("id", "name" inside the type and sourceField is the name of my type ('t' in the case of the example query above).  In order to properly qualify these names I'm doing this:

.groupBy(*columns.map { DSL.field(DSL.name(sourceField.name,it.name))}.toTypedArray())

Is that right?

[Lukas Eder]

That looks about right. Too bad you cannot use jOOQ's internals. There's Tools.fieldsByName(String, String[]):

    static final Field<?>[] fieldsByName(String tableName, String[] fieldNames) {

        if (fieldNames == null)

            return null;

        Field<?>[] result = new Field[fieldNames.length];

        if (tableName == null)

            for (int i = 0; i < fieldNames.length; i++)

                result[i] = DSL.field(name(fieldNames[i]));

        else

            for (int i = 0; i < fieldNames.length; i++)

                result[i] = DSL.field(name(tableName, fieldNames[i]));

        return result;

    }

I have to say, the Groovy version looks a bit more concise...

Cheers,

Lukas

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

[Emrul Islam]

Ah ha, good to see that's how jOOQ does it internally.

PS: code is Kotlin - gave up on Groovy years ago, easy to write code that performs poorly and too much syntactic sugar resulted in team members getting sugar rushes :)

[Lukas Eder]

"sugar rushes" ... I have to remember that :)

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