JSON array to rows in ClickHouse

Question

What query would transpose the following JSON value to multiple rows. Examples with nested JSON objects are sparse.

[{"a":{"b":"val", "c":1}},{"a":{"b":"val", "c":4}}]

Tried combinations of JSONExtract but having difficulty with nested object

Answer 1

You can use JSONEachRow format to read an array of objects. Nested object ("a" : {...}) can be read into:

1. String and than be parsed it using JSONExtract:

:) select * from format(JSONEachRow, 'a String', '[{"a":{"b":"val", "c":1}},{"a":{"b":"val", "c":4}}]')

┌─a──────────────────┐
│ {"b":"val", "c":1} │
│ {"b":"val", "c":4} │
└────────────────────┘

 :) select JSONExtractString(a, 'b') from format(JSONEachRow, 'a String', '[{"a":{"b":"val", "c":1}},{"a":{"b":"val", "c":4}}]')


┌─JSONExtractString(a, 'b')─┐
│ val                       │
│ val                       │
└───────────────────────────┘

2. Named tuple (if all nested objects are known or you want to read only some of them):

avogar-dev :) select * from format(JSONEachRow, 'a Tuple(b String, c UInt32)', '[{"a":{"b":"val", "c":1}},{"a":{"b":"val", "c":4}}]')

┌─a─────────┐
│ ('val',1) │
│ ('val',4) │
└───────────┘

3. Using experimental JSON object type:

 :) select * from format(JSONEachRow, 'a JSON', '[{"a":{"b":"val", "c":1}},{"a":{"b":"val", "c":4}}]') settings allow_experimental_object_type=1

┌─a─────────────────┐
│ {"b":"val","c":1} │
│ {"b":"val","c":4} │
└───────────────────┘

Note: JSON type is still experimental and using it can lean to unexpected bugs.