Using an unstructured JSON column for Clickhouse v22.3

Question

I'm using the new JSON column for Clickhouse, which was added in version 22.3.

There is a great blog post here on the Clickhouse website about it - https://clickhouse.com/blog/clickhouse-newsletter-april-2022-json-json-json/

I'm trying to add unstructured JSON, where the document type isn't known until it's inserted. I've been using Postgres with JSONB and Snowflake with VARIANT for this and it's been working great.

With Clickhouse (v22.4.5.9, current as of 2022-05-14), here is what I'm doing:

-- We need to enable this flag to use JSON, as it's currently (as of 2022-05-14) experimental.
set allow_experimental_object_type = 1;

-- Create an example table for our testing, we can use the Memory engine as it'll be tiny.
create table example_json (
  json_data json
)
engine = Memory();

-- Now let's insert two different JSON documents, usually this would be batched, but for the sake of this
-- example, let's just use two inserts.
-- insert into example_json(json)
INSERT INTO example_json VALUES ('{"animal": "dog"}');

-- Returns ('dog'), great.
select * from example_json;

-- Returns "dog", even cooler.
select json_data.animal from example_json;

-- Now we want to change around the values
INSERT INTO example_json VALUES ('{"name": "example", "animal": {"breed": "cat"}}');

This throws the following error:

Code: 15. DB::Exception: Data in Object has ambiguous paths: 'animal.breed' and 'animal'. (DUPLICATE_COLUMN) (version 22.4.5.9 (official build))

I think that under the hood Clickhouse is converting the keys to column types, but won't change the type if a conflicting type is then created?

Is there a way to insert JSON like this to Clickhouse?

Answer 1

Can you try parse_json function within Snowflake to insert values into the JSON table. https://docs.snowflake.com/en/sql-reference/functions/parse_json.html#examples

Basically try this DML

INSERT INTO example_json select 1, parse_json ($$ { "name": "example", "animal": {"breed": "cat" } $$);