Sorry to beat a dead horse here, but I'm one of those weirdos that gets a
lot of use out of Calcite's JSON operators.
Calcite's JSON implementation is broken for queries that have more than one
depth of JSON object/array calls.
The reason is because the operator calls "jsonize()", which parses the
(presumably) JVM object as a JSON string
This works for something like Map<String, String>, but if you have
Map<String, Map<String, String>>, what happens is this:
JSON_OBJECT(
foo: 1,
bar: JSON_OBJECT(
qux: 2
)
)
The parse happens inside-out, so first we get the innermost object parsed,
which gives:
{ "qux": 2 }
But -- as a string! This is important!
Now, when we parse the next object, we get this:
{ "foo": 1, "bar": \"{ \"qux\": 2 }\" }
This is because the object with "qux" isn't an object, but a string value
Which to be valid JSON, needs to have its quotes and braces escaped
Definitely not what you want, and the value isn't usable =(
Since there is no state/context/stack (that I can tell) when the parse
function is called,
how might it be possible to write something to the effect of:
"Analyze the query, and if the number of JSON operations is greater than
one,
only call 'jsonize()' on the outer-most parse/object."
https://github.com/apache/calcite/blob/8d21c3f2f0b75d788e70bbeea9746695f2fde552/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java#L1886-L1891
https://github.com/apache/calcite/blob/4bc916619fd286b2c0cc4d5c653c96a68801d74e/core/src/main/java/org/apache/calcite/runtime/JsonFunctions.java#L93-L95
