[client-v2] JSON Support

[chernser]

Description

Client should support JSON Data type (https://clickhouse.com/docs/en/integrations/data-formats/json/overview).

Considerations:

• Client should provide convenient API to get access to row data
• Client should have examples of using a few commonly used JSON parsing libraries
• Client should not implement own JSON parser in any way
• Client documentation should list all details of work with JSON format

---

See also:

• Allow to read/write JSON type as binary string in RowBinary format ClickHouse#70288
• https://github.com/Avogar/ClickHouse/blob/master/src/DataTypes/Serializations/SerializationObject.cpp

[dzmitry-biatenia-smop]

@chernser any idea when 0.7.2 Java client will be released?

[chernser]

Hi @dzmitry-biatenia-smop!
We plan to release 0.7.2 by end of November. However this functionality is not in the 0.7.2: it is a complex and experimental feature - so it will take time to add it to the client.
However 0.7.2 might have a minimal support when JSON columns can be read/written as a string.

How would you use JSON? Do you have a DTO object or do you work directly with some generic classes generated by GSON or Jackson ?

Thanks!

[dzmitry-biatenia-smop]

@chernser the basic case - I was able to create a column with JSON type in Clickhouse table with flag allow_experimental_json_type enabled, and even was able to insert some JSON data there with com.clickhouse.client.api.Client#execute(), but when can running any com.clickhouse.client.api.Client.query("select * from table_with_json_column") I'm getting Unsupported format: "JSON" coming from here because response has JSON type here.
I tried to set output_format_binary_write_json_as_string , but seems it's not supported by my version of server 24.8.4

[chernser]

@dzmitry-biatenia-smop
I will fix this issue for sure.

[dzmitry-biatenia-smop]

@chernser are you going to release that fix separately? any ETA for that fix?

[chernser]

@dzmitry-biatenia-smop
patch should be release by end of this week.
release is by end of the month
another option is to take nightly build.

[dzmitry-biatenia-smop]

@chernser can you please notify me when that patch is ready or point me to the correct nightly build?

[chernser]

@dzmitry-biatenia-smop
yes, sure!

[chernser]

Good day, @dzmitry-biatenia-smop ! I've just merged the JSON support (see example).
Nightly build should be ready in a several minutes. Here is how to use it https://gist.github.com/chernser/b4eacfde70093847449e3aef8e51ae8e

[dzmitry-biatenia-smop]

@chernser I tried nightly build as per example you've provided with dependency on new nightly build,

<repository>
  <id>ossrh</id>
  <name>Sonatype OSSRH</name>
  <url>https://s01.oss.sonatype.org/content/repositories/snapshots/</url>
</repository>

with version

<dependency>
      <groupId>com.clickhouse</groupId>
      <artifactId>client-v2</artifactId>
      <version>0.7.1-SNAPSHOT</version>
</dependency>

and event could not build a client with those options:
.serverSetting(ClientSettings.INPUT_FORMAT_BINARY_READ_JSON_AS_STRING, "1")
.serverSetting(ClientSettings.OUTPUT_FORMAT_BINARY_WRITE_JSON_AS_STRING, "1")
getting
Setting input_format_binary_read_json_as_string is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings: Maybe you meant ['input_format_json_read_bools_as_strings','input_format_json_read_objects_as_strings']. (UNKNOWN_SETTING) (version 24.8.4.13 (official build))
or
Setting output_format_binary_write_json_as_string is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings: Maybe you meant ['output_format_arrow_string_as_string','output_format_parquet_string_as_string']. (UNKNOWN_SETTING) (version 24.8.4.13 (official build))
errors.
When I'm trying to read JSON in java client without those options, I'm getting another runtime error:
Reading JSON from binary is not implemented yet

Please, advise

[VatsekVlad]

Hi @chernser!
Are there any updates or plans to resolve this issue? We’re very interested in leveraging the potential of JSON in ClickHouse for working with data in Metabase, but as far as I understand, this is currently a blocker.
Is there any way we could help accelerate resolving this, if so, feel free to reach out.