Skip to main content
    • Knowledge Base/
    Importing And Working With JSON Array Objects

    Importing and Querying JSON Array Objects in ClickHouse

    Learn how to import JSON array objects into ClickHouse and perform advanced queries using JSON functions and array operations.

    Question​

    How do I import JSON arrays and how can I query the inner objects?

    Answer​

    Dump this 1 line JSON array to sample.json

    Check the data:

    Create a table to receive the JSON rows:

    Insert the data:

    Check how the data inference acted on JSON object type:

    Events is an Array of Tuple each containing a eventType String and a time String fields. This latter type is suboptimal (we'd want DateTime instead).

    Let's see the data:

    Let's run a few queries:

    _id and channel of events that have an eventType of value close

    We want to query the time , for example all events between a given time range, but we notice it was imported as String:

    So, in order to handle these as dates, first we want to convert to DateTime. To convert an array we use a map function:

    we can appreciate the diffs using toTypeName on both the arrays:

    now let's get the id of of the rows where time is between a given interval.

    we use arrayCount to see if there is a count greater than than 0 of items in the array returned by the map function that will match the condition x BETWEEN toDateTime('2021-06-18 11:46:00', 'Europe/Rome') AND toDateTime('2021-06-18 11:50:00', 'Europe/Rome')

    ⚠️

    Please remember, at the time of writing this article the current implementation of JSON is experimental and not suited for production.

    This example highlights how to quickly import JSON and start querying it and represents a tradeoff between the ease of use where we import the JSON objects as JSON type with no need to specify upfront the schema type. Convenient for a quick test however for long term use of the data we would like to, with regards to this example to store the data using the most appropriate types, so for the time field, use DateTime instead of String, in order to avoid any post-ingestion phase conversion as illustrated above. Please refer to the documentation for more about handling JSON.

    · 6 min read