jOOQ JSON formatting as array of objects

Question

I have the following (simplified) jOOQ query:

val result = context.select(
  jsonObject(
    key("id").value(ITEM.ID),
    key("title").value(ITEM.NAAM),
    key("resources").value(
      jsonArrayAgg(ITEM_INHOUD.RESOURCE_ID).absentOnNull()
    )
  )
).from(ITEM).fetch()

Now the output that I want is:

[
    {
        "id": "0da04cc5-f70c-4fb3-b5c7-dc645d342631",
        "title": "Title1",
        "resources": [
            "8b0f6d5c-67fc-47ca-be77-d1735e7721ce",
            "ea0316db-1cfd-46d7-8260-5c1a4e65a0cd"
        ]
    },
    {
        "id": "0f7e67e6-5187-47e2-9f1d-dab08feba38b",
        "title": "Title2"
    }
]

result.formtJSON() gives the following output:

{
    "fields": [
        {
            "name": "json_object",
            "type": "JSON"
        }
    ],
    "records": [
        [
            {
                "id": "0da04cc5-f70c-4fb3-b5c7-dc645d342631",
                "title": "Title 1"
            }
        ]
    ]
}

Disabling the headers with result.formatJSON(JSONFormat.DEFAULT_FOR_RECORDS) will get me:

[
    [
        {
            "id": "0da04cc5-f70c-4fb3-b5c7-dc645d342631",
            "title": "Title1",
            "resources": [
                "8b0f6d5c-67fc-47ca-be77-d1735e7721ce",
                "ea0316db-1cfd-46d7-8260-5c1a4e65a0cd"
            ]
        }
    ],
    [
        {
            "id": "0f7e67e6-5187-47e2-9f1d-dab08feba38b",
            "title": "Title2"
        }
    ]
]

where I don't want the extra array.

Further customizing the JSONformatter with result.formatJSON(JSONFormat().header(false).recordFormat(JSONFormat.RecordFormat.OBJECT)) I get:

[
    {
        "json_object": {
            "id": "0da04cc5-f70c-4fb3-b5c7-dc645d342631",
            "title": "Title1",
            "resources": [
                "8b0f6d5c-67fc-47ca-be77-d1735e7721ce",
                "ea0316db-1cfd-46d7-8260-5c1a4e65a0cd"
            ]
        }
    },
    {
        "json_object": {
            "id": "0f7e67e6-5187-47e2-9f1d-dab08feba38b",
            "title": "Title2"
        }
    }
]

where I don't want the object wrapped in json_object.

Is there a way to get the output I want?

Answer 1

Doing it with Result.formatJSON()

This is clearly a flaw in the jOOQ 3.14.0 implementation of Result.formatJSON(). In the special case where there is only one column, and that column is of type JSON or JSONB, the column name may not really matter, and thus its contents should be flattened into the object describing the row. I've created a feature request for this: https://github.com/jOOQ/jOOQ/issues/10953. It will be available in jOOQ 3.15.0 and 3.14.4. You will be able to do this:

result.formatJSON(JSONFormat().header(false).wrapSingleColumnRecords(false));

The RecordFormat is irrelevant here. This works the same way for RecordFormat.ARRAY and RecordFormat.OBJECT

Doing it directly with SQL

Of course, you can always work around this by moving all the logic into SQL. You probably simplified your query by omitting a JOIN and GROUP BY. I'm assuming this is equivalent to what you want:

JSON result = context.select(
  jsonArrayAgg(jsonObject(
    key("id").value(ITEM.ID),
    key("title").value(ITEM.NAAM),
    key("resources").value(
      select(jsonArrayAgg(ITEM_INHOUD.RESOURCE_ID).absentOnNull())
      .from(ITEM_INHOUD)
      .where(ITEM_INHOUD.ITEM_ID.eq(ITEM.ID))
    )
  ))
).from(ITEM).fetchSingle().value1()

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()