JSON v2

[gingerwizard]

Issue to track missing features from current JSON support. Contributors feel free to pick up, none super urgent and all nice to have:

• Support for pointers inside structs and maps
• Support for BigInt (encode as float64?? with precision loss?)
• Support for Polygons + other shapes
• Support for Points
• Should we support mixed types slices in interface[]{} and nils e.g. ["4.2", 2.1, 1.1] - allow conversion to string and/or higher precision?
• Support insertion of arrays e.g. insert into test_json (col) values ('[{"name": "b"}, {"test": "c"}]')

[Astlol]

Hi,

What do you mean by Null types?

What do you think about ...

1. is bool type supported? (now bool -> int (0, 1) (a problem is in Scan(map) )
2. pls, consider this Scan( &map ): now map gets loaded with all columns (with zero values as well)
   may be add func (load cols with values ("not null")

(some times it is difficult to make difference between zero value and null in json)

[gingerwizard]

(2) is a limitation of ClickHouse itself. It returns all the columns - we have no way of knowing which are valid. We just serialize whats returned.

(1) this seems like a bug. Will ensure its fixed.

[msyesyan]

@gingerwizard will you support insert array?

for example:

I have a table like

create table test_json (col JSON) ENGINE=MergeTree PRIMARY KEY(col)

and I want to insert

insert into test_json (col) values ('[{"name": "a"}]')

[gingerwizard]

create table test_json (col JSON) ENGINE=MergeTree PRIMARY KEY(col)

is not valid

Code: 549. DB::Exception: Received from oudbme6scf.us-east-2.aws.clickhouse.cloud:9440. DB::Exception: Column `col` with type Object('json') is not allowed in key expression, it's not comparable. (DATA_TYPE_CANNOT_BE_USED_IN_KEY)

lets assume, you meant:

CREATE TABLE test_json
(
    `col` JSON
)
ENGINE = MergeTree
ORDER BY tuple()

From clickhouse-client

SELECT *
FROM test_json
FORMAT JSONEachRow

Query id: 661e63b6-d621-4ff6-8386-3ebeff1fc1f1

{"col":{"name":["a"]}}

1 row in set. Elapsed: 0.002 sec.

then

insert into test_json (col) values ('[{"name": "b"}]')
insert into test_json (col) values ('[{"name": "b"}, {"test": "c"}]')

SELECT *
FROM test_json
FORMAT JSONEachRow

Query id: 5c366cb0-86df-494b-84e0-d976bfd3c9a6

{"col":{"name":["b"],"test":[]}}
{"col":{"name":["a"],"test":[]}}
{"col":{"name":["b",""],"test":["","c"]}}

Looks like we probably should support but I need to see whats doing this array to map interpretation - the client or the server. And even then it maybe not what you expect.

[msyesyan]

Looks like we probably should support but I need to see whats doing this array to map interpretation - the client or the server. And even then it maybe not what you expect.

@gingerwizard yes this is what i want. It would be great if batch api could support this feature

[gingerwizard]

Added to above. welcome contributions otherwise this will be v3 I suspect.

[msyesyan]

thanks, and I'll contribute more as I gain more knowledge abount clickhouse

[msyesyan]

@gingerwizard we cannot insert an empty map or a nil value into a json column now, do you think it's a bug？Or do you think this is a desired behaviour? If it's a bug, I think I can try to fix it, it doesn't look too hard