Skip to main content

JSON

note

This feature is experimental and is not production ready. If you need to work with JSON documents, consider using this guide instead.

Stores JavaScript Object Notation (JSON) documents in a single column.

JSON is an alias for Object('json').

note

The JSON data type is an experimental feature. To use it, set allow_experimental_object_type = 1.

Example​

Example 1

Creating a table with a JSON column and inserting data into it:

CREATE TABLE json
(
    o JSON
)
ENGINE = Memory
INSERT INTO json VALUES ('{"a": 1, "b": { "c": 2, "d": [1, 2, 3] }}')
SELECT o.a, o.b.c, o.b.d[3] FROM json
┌─o.a─┬─o.b.c─┬─arrayElement(o.b.d, 3)─┐
│   1 │     2 │                      3 │
└─────┴───────┴────────────────────────┘

Example 2

To be able to create an ordered MergeTree family table the sorting key has to be extracted into its column. For example, to insert a file of compressed HTTP access logs in JSON format:

CREATE TABLE logs
(
    timestamp DateTime,
    message JSON
)
ENGINE = MergeTree
ORDER BY timestamp
INSERT INTO logs
SELECT parseDateTimeBestEffort(JSONExtractString(json, 'timestamp')), json
FROM file('access.json.gz', JSONAsString)

Displaying JSON columns​

When displaying a JSON column ClickHouse only shows the field values by default (because internally, it is represented as a tuple). You can display the field names as well by setting output_format_json_named_tuples_as_objects = 1:

SET output_format_json_named_tuples_as_objects = 1

SELECT * FROM json FORMAT JSONEachRow
{"o":{"a":1,"b":{"c":2,"d":[1,2,3]}}}

Related Content​