Company or project name

ClickHouse

Use case

The SQL/JSON and JSONExtract family of functions were implemented for String:

SELECT '{"x": 1}' AS json, JSON_VALUE(json, '$.x');
SELECT '{"x": 1}' AS json, JSONExtractUInt(json, 'x');

These functions are mostly redundant for the JSON data type, as it supports the syntax for querying subcolumns directly:

SELECT '{"x": 1}'::JSON AS json, json.x

However, it's natural to make the extraction functions to work on the JSON data type:

SELECT '{"x": 1}'::JSON AS json, JSON_VALUE(json, '$.x');

Code: 43. DB::Exception: JSONPath functions require first argument to be JSON of string, illegal type: JSON: In scope SELECT CAST('{"x": 1}', 'JSON') AS json, JSON_VALUE(json, '$.x'). (ILLEGAL_TYPE_OF_ARGUMENT)

SELECT '{"x": 1}'::JSON AS json, JSONExtractUInt(json, 'x');

Code: 43. DB::Exception: The first argument of function JSONExtractUInt should be a string containing JSON, illegal type: JSON: In scope SELECT CAST('{"x": 1}', 'JSON') AS json, JSONExtractUInt(json, 'x'). (ILLEGAL_TYPE_OF_ARGUMENT)

Because sometimes agents try to do it this way:

And because JSON_EXTRACT and JSON_VALUE have a broader set of expressions supported.

Describe the solution you'd like

Simple invocations of these functions should be converted to querying subcolumns and casting.

Invocations with complex expressions can be implemented: - in a worse, generic way: with casting JSON to string and processing it as usual; - in a better way with custom logic inside the functions.

Describe alternatives you've considered

No response

Additional context

Part of #68428