This approach works for some use cases, but it has limitations and problems (see comments). Let me suggest another approach:

• Support JSONExtract* functions over JSON type only with constant keys arguments with type String. Supporting non-constant keys and indexes is not trivial, will be complex to implement and will work very slow. And in my opinion, the use cases with indexes and non-constant keys are not common and it's totally ok to skip them for now.
• With constant keys we can form a path that we need to extract, here we can use subcolumns to do it, but as I described in one of the comments, we can't just use simple subcolum with dots, we need a combination of a literal subcolumn and subobject subcolumn to be able to extract both literals and nested objects by specified path. Similar how it's done in tupleElement function (see FunctionTupleElement::getObjectElement).
• After extracting and combining subcolumns we can use accurateCastOrDefault to convert the values to the destination data type (for JSONExtractRaw we need to do something else, for example, formatting the value as JSON string using ISerialization::serializeTextJSON).

Great work! Just 2 final minor comments and we can merge it