0

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

Suleman C
  • 783
  • 4
  • 17
  • 1
    Are you importing the data? Because if you are, then `JSONEachRow` can handle arrays of JSON: https://clickhouse.com/docs/en/integrations/data-formats/json#importing-from-an-array-of-json-objects – Rich Raposa Mar 29 '23 at 11:05
  • Does this answer your question? [How to extract json from json in clickhouse?](https://stackoverflow.com/questions/58819341/how-to-extract-json-from-json-in-clickhouse) – vladimir Mar 29 '23 at 17:00
  • @vladimir unfortunately not as there is an object vs an array in my example. I wasn't able to use the answers there – Suleman C Mar 30 '23 at 05:14

2 Answers2

2

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                       │
└───────────────────────────┘
  1. 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) │
└───────────┘
  1. 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.

Pavel Kruglov
  • 91
  • 2
0

take a look at this

https://clickhouse.com/docs/knowledgebase/importing_and_working_with_JSON_array_objects

see if that helps

Nellicus
  • 43
  • 1
  • 3