[BUG] json function return type is undefined

[penghuo]

What is the bug?

• json The function's return type is undefined, which presents two issues:
  • Undefined type is not a valid PPL data type.
  • It is inconsistent with the Spark-PPL JSON function, which returns a string.

How can one reproduce the bug?
https://github.com/Bit-Quill/opensearch-project-sql/blob/main/integ-test/src/test/java/org/opensearch/sql/ppl/JsonFunctionsIT.java#L73

What is the expected behavior?
align with Spark PPL, Return type is String

[LantaoJin]

Should we add a new datatype for JSON in SQL plugin? I found we have a merged PR #3243 which updated CAST function to support cast(string as JSON). Somewhat, It seems treated JSON as a datatype which cause this failure. I can workaround it, but we'd better to discuss it. Maybe VARIANT is a better option than String for Json. https://www.databricks.com/blog/introducing-open-variant-data-type-delta-lake-and-apache-spark

[LantaoJin]

In v3, all json function return type is string. close