distinctJSONPaths
Calculates a list of distinct paths stored in a JSON column.
This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See SQL interface scope.
Calculates a list of distinct paths stored in a JSON column.
Syntax
distinctJSONPaths(json)Arguments
json— JSON column.JSON
Returned value
Returns the sorted list of paths. Array(String)
Examples
Basic usage with nested JSON
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"a" : 42, "b" : "Hello"}'), ('{"b" : [1, 2, 3], "c" : {"d" : {"e" : "2020-01-01"}}}'), ('{"a" : 43, "c" : {"d" : {"f" : [{"g" : 42}]}}}');
SELECT distinctJSONPaths(json) FROM test_json;┌─distinctJSONPaths(json)───┐
│ ['a','b','c.d.e','c.d.f'] │
└───────────────────────────┘With declared JSON paths
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"a" : 42, "b" : "Hello"}'), ('{"b" : [1, 2, 3], "c" : {"d" : {"e" : "2020-01-01"}}}'), ('{"a" : 43, "c" : {"d" : {"f" : [{"g" : 42}]}}}')
SELECT distinctJSONPaths(json) FROM test_json;┌─distinctJSONPaths(json)─┐
│ ['a','b','c'] │
└─────────────────────────┘Introduced in version 24.9.0.