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

[EricCournarie]

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

[K_Anudeep]

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. 

Similar thread: https://stackoverflow.com/questions/79432459/how-to-fetch-nested-data-structures-in-databricks-using...

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

SELECT
  id,
  nom,
  to_json(infos, map('dateFormat','yyyy-MM-dd','timestampFormat','yyyy-MM-dd HH:mm:ss.SSS')) AS infos_json,
  to_json(tags) AS tags_json
FROM main.eric.eric_complex_team;​

[EricCournarie]

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 ...