ReferenceSQL ReferenceAggregate Functions

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.