--- title: JSON Data Type reference meta: description: Stores JSON documents in a single column. headingMaxLevels: 2 --- # JSON data type {% pill text="BETA" /%} {% callout type="caution" %} The `JSON` data type is in private beta. If you are interested in using this type, contact Tinybird at or in the [Community Slack](/community). {% /callout %} The `JSON` data type stores JavaScript Object Notation (JSON) documents within a single column. It allows for flexible schema-on-read operations, where individual fields can be accessed as subcolumns. ## Syntax To declare a column of `JSON` type, use the following syntax: ```sql JSON([max_dynamic_paths=N, max_dynamic_types=M, some.path TypeName, SKIP path.to.skip, SKIP REGEXP 'paths_regexp']) [jsonPath] ``` ### Arguments - `max_dynamic_paths`: Optional. An integer indicating the maximum number of distinct paths that can be stored separately as subcolumns within a single data block (e.g., a single data part for a MergeTree table). If this limit is exceeded, additional paths will be grouped together in a single structure. Default value is `1024`. - `max_dynamic_types`: Optional. An integer between `1` and `255` indicating the maximum number of different data types that can be stored within a single path column with a `Dynamic` type across a single data block. If this limit is exceeded, any new types will be converted to `String`. Default value is `32`. - `some.path TypeName`: Optional. A type hint for a specific path within the JSON document. Paths specified with a type hint will always be stored as subcolumns with the given `TypeName`. - `SKIP path.to.skip`: Optional. A hint for a specific path that should be ignored during JSON parsing. Such paths will not be stored in the `JSON` column. If the specified path is a nested JSON object, the entire nested object will be skipped. - `SKIP REGEXP 'paths_regexp'`: Optional. A regular expression hint used to skip paths during JSON parsing. Any path matching this regular expression will not be stored in the `JSON` column. ### Returns A column capable of storing JSON documents, allowing access to its fields as subcolumns. ## Creating a Data Source with JSON columns You can create a data source with JSON columns by using the `JSON` data type in your schema definition. ```tb {% title="test.datasource" %} SCHEMA > `json` JSON `json:$` ENGINE "MergeTree" ENGINE_SORTING_KEY "tuple()" ``` ```bash tb push datasources/test.datasource echo '{"a" : {"b" : 42}, "c" : [1, 2, 3]}\n{"f" : "Hello, World!"}\n{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}' > test.ndjson tb datasource append test test.ndjson tb sql 'select * from test' ``` Result: ```result --------------------------------------------- | json | --------------------------------------------- | {'a': {'b': 42}, 'c': [1, 2, 3]} | | {'f': 'Hello, World!'} | | {'a': {'b': 43, 'e': 10}, 'c': [4, 5, 6]} | --------------------------------------------- ``` You can also specify type hints and paths to skip directly in the data source schema: ```tb {% title="test.datasource" %} SCHEMA > `json` JSON(a.b UInt32, SKIP a.e) `json:$` ENGINE "MergeTree" ENGINE_SORTING_KEY "tuple()" ``` ```bash tb push datasources/test.datasource echo '{"a" : {"b" : 42}, "c" : [1, 2, 3]}\n{"f" : "Hello, World!"}\n{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}' > test.ndjson tb datasource append test test.ndjson tb sql 'select * from test' ``` Result: ```result ----------------------------------------- | json | ----------------------------------------- | {'a': {'b': 42}, 'c': [1, 2, 3]} | | {'a': {'b': 0}, 'f': 'Hello, World!'} | | {'a': {'b': 43}, 'c': [4, 5, 6]} | ----------------------------------------- ``` ## Creating JSON in a Query You can create a `JSON` value directly in a query by casting a `String` literal. ### Example ```sql SELECT '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json ``` Result: ```result ┌─json───────────────────────────────────────────┐ │ {"a":{"b":42},"c":[1,2,3],"d":"Hello, World!"} │ └────────────────────────────────────────────────┘ ``` To cast from `Tuple` or `Map` to `JSON`, you first need to cast the column to a `String` containing the JSON object, and then deserialize it back to the `JSON` type. ## Reading JSON paths as subcolumns The `JSON` type allows you to access individual fields as subcolumns using dot notation. If a path's type was not explicitly specified in the `JSON` type declaration, its subcolumn will have a `Dynamic` type. First, create and populate a data source for the examples: ```tb {% title="test.datasource" %} SCHEMA > `json` JSON(a.b UInt32, SKIP a.e) `json:$` ENGINE "MergeTree" ENGINE_SORTING_KEY "tuple()" ``` ```bash tb push datasources/test.datasource echo '{"a" : {"b" : 42, "g" : 42.42}, "c" : [1, 2, 3], "d" : "2020-01-01"}\n{"f" : "Hello, World!", "d" : "2020-01-02"}\n{"a" : {"b" : 43, "e" : 10, "g" : 43.43}, "c" : [4, 5, 6]}' > test.ndjson tb datasource append test test.ndjson ``` ### Example: Accessing subcolumns with dot notation ```sql SELECT json.a.b, json.a.g, json.c, json.d FROM test ``` Result: ```result ┌─json.a.b─┬─json.a.g─┬─json.c──┬─json.d─────┐ │ 42 │ 42.42 │ [1,2,3] │ 2020-01-01 │ │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-02 │ │ 43 │ 43.43 │ [4,5,6] │ ᴺᵁᴸᴸ │ └──────────┴──────────┴─────────┴────────────┘ ``` {% callout type="tip" %} If you encounter issues with dot notation, you can use the `getSubcolumn(json,'path')` syntax as an alternative. {% /callout %} ### Example: Accessing subcolumns with `getSubcolumn()` ```sql SELECT getSubcolumn(json, 'a.b'), getSubcolumn(json, 'a.g'), getSubcolumn(json, 'c'), getSubcolumn(json, 'd') FROM test ``` Result: ```result ┌─getSubcolumn(json, 'a.b')─┬─getSubcolumn(json, 'a.g')─┬─getSubcolumn(json, 'c')─┬─getSubcolumn(json, 'd')─────┐ │ 42 │ 42.42 │ [1,2,3] │ 2020-01-01 │ │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-02 │ │ 43 │ 43.43 │ [4,5,6] │ ᴺᵁᴸᴸ │ └───────────────────────────┴───────────────────────────┴─────────────────────────┴─────────────────────────────┘ ``` If a requested path is not found in the data, it will be filled with `NULL` values. ### Example: Accessing a non-existent path ```sql SELECT json.non.existing.path FROM test ``` Result: ```result ┌─json.non.existing.path─┐ │ ᴺᵁᴸᴸ │ │ ᴺᵁᴸᴸ │ │ ᴺᵁᴸᴸ │ └────────────────────────┘ ``` You can inspect the data types of the returned subcolumns. ### Example: Checking subcolumn types ```sql SELECT toTypeName(json.a.b), toTypeName(json.a.g), toTypeName(json.c), toTypeName(json.d) FROM test ``` Result: ```result ┌─toTypeName(json.a.b)─┬─toTypeName(json.a.g)─┬─toTypeName(json.c)─┬─toTypeName(json.d)─┐ │ UInt32 │ Dynamic │ Dynamic │ Dynamic │ │ UInt32 │ Dynamic │ Dynamic │ Dynamic │ │ UInt32 │ Dynamic │ Dynamic │ Dynamic │ └──────────────────────┴──────────────────────┴────────────────────┴────────────────────┘ ``` In this example, `json.a.b` has type `UInt32` as specified in the `JSON` type declaration, while other subcolumns are `Dynamic`. You can also read `Dynamic` subcolumns by explicitly specifying a target type using the `.:TypeName` syntax. ### Example: Reading `Dynamic` subcolumns with type hints ```sql SELECT json.a.g.:Float64, dynamicType(json.a.g), json.d.:Date, dynamicType(json.d) FROM test ``` Result: ```result ┌─json.a.g.:`Float64`─┬─dynamicType(json.a.g)─┬─json.d.:`Date`─┬─dynamicType(json.d)─┐ │ 42.42 │ Float64 │ 2020-01-01 │ Date │ │ ᴺᵁᴸᴸ │ None │ 2020-01-02 │ Date │ │ 43.43 │ Float64 │ ᴺᵁᴸᴸ │ None │ └─────────────────────┴───────────────────────┴────────────────┴─────────────────────┘ ``` `Dynamic` subcolumns can be cast to any data type. An exception will be thrown if the internal type within `Dynamic` cannot be cast to the requested type. ### Example: Casting a `Dynamic` subcolumn ```sql SELECT json.a.g::UInt64 as uint FROM test ``` Result: ```result ┌─uint─┐ │ 42 │ │ 0 │ │ 43 │ └──────┘ ``` ## Reading JSON sub-objects as subcolumns The `JSON` type supports reading nested objects as subcolumns of type `JSON` using the special syntax `json.^some.path`. First, create and populate a data source for the examples: ```tb {% title="test.datasource" %} SCHEMA > `json` JSON `json:$` ENGINE "MergeTree" ENGINE_SORTING_KEY "tuple()" ``` ```bash tb push datasources/test.datasource echo '{"a" : {"b" : {"c" : 42, "g" : 42.42}}, "c" : [1, 2, 3], "d" : {"e" : {"f" : {"g" : "Hello, World", "h" : [1, 2, 3]}}}}\n{"f" : "Hello, World!", "d" : {"e" : {"f" : {"h" : [4, 5, 6]}}}}\n{"a" : {"b" : {"c" : 43, "e" : 10, "g" : 43.43}}, "c" : [4, 5, 6]}' > test.ndjson tb datasource append test test.ndjson ``` ### Example ```sql SELECT json.^a.b, json.^d.e.f FROM test ``` Result: ```result ┌─json.^`a`.b───────────────┬─json.^`d`.e.f────────────────────┐ │ {"c":42,"g":42.42} │ {"g":"Hello, World","h":[1,2,3]} │ │ {} │ {"h":[4,5,6]} │ │ {"c":43,"e":10,"g":43.43} │ {} │ └───────────────────────────┴──────────────────────────────────┘ ``` Reading sub-objects as subcolumns can be less efficient, as it may require a near-full scan of the JSON data. ## Handling arrays of JSON objects JSON paths containing an array of objects are parsed as type `Array(JSON)` and inserted into a `Dynamic` column for that path. To read an array of objects, you can extract it from the `Dynamic` column as a subcolumn. First, create and populate a data source for the examples: ```tb {% title="test.datasource" %} SCHEMA > `json` JSON `json:$` ENGINE "MergeTree" ENGINE_SORTING_KEY "tuple()" ``` ```bash tb push datasources/test.datasource echo '{"a" : {"b" : [{"c" : 42, "d" : "Hello", "f" : [[{"g" : 42.42}]], "k" : {"j" : 1000}}, {"c" : 43}, {"e" : [1, 2, 3], "d" : "My", "f" : [[{"g" : 43.43, "h" : "2020-01-01"}]], "k" : {"j" : 2000}}]}}\n{"a" : {"b" : [1, 2, 3]}}\n{"a" : {"b" : [{"c" : 44, "f" : [[{"h" : "2020-01-02"}]]}, {"e" : [4, 5, 6], "d" : "World", "f" : [[{"g" : 44.44}]], "k" : {"j" : 3000}}]}}' > test.ndjson tb datasource append test test.ndjson ``` ### Example: Reading an array of JSON objects ```sql SELECT json.a.b, dynamicType(json.a.b) FROM test ``` Result: ```result ┌─json.a.b──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─dynamicType(json.a.b)────────────────────────────────────┐ │ ['{"c":"42","d":"Hello","f":[[{"g":42.42}]],"k":{"j":"1000"}}','{"c":"43"}','{"d":"My","e":["1","2","3"],"f":[[{"g":43.43,"h":"2020-01-01"}]],"k":{"j":"2000"}}'] │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │ │ [1,2,3] │ Array(Nullable(Int64)) │ │ ['{"c":"44","f":[[{"h":"2020-01-02"}]]}','{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}'] │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────┘ ``` Note that the `max_dynamic_types` and `max_dynamic_paths` parameters for nested `JSON` types are reduced from their default values to prevent an uncontrolled growth in the number of subcolumns for nested arrays of JSON objects. ### Example: Reading subcolumns from a nested `JSON` array ```sql SELECT json.a.b.:`Array(JSON)`.c, json.a.b.:`Array(JSON)`.f, json.a.b.:`Array(JSON)`.d FROM test ``` Result: ```result ┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐ │ [42,43,NULL] │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My'] │ │ [] │ [] │ [] │ │ [44,NULL] │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]] │ [NULL,'World'] │ └───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘ ``` You can simplify the syntax by using `[]` after the path to indicate an array level, avoiding the explicit `Array(JSON)` subcolumn name. ### Example: Simplified syntax for nested `JSON` arrays ```sql SELECT json.a.b[].c, json.a.b[].f, json.a.b[].d FROM test ``` Result: ```result ┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐ │ [42,43,NULL] │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My'] │ │ [] │ [] │ [] │ │ [44,NULL] │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]] │ [NULL,'World'] │ └───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘ ``` The number of `[]` after a path indicates the array level. For instance, `json.path[][]` is transformed to `json.path.:Array(Array(JSON))`. ### Example: Reading subcolumns from deeply nested `JSON` arrays ```sql SELECT json.a.b[].c.:Int64, json.a.b[].f[][].g.:Float64, json.a.b[].f[][].h.:Date FROM test ``` Result: ```result ┌─json.a.b.:`Array(JSON)`.c.:`Int64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.g.:`Float64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.h.:`Date`─┐ │ [42,43,NULL] │ [[[42.42]],[],[[43.43]]] │ [[[NULL]],[],[['2020-01-01']]] │ │ [] │ [] │ [] │ │ [44,NULL] │ [[[NULL]],[[44.44]]] │ [[['2020-01-02']],[[NULL]]] │ └────────────────────────────────────┴──────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘ ``` You can also read sub-object subcolumns from a nested `JSON` array. ### Example: Reading sub-objects from a nested `JSON` array ```sql SELECT json.a.b[].^k FROM test ``` Result: ```result ┌─json.a.b.:`Array(JSON)`.^`k`─────────┐ │ ['{"j":"1000"}','{}','{"j":"2000"}'] │ │ [] │ │ ['{}','{"j":"3000"}'] │ └──────────────────────────────────────┘ ```