distinctJSONPathsAndTypes
Calculates the list of distinct paths and their types stored in 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 the list of distinct paths and their types stored in JSON column.
:::note
If JSON declaration contains paths with specified types, these paths will be always included in the result of distinctJSONPaths/distinctJSONPathsAndTypes functions even if input data didn't have values for these paths.
:::
Syntax
distinctJSONPathsAndTypes(json)Arguments
json— JSON column.JSON
Returned value
Returns the sorted map of paths and types. Map(String, Array(String))
Examples
Basic usage with mixed types
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 distinctJSONPathsAndTypes(json) FROM test_json;┌─distinctJSONPathsAndTypes(json)───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {'a':['Int64'],'b':['Array(Nullable(Int64))','String'],'c.d.e':['Date'],'c.d.f':['Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))']} │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘With declared JSON paths
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON(a UInt32)) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"b" : "Hello"}'), ('{"b" : "World", "c" : [1, 2, 3]}');
SELECT distinctJSONPathsAndTypes(json) FROM test_json;┌─distinctJSONPathsAndTypes(json)────────────────────────────────┐
│ {'a':['UInt32'],'b':['String'],'c':['Array(Nullable(Int64))']} │
└────────────────────────────────────────────────────────────────┘Introduced in version 24.9.0.