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

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.