Importing GeoJSON with a deeply nested object array
Question
How do I import GeoJSON with a nested object array?
Answer
For this tutorial, we will use open data publicly available here. A copy can be found here.
-
Download the data in GeoJSON format and rename the file to
geojson.json. -
Understand the structure.
- Create a table to store the GeoJSON rows.
The requirement here is to generate a row for each object in the features array.
The data type inferred for the field geometry suggests that it translates to ClickHouse's MultiPolygon data type.
- Prepare the data.
The main purpose of the query is to verify that we obtain one row for each object in the features array.
The field features.geometry.coordinates is commented to make the result set more readable.
- Insert the data.
Here, we get the following error:
This is caused by the parsing of features.geometry.coordinates.
- Let's check its data type.
It can be fixed by casting multipolygon.properties.coordinates to Array(Array(Array(Tuple(Float64,Float64)))).
To do so, we can use the function arrayMap(func,arr1,...).
- Insert the data.
Conclusion
Handling JSON can result in a complex task. This tutorial addressed a scenario where a nested object array could make this task even more difficult.
For any other JSON-related requirements, please refer to our documentation.