How to deserialize JSON from Postgres row_to_json() back to Table record in Jooq?

Question

I'm trying to get a Jooq Table record from json produced with postgres row_to_json().

I have a Table called InventoryItem in Postgres with the following schema:

CREATE TABLE "InventoryItem" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "price_per_100_units" double precision,
  "weight_in_kilograms" double precision,
  "inventory_count" integer,
  "expected_value" double precision,
  "description" text
);

When I use row_to_json() in Postgres to create a Json for a single row from this table, I get:

{
  "id": 6,
  "price_per_100_units": null,
  "weight_in_kilograms": null,
  "inventory_count": null,
  "expected_value": 142,
  "description": null
}

I couldn't find a way to take the above Json and convert it into an instance of Jooq InventoryItemRecord.

I tried to use Jooq's import Json functionality, but that only works for the Json format produced from Jooq export to Json. It doesn't work with row_to_json() output.

As a last resort, I could write a Pojo to which I deserialize the above json and then create JooqRecord from this pojo, but this seems like a lot of work for multiple tables.

How does one convert row_to_json() output to a Jooq TableRecord?

See JSON Functions and Operators json_populate_... and json_to_,,,. — Belayer
– Belayer, Commented Jan 5, 2023 at 18:00
@Belayer Thanks for your comment, it set me in the direction to my solution. — J. Cool
– J. Cool, Commented Jan 12, 2023 at 15:51

Lukas Eder · Accepted Answer · 2023-01-09 10:12:03Z

1

You can use DSLContext::fetchFromJSON to load any jOOQ supported JSON format into a jOOQ Result, e.g.

Object v = ctx.fetchValue(
    "select row_to_json(x) from inventory_item as x where id = 1");
InventoryItemRecord record = ctx.fetchFromJSON("[" + v + "]")
                                .into(INVENTORY_ITEM)
                                .get(0);

You're probably looking for this convenience feature, which isn't available yet in jOOQ 3.17:

#8016 Add API to load individual records from XML, JSON, CSV, etc.

But the above is almost equivalent, except that you have to:

Wrap your JSON object in a JSON array, manually
Map your generic Record containing the JSON data into an InventoryItemRecord manually.

answered Jan 9, 2023 at 10:12

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J. Cool Over a year ago

your solution solved my problem. I wanted to add this for anyone else running into this problem: I used this bit of config while outputting json from jooq: ` JSONFormat jsonFormat = new JSONFormat().header(false).recordFormat(JSONFormat.RecordFormat.OBJECT); ` The RecordFormat.OBJECT changed the output format from array to object and made it easy.

Lukas Eder Over a year ago

@J.Cool: You can also add an answer here on Stack Overflow, to increase visibility for anyone else. I think it will certainly be helpful

ThrowsError · Accepted Answer · 2023-01-14 10:42:04Z

0

Adding my comment as a full answer to be clear. Jooq by default seems to use the array format when exporting a record to json. If you're trying to interface postgres row_to_json(), you need to tell Jooq to use Json object format when exporting to json.

I was able to achieve this by:

JSONFormat jsonFormat = new
JSONFormat().header(false).recordFormat(JSONFormat.RecordFormat.OBJECT);

Specifying RecordFormat.OBJECT does the trick.

edited Jan 14, 2023 at 10:42

ThrowsError

1,7492 gold badges20 silver badges49 bronze badges

answered Jan 13, 2023 at 16:07

J. Cool

1513 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to deserialize JSON from Postgres row_to_json() back to Table record in Jooq?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related