A JSON function that knows the type: the request that needed a different design

I asked for a function that was impossible, and the reason it was impossible turned out to be the most useful thing in the thread. Issue #8327 wanted a function called JSON that looks at a value in a JSON string, works out its type, and returns it typed. The maintainer closed it in 2021 with a two-line explanation of why no function can do that. The goal behind the request was met years later, by changing what the column is instead of what the function returns.

Why 2019 felt like a problem

JSON is dynamic and SQL columns are not. In 2019 reading a field meant naming its type in the call, which meant knowing it before writing the query:

WITH '{"i": 1, "f": 1.2}' AS json
SELECT JSONExtract(json, 'i', 'Int8') AS i, JSONExtract(json, 'f', 'Float32') AS f;

The alternatives each gave something up. JSONExtractRaw avoids guessing but returns a String every time. JSONType tells you the type of a value (Int64 for i, Double for f, Null for a path that is not there), so the obvious plan was two steps: ask for the type, then build a second query around the answer. I wanted to fold the two steps into one, and the natural attempt was to pass the output of JSONType as the type argument of JSONExtract. On the 19.17 server I was using that crashed with std::bad_typeid.

The wrong-type behaviour is also easy to miss, because it does not fail. On 26.4:

WITH '{"s": "abc", "n": 300, "t": "12", "f": 1.2}' AS json
SELECT JSONExtract(json, 's', 'Int8') AS s,       -- 0, a string that is not a number
       JSONExtract(json, 'n', 'Int8') AS n,       -- 0, 300 does not fit
       JSONExtract(json, 't', 'Int8') AS t,       -- 12, the string "12" is converted
       JSONExtract(json, 'f', 'Int8') AS f,       -- 1, the float is converted
       JSONExtract(json, 'zz', 'Int8') AS missing; -- 0, no such key

s: 0   n: 0   t: 12   f: 1   missing: 0

A guess that is wrong yields a default, and a default is indistinguishable from a real zero. That is the cost of spelling out the type, and part of why “just infer it” looked so attractive. The motivation I gave was bigger than convenience: send any JSON structure into ClickHouse and query any field without knowing the schema in advance, the document-store use case.

The maintainer’s reasoning

The request sat for two years. On 2021-12-07, alexey-milovidov answered:

“This is not easy to implement. In our query language, the function return type is determined by the types of its arguments and the values of the constant arguments. It cannot depend on the values of non-constant arguments.”

His example was SELECT JSON(json, 'k') FROM VALUES('json String', ('{"k":1}'), ('{"k":"hello"}')): “json is not constant and the type cannot be inferred.” The issue was closed in the same minute (it now carries the not planned label).

This is the design boundary in one sentence. ClickHouse resolves every expression’s type at analysis time, before a single row is read, from the argument types and any constants; the pipeline is then built from typed columns. JSONExtract(json, 'i', 'Int8') works because 'Int8' is a constant: the function’s result type is a function of that constant. JSON(json, 'k') would need the result type to depend on data, which means the type of the column is not known until execution, and every operator downstream (+, GROUP BY, ORDER BY, a JOIN key) would have to be planned without it. A column in ClickHouse has one type, and the planner relies on it.

A minute later he added that the error message had been improved. Today:

WITH '{"i": 1, "f": 1.2}' AS json, JSONType(json, 'i') AS type
SELECT JSONExtract(json, 'i', type);

Code: 44. DB::Exception: The last argument of function JSONExtract should be a constant string specifying
the return data type, illegal value: type ... (ILLEGAL_COLUMN)

That is the same boundary stated back to the user. The crash itself was fixed earlier, in PR #12400 (merged 2020-07-11), which says it fixes #11885 and is “related to” this issue. There is still no JSON function: SELECT JSON('x') gives UNKNOWN_FUNCTION.

What people did instead

Before the new types, the workable patterns were: know the schema and write the types; extract raw strings and convert in SQL; or ask JSONType in a first query and generate the second query from the client. All three move the type decision out of the function and into whoever writes the SQL. None of them is bad, and for stable schemas they are still the right choice. What they cannot do is the thing I asked for: one query over documents whose field types differ from row to row.

The design that answered it

The route ClickHouse took was to stop asking the function to be dynamic and to make a type that is. It came in three steps, each a pull request you can read:

• Variant (#58047, merged 2024-01-29): a discriminated union, Variant(Int64, String). The set of alternatives is fixed in the type, so the planner still sees one static type, while each value knows which alternative it holds.
• Dynamic (#63058, merged 2024-05-23): a Variant whose alternatives are not declared in advance.
• The new JSON type (#66444, merged 2024-08-16): every path in a document is stored as its own subcolumn, typed per value. The 25.3 release announced it as production-ready.

The design proposal, #54864, lists what was wrong with the older experimental JSON, and it reads like an answer to my issue. Among the drawbacks: “eager type unification: for example, if a field was an integer in JSON, and you inserted a single object where it is a string, the table will be poisoned forever; having variant data type and the support for explicit type specification should solve this problem”. Inferring one type per field up front, which is what a JSON(...) function would have done, is the failure mode. The new design keeps every value’s own type instead.

The maintainer’s own counterexample now runs, on 26.4:

SELECT j.k AS k, dynamicType(j.k) AS k_type, toTypeName(j.k) AS static_type
FROM (SELECT json::JSON AS j FROM VALUES('json String', ('{"k":1}'), ('{"k":"hello"}')));

1      Int64   Dynamic
hello  String  Dynamic

The type of the column is Dynamic, one static type, which is what the analyzer needs. What differs per row is the type of the value inside it, and dynamicType reads that. The “what type is this value?” question I asked in 2019 is answered per value instead of per function call.

A document with several shapes, in a stored table:

CREATE TABLE docs (j JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO docs VALUES ('{"k":1}'), ('{"k":"hello"}'), ('{"k":[1,2]}'), ('{}');
SELECT dynamicType(j.k) AS t, count() FROM docs GROUP BY t ORDER BY t;
SELECT arrayJoin(JSONAllPathsWithTypes(j)) FROM docs;

Array(Nullable(Int64))  1
Int64                   1
None                    1
String                  1
('k','Int64')   ('k','String')   ('k','Array(Nullable(Int64))')

A document with no k has the dynamic type None. The same type is available on a plain string through JSONExtract(json, 'k', 'Dynamic'), which returns 1.2 as Float64 for the 2019 example document. For the practical side of storing and querying see working with JSON.

What it costs: the contract moves to runtime

None of this breaks the rule the maintainer stated; it relocates the problem. If the static type is Dynamic, the analyzer cannot check operations on it, and some failures are data-dependent:

-- all integers: fine
SELECT j.k + 1 FROM (SELECT json::JSON AS j FROM VALUES('json String', ('{"k":1}'), ('{"k":5}')));
-- 2, 6

-- one string in the column: the whole query fails
SELECT j.k + 1 FROM (SELECT json::JSON AS j FROM VALUES('json String', ('{"k":1}'), ('{"k":"7"}')));
-- Code: 43. Illegal types String and UInt8 of arguments of function plus ... (ILLEGAL_TYPE_OF_ARGUMENT)

The same query that worked on Monday’s data throws on Tuesday’s, because a document arrived with a string. That is exactly the poisoning described in #54864, only now the cost is paid at query time rather than at insert time. The way out is to state the type you expect, in the query or in the column:

SELECT j.k::Int64 AS v FROM (SELECT json::JSON AS j FROM VALUES('json String', ('{"k":1}'), ('{"k":"7"}')));  -- 1, 7
SELECT j.k.:Int64 AS as_int, j.k.:String AS as_str
FROM (SELECT json::JSON AS j FROM VALUES('json String', ('{"k":1}'), ('{"k":"hello"}')));
-- (1, NULL) then (NULL, 'hello')
CREATE TABLE docs (j JSON(k Int64)) ENGINE = MergeTree ORDER BY tuple();   -- a typed path hint

::Int64 converts, and throws on 'hello'; .:Int64 reads the value only where its stored type is Int64 and returns NULL elsewhere, so it filters silently; and JSON(k Int64) makes k a real Int64 column inside the JSON, rejecting an insert that cannot parse (Cannot insert data into JSON column: cannot parse Int64 value here: "hello"). Each is a way to put the type back, for exactly the paths where you care.

What it teaches

• Type is resolved before data is read. This is not a limitation to be fixed; the analyzer and the planner build everything downstream on it. A request that needs “the type depends on the data” cannot be a function.
• Put the dynamism in the type system, not the function. Variant and Dynamic are the same trick as Nullable: the column has one static type, and a per-value tag lives inside it. The cost is paid in storage and in errors that appear later.
• The honest answer to “infer it” is “decide where the type lives”. In the query (::), in the column (JSON(k Int64)), or in each value (Dynamic). A function cannot choose for you.

I count this as superseded rather than implemented. Nobody added the function I asked for, the explanation of why it could not exist was correct, and the underlying goal is met by a better design.