I have the following table
SET flatten_nested = 0;
CREATE TABLE test.hm
(
customDimensions Array(Nested(index Nullable(Int64), value Nullable(String)))
)
engine = Memory;
I am trying to insert into it with the following query:
INSERT INTO test.hm FORMAT JSONEachRow {"customDimensions": [{"index": 1, "value": 2}]}
But it fails with
Code: 130, e.displayText() = DB::Exception: Array does not start with '[' character: (while reading the value of key customDimensions): (at row 1) (version 21.8.4.51 (official build))
How to fix it and insert JSON into flatten_nested = 0 having multi lvl nested hierarchy?
Are you sure you need Array(Nested because it's two-dimensional array.
you can use select to get understanding what JSONEachRow CH expectes
insert into test.hm values([[(1,'test1'), (2,'test2')]]);
select * from test.hm format JSONEachRow;
{"customDimensions":[[["1","test1"],["2","test2"]]]}
I guess you really need Array(Tuple(index Nullable(Int64), value Nullable(String)))
And you can use JSONExtract