Question 90261 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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.


  2. 0

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search