JSONB aggregate results are displayed differently in sqlite-jdbc and SQLite Fiddle

[LLMTesting-Ojiken]

Describe the bug

I observed inconsistent representations of JSONB aggregate function results between SQLite Fiddle and sqlite-jdbc.

For the same SQL queries, SQLite Fiddle displays the decoded JSON array, whereas sqlite-jdbc exposes the binary JSONB representation, which may appear as raw characters or hexadecimal data.

This discrepancy can make equivalent results appear different to users and applications, particularly when JSONB values are retrieved or displayed as strings.

To Reproduce

Case 1: JSONB_GROUP_ARRAY with a string literal

SELECT JSONB_GROUP_ARRAY('Ia');

Observed results:

• SQLite Fiddle: ["Ia"]
• sqlite-jdbc: ;'Ia

The hexadecimal representation of the JSONB value is:

3B274961

Case 2: JSONB_GROUP_ARRAY with a scalar subquery

SELECT JSONB_GROUP_ARRAY(
    (SELECT CAST(json('Ia' AND 'Ia') AS TEXT)
     FROM (SELECT 1 AS k)
     WHERE k = 1
     ORDER BY k)
);

Observed results:

• SQLite Fiddle: [0]
• sqlite-jdbc: 0x2B1330

The hexadecimal value 2B1330 represents the JSONB array [0].

Expected behavior

I would like clarification on the expected JDBC behavior when retrieving JSONB aggregate results.

Ideally, applications should have a documented and consistent way to retrieve JSONB results in a human-readable JSON format, such as ["Ia"] or [0], rather than inadvertently interpreting binary JSONB data as ordinary text.

If the driver intentionally exposes JSONB values as BLOBs, it would be helpful to clarify the appropriate retrieval method and whether ResultSet.getString() is expected to return decoded JSON or a raw binary representation.

Logs

No exception is thrown. The issue concerns the representation of returned JSONB values.

Environment

• OS: macOS, Apple Silicon (arm64)
• SQLite interface: SQLite Fiddle and sqlite-jdbc
• sqlite-jdbc version: [Please specify]
• SQLite version: [Please specify]
• JDBC result retrieval method: [Please specify, e.g., ResultSet.getString() or database client display]

Additional context

According to the SQLite JSON1 documentation, the jsonb_ aggregate functions return binary JSONB values rather than ordinary JSON text.

Therefore, the observed differences may reflect distinct result-rendering or JDBC conversion behaviors rather than different SQL evaluation results.

The purpose of this report is to clarify whether sqlite-jdbc's JSONB result handling is intentional and whether a more consistent or explicitly documented JSONB-to-text retrieval mechanism should be provided.

[gotson]

It seems SQLite Fiddle uses the sqlite CLI, which has different rendering. This is not comparable.

[LLMTesting-Ojiken]

SQLite Fiddle is merely a presentation method. What I actually meant to say is that there is a significant difference in the performance between these two. For example, SELECT JSONB_GROUP_ARRAY((SELECT CAST((json((((('Ia'))) AND ('Ia')))) AS TEXT))
FROM (SELECT 1 AS k)
WHERE k = 1
ORDER BY k));

SELECT JSONB_GROUP_ARRAY((SELECT CAST((json((((('Ia'))) AND ('Ia')))) AS TEXT)
FROM (SELECT 1 AS k)
WHERE 1
ORDER BY k));

These two queries resulted in the following outcomes: 0x2B1330 and 0x2B1730.

[AbdullahTammar]

The two hex values in your follow-up represent different JSON values, not just different display formats. I checked them with SQLite 3.53.1:

SELECT json(x'2B1330'), json(x'2B1730');
-- [0]                  ["0"]

The first contains a JSON number; the second contains a JSON string. That is separate from the original JDBC-versus-Fiddle rendering question.

For human-readable JDBC output, convert explicitly in SQL and then use getString():

SELECT json(jsonb_group_array('Ia'));
-- ["Ia"]

Use getBytes() if you intentionally want the binary JSONB value. Casting the BLOB to TEXT is not JSONB decoding.

To investigate the two query variants, compare sqlite_version() and json(...) of each complete aggregate on both engines. SQLite distinguishes ordinary SQL text from values coming directly from JSON functions; subquery/expression handling can therefore matter. The hex values alone do not establish which query transformation caused the difference or that JDBC caused it. A minimal pair of runnable queries plus the actual bundled SQLite versions would isolate that.

References: JSON value arguments and JSONB, JSONB encoding.

[LLMTesting-Ojiken]

This has been confirmed as a bug and has been fixed: https://sqlite.org/src/info/8ab55e0185a1956f