topic Re: Retrieving OBJECT values with the JDBC driver may lead to invalid JSON in Data Engineering

Retrieving OBJECT values with the JDBC driver may lead to invalid JSON

EricCournarie — Wed, 29 Oct 2025 13:15:38 GMT

Hello,

Using the JDBC driver , I try to retrieve values in the ResultSet for a OBJECT type.

Sadly, it returns invalid JSON

Given the SQL

CREATE OR REPLACE TABLE main.eric.eric_complex_team (
`id` INT,
`nom` STRING,
`infos` STRUCT<`age`: INT, `ville`: STRING, `genre`: BOOLEAN, `anniv`: DATE, `vma`: DECIMAL(3,1), `temps`: TIMESTAMP_NTZ>,
`tags` ARRAY<STRING>
);
INSERT INTO main.eric.eric_complex_team VALUES
(1, 'Manon', named_struct('age', 30, 'ville', 'Paris', 'genre', true, 'anniv', '1995-10-12', 'vma', 17.4, 'temps', '2025-10-29 12:12:12.789'), array('elite', 'science')),
(2, 'Paul', named_struct('age', 25, 'ville', 'Lyon', 'genre', false, 'anniv', '2000-09-12', 'vma', 19.6, 'temps', '2025-10-29 12:10:12.234'), array('dev', 'elite'));

The value returned by rs.getObject() is something like

{"age":30,"ville":"Paris","genre":true,"anniv":1995-10-12,"vma":17.4,"temps":2025-10-29 12:12:12.789}

Dates are not quoted.

Is there an opened bug or a workaround ?

Thanks in advance

Eric

Re: Retrieving OBJECT values with the JDBC driver may lead to invalid JSON

K_Anudeep — Wed, 29 Oct 2025 15:05:04 GMT

Hello @EricCournarie ,

I believe this is a JDBC driver limitation. The Databricks JDBC driver serializes complex types (STRUCT/ARRAY) to a JSON-like string but doesn’t always quote DATE/TIMESTAMP (and some characters) correctly, so rs.getObject()/rs.getString() can yield invalid JSON.

One way to fix this is to serialise on the server with to_json

Re: Retrieving OBJECT values with the JDBC driver may lead to invalid JSON

EricCournarie — Wed, 29 Oct 2025 15:43:45 GMT

Hello, thanks for the quick response .

Sadly I do not have the hand on the SQL request , so no way for me to modify it ...