would you be so kind to help me to parse i.e. make structured table from the following JSON value I receive from base. type is defined as Array(string)
[{“id”: 1, “categ”: “abc”, “fig”: null, “goods”: [{“g_id”: 05, “code”: “xxx” }, {“g_id”: 06, “code”: “xx2” }]},
{“id”: 2, “categ”: “de”, “fig”: null, “goods”: [{“g_id”: 07, “code”: “xxx” }, {“g_id”: 08, “code”: “xx2” }]}]
I want to receive table with columns id, categ, fig, g_id, code.
id, categ, fig will repeat for goods list.
I tried JSOnExtract, but haven’t received good result.
Natalya Tsareva is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
https://fiddle.clickhouse.com/f1a50d7d-d77b-4622-800b-c35616906a4f
create table t Engine=Memory empty as select * from format('JSONEachRow', '[{"id": 1, "categ": "abc", "fig": null, "goods": [{"g_id": 05, "code": "xxx" }, {"g_id": 06, "code": "xx2" }]},
{"id": 2, "categ": "de", "fig": null, "goods": [{"g_id": 07, "code": "xxx" }, {"g_id": 08, "code": "xx2" }]}]') settings schema_inference_make_columns_nullable = 0, describe_compact_output=1;
set input_format_json_read_arrays_as_strings=1;
insert into t format JSONEachRow [{"id": 1, "categ": "abc", "fig": null, "goods": [{"g_id": 05, "code": "xxx" }, {"g_id": 06, "code": "xx2" }]}, {"id": 2, "categ": "de", "fig": null, "goods": [{"g_id": 07, "code": "xxx" }, {"g_id": 08, "code": "xx2" }]}]
;
select * from t format Pretty;
+----+-------+-----+-----------------------+
| id | categ | fig | goods |
+----+-------+-----+-----------------------+
1. | 1 | abc | | [('xxx',5),('xx2',6)] |
+----+-------+-----+-----------------------+
2. | 2 | de | | [('xxx',7),('xx2',8)] |
+----+-------+-----+-----------------------+
show create table t format TSVRaw;
CREATE TABLE default.t
(
`id` Int64,
`categ` String,
`fig` String,
`goods` Array(Tuple(code String, g_id Int64))
)
ENGINE = Memory
JSONExtract does not work because of leading zeros 05 https://github.com/ClickHouse/ClickHouse/issues/66594
https://fiddle.clickhouse.com/5c647cdb-6add-4fdb-8c00-0faba2647a15