Add support for new JSON type

[mshustov]

Scope:

• Variant type Add Variant Type #1453
• Dynamic type Add Dynamic Type #1454
• JSON type Add JSON Type #1455
• Deprecation of Object('json')
• performance benchmarking for string- and binary-based implementation Performance Benchmarking for JSON type serialization, Object vs String #1474

from @SpencerTorres preliminary research

What works:

• Inserting JSON as Go string
• Selecting JSON when it is first cast to a string (::String). Must be scanned into a Go string type.
• Selecting Dynamic column (from within a JSON object) when it is first cast to a string (::String). Must be scanned into a Go string type.

What doesn't work:

• Inserting as Go json.RawMessage ([]byte) does not work (although I don't believe this works for String either)
• Selecting does not work for non-casted JSON type column, the Go driver reports it as an unsupported type
• Selecting does not work for non-casted Dynamic type column, the Go driver reports it as an unsupported type

Sample SQL:

SET allow_experimental_json_type = 1;

CREATE TABLE json_test (id String, obj JSON) Engine = MergeTree ORDER BY id;

INSERT INTO json_test (id, obj) VALUES ('a', '{ "key1": "value1", "nestedKey2": { "nestedValue2": 5 }, "arrayKey3": [4, 5, 6] }')

SELECT id, obj.key1, obj.nestedKey2, obj.arrayKey3, obj.nestedKey2.nestedValue2, obj FROM json_test;

Sample code:

// regular struct
type jsonRow struct {
	ID  string `ch:"id"`
	Obj string `ch:"obj"`
}

// row to insert, note that the JSON data is a `string`
var insertRow = jsonRow{
	ID:  "c",
	Obj: `{ "key1": "value1", "nestedKey2": { "nestedValue2": 5 }, "arrayKey3": [4, 5, 6] }`,
}

// async insert, blocking
err = db.AsyncInsert(context.Background(), "INSERT INTO json_test (id, obj) VALUES (?, ?)", true, insertRow.ID, insertRow.Obj)
if err != nil {
	fmt.Println(err)
	return
}

// This is where things break. The JSON column MUST be cast to a string here.
// Also renamed to fit struct tags.
// If the JSON column is not cast to string, the following error is printed: clickhouse: unsupported column type "JSON"
var rows []jsonRow
err = db.Select(context.Background(), &rows, "SELECT id, obj::String as obj FROM json_test")
if err != nil {
	fmt.Println(err)
	return
}

// Print object
fmt.Printf("%+v\n", rows)
// Output (formatted):
// [{
// 	ID: c
// 	Obj: {"arrayKey3":["4","5","6"],"key1":"value1","nestedKey2":{"nestedValue2":"5"}}
// }]

Example of selecting an object key in Grafana (which uses our latest Go driver):

[stsmurf]

Thanks for working on this! I was starting to use the new JSON data type today and AsyncInsert does work, however if attempting to use

err := db.PrepareBatch(context.Background(), "INSERT INTO json_test")

you will get the error unsupported column type "JSON" .

[lazharichir]

SOLVED: only works by appending SETTINGS output_format_json_quote_64bit_integers = 0 to the query; the same user-level setting did nothing.

Leaving the original below for future readers/googlers...

It's also problematic that selecting a JSON or Dynamic column with UInt64 (i.e., golang's int or int64) results in a string being returned even if the JSON field has the UInt64 type.

This doesn't change when using SET output_format_json_quote_64bit_integers = 0;

You can see this in @mshustov's code snippet:

{ "key1": "value1", "nestedKey2": { "nestedValue2": 5 }, "arrayKey3": [4, 5, 6] }
becomes
{"arrayKey3":["4","5","6"],"key1":"value1","nestedKey2":{"nestedValue2":"5"}}

[SpencerTorres]

Found a bug that prevented enabling output_format_native_write_json_as_string=1, keep this in mind for other clients doing a string implementation. You'll need to update to a newer version

ClickHouse/ClickHouse#73179

[SpencerTorres]

Keeping this open until we handle the other tasks (it keeps getting auto closed from the PRs)

[SpencerTorres]

Performance benchmarking done for read/write: #1474 (comment)

I added a short writeup of the conclusions along with recommendations for users. Perhaps we can copy it to docs at some point

[quoeamaster]

Hi Spencer, is the issue solved?

I still have the error failed, clickhouse: unsupported column type "JSON"

my go client -> github.com/ClickHouse/clickhouse-go/v2 v2.30.0

and I am using PrepareBatch() for batch inserts (not AsyncInsert())

a snippet on settings employed (based on this github issue)

      c.conn, e = clickhouse.Open(&clickhouse.Options{
		Addr: []string{c.cfg.URL},
		Auth: clickhouse.Auth{
			Database: c.cfg.Database,
			Username: c.cfg.Username,
			Password: c.cfg.Password,
		},
		ClientInfo: clickhouse.ClientInfo{
			Products: []struct {
				Name    string
				Version string
			}{
				{Name: `sample_app`, Version: `0.1.0`},
			},
		},
		Debugf: func(format string, args ...interface{}) {
			fmt.Printf(format, args...)
		},
		TLS: &tls.Config{
			InsecureSkipVerify: true,
		},
		Settings: clickhouse.Settings{
			"allow_experimental_json_type":              1,
			"output_format_json_quote_64bit_integers":   0,
			"output_format_native_write_json_as_string": 1,
		},
	})

Thanks

[mshustov]

my go client -> github.com/ClickHouse/clickhouse-go/v2 v2.30.0

@quoeamaster JSON type supported from 2.30.1, see https://github.com/ClickHouse/clickhouse-go/releases/tag/v2.30.1

[SpencerTorres]

Related issue with JSON strings: ClickHouse/ClickHouse#75832

Strings work now, but they don't preserve the same settings, example
If you insert this, x will be an Int64:

{ "x": 42 }

But the server will send back

{ "x": "42" }

If you try to change output_format_json_quote_64bit_integers, it will have no affect on JSON type. After this fix we will get the expected:

{ "x": 42 }

Temporary fix is to cast JSON to String