Json aggregation in postgres with JOOQ

[a***@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

Post by a***@gmail.com
Good day!
I didn't find the way to aggregate json with jooq.
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
);
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));
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
--
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
For more options, visit https://groups.google.com/d/optout.

[a***@gmail.com]

Thank you, Lukas!

The workaround with API quickly solved my issue!

many thanks,
Oleksii
--

Post by a***@gmail.com
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
For more options, visit https://groups.google.com/d/optout.