Json aggregation in postgres with JOOQ

[aleksey...@gmail.com]

Good day! 

I didn't find the way to aggregate json with jooq. 

my schema looks like:

CREATE TABLE IF NOT EXISTS usage (

      time      TIMESTAMP         NOT NULL,

      metric    TEXT              NOT NULL,

      account   TEXT              NOT NULL,

      usage     DOUBLE PRECISION  NOT NULL,

      data      JSONB,

      events    JSONB,

      unit      TEXT

    );

 

select query:

SelectQuery select = db.createSelectFrom(Usage.TABLE, Usage.METRIC_FIELD, Usage.ACCOUNT_FIELD, sum(Usage.USAGE_FIELD),
        inline(new Timestamp(time_from)), inline(new Timestamp(time_to)), Usage.UNIT_FIELD, Usage.DATA_FIELD,
        groupConcat(Usage.EVENTS_FIELD));

what at the end corresponds to the query: 

 select 

  "metric", 

  "account", 

  sum("usage"), 

  timestamp '1970-01-01 00:00:00.0', 

  timestamp '2019-08-22 05:15:37.0', 

  "unit", 

  "data", 

  array_agg("events")

from "usage"

where "time" between timestamp '1970-01-01 00:00:00.0' and timestamp '2019-08-22 05:15:37.0'

group by 

  "metric", 

  "account", 

  "unit", 

  "data"

but in fact I need not  array_agg("events") but json_agg("events"). Can it be handled by JOOQ? 

best, 

Oleksii 

[Lukas Eder]

Hi Oleksii,

jOOQ currently doesn't support any JSON data types out of the box because there's no standard JSON API in the JDK (yet). But you can easily work around this limitation by using jOOQ's plain SQL API:

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

And perhaps a custom data type binding, if you want to work with some third party JSON library:

https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

Let me know if you need any specific help with that

Thanks,

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.

[aleksey...@gmail.com]

Thank you, Lukas! 

The workaround with API quickly solved my issue! 

many thanks,

Oleksii

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