Skip to main content

Object Data Type (deprecated)

This feature is not production-ready and deprecated. If you need to work with JSON documents, consider using this guide instead. A new implementation to support JSON object is in progress and can be tracked here.

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

JSON can be used as an alias to Object('json') when setting use_json_alias_for_old_object_type is enabled.

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 also display the field names 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​