Repository: ClickHouse/ClickHouse
File: src/Functions/FunctionsJSON.cpp (C++)

Code before:
                }
                return result_col;
            }
            else
            {
                /// Cast merged column to the function's return type.
                /// Use accurateCastOrNull to get NULL for failed casts then
                /// replace NULLs with defaults (JSONExtract functions return non-nullable types).
                auto nullable_type = makeNullable(result_type);
                auto casted = castColumnAccurateOrNull({merged, merged_type, ""}, nullable_type);

                if (const auto * nullable_col = typeid_cast<const ColumnNullable *>(casted.get()))
                {
                    const auto & null_map = nullable_col->getNullMapData();
                    const auto & nested = nullable_col->getNestedColumn();
                    auto result_col = result_type->createColumn();
                    for (size_t i = 0; i < input_rows_count; ++i)
                    {
                        if (null_map[i])
                            result_col->insertDefault();
                        else
                            result_col->insertFrom(nested, i);
                    }
                    return result_col;
                }
                return casted;
            }
        }
    };

private:
    /// Represents a move of a JSON iterator described by a single argument passed to a JSON function.
    /// For example, the call JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1)
    /// contains two moves: {MoveType::ConstKey, "b"} and {MoveType::ConstIndex, 1}.
    /// Keys and indices can be nonconst, in this case they are calculated for each row.
    enum class MoveType : uint8_t
    {
        Key,
        Index,
        ConstKey,
        ConstIndex,
    };

    struct Move
    {
        explicit Move(MoveType type_, size_t index_ = 0) : type(type_), index(index_) {}
        Move(MoveType type_, const String & key_) : type(type_), key(key_) {}
        MoveType type;
        size_t index = 0;
        String key;
    };

Review comment (refactor):
A few notes:
1) The result type of `JSONExtract` can be `Nullable` : `JSONExtract('{"a" : "hello"}', 'a', 'Nullable(String)')`, in this case this code will do unnecessary data copying.
2) The type of `merged` here should be always `Dynamic`. And `castColumnAccurateOrNull` from `Dynamic` to some `Nullable` type will always insert default value on nulls into the nested column inside Nullable. So we can avoid data copying and just return `removeNullable(casted)`.
3) `castColumnAccurateOrNull` will make result type `Nullable` automatically, no need to use `makeNullable(result_type)`

So we can replace this code to:
```c++
auto casted = castColumnAccurateOrNull({merged, merged_type, ""}, result_type);
return result_type->isNullable() ? casted : removeNullable(casted);
```

Also we have a problem with `castColumnAccurateOrNull` that it doesn't work for data types that cannot be inside `Nullable` (like `Array`):
```sql
:) select JSONExtract('{"a" : "hello"}'::JSON, 'a', 'Array(UInt32)')

SELECT JSONExtract(CAST('{"a" : "hello"}', 'JSON'), 'a', 'Array(UInt32)')

Query id: 4dc670fc-ffcb-481d-8e1b-f1a8e890c7a9


Elapsed: 0.026 sec.

Received exception:
Code: 43. DB::Exception: Nested type Array(UInt32) cannot be inside Nullable type: In scope SELECT JSONExtract(CAST('{"a" : "hello"}', 'JSON'), 'a', 'Array(UInt32)'). (ILLEGAL_TYPE_OF_ARGUMENT)
```

Unfortunately we cannot avoid it right now, it's a known issue and we need to fix it separately: https://github.com/ClickHouse/ClickHouse/issues/61982. We can keep it for now.

Code after:

private:
    /// Represents a move of a JSON iterator described by a single argument passed to a JSON function.
    /// For example, the call JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1)
    /// contains two moves: {MoveType::ConstKey, "b"} and {MoveType::ConstIndex, 1}.
    /// Keys and indices can be nonconst, in this case they are calculated for each row.
    enum class MoveType : uint8_t
    {
        Key,
        Index,
        ConstKey,
        ConstIndex,
    };

    struct Move
    {
        explicit Move(MoveType type_, size_t index_ = 0) : type(type_), index(index_) {}
        Move(MoveType type_, const String & key_) : type(type_), key(key_) {}
        MoveType type;
        size_t index = 0;
        String key;
    };

    static std::vector<FunctionJSONHelpers::Move> prepareMoves(
        const char * function_name,
        const ColumnsWithTypeAndName & columns,
        size_t first_index_argument,
        size_t num_index_arguments)
    {
        std::vector<Move> moves;
        moves.reserve(num_index_arguments);
        for (const auto i : collections::range(first_index_argument, first_index_argument + num_index_arguments))
        {
            const auto & column = columns[i];
            if (!isString(column.type) && !isNativeInteger(column.type))
                throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
                                "The argument {} of function {} should be a string specifying key "
                                "or an integer specifying index, illegal type: {}",
                                std::to_string(i + 1), String(function_name), column.type->getName());

            if (column.column && isColumnConst(*column.column))
            {
                const auto & column_const = assert_cast<const ColumnConst &>(*column.column);
                if (isString(column.type))
                    moves.emplace_back(MoveType::ConstKey, column_const.getValue<String>());
                else
                    moves.emplace_back(MoveType::ConstIndex, column_const.getInt(0));
            }
            else
            {
                if (isString(column.type))