That sounds very reasonable to me I don't know the Calcite codebase as well as other folks -- and certainly not nearly as well as you Where would be the place to put such a thing/the overall approach?
On Fri, Mar 18, 2022 at 1:53 PM Julian Hyde <[email protected]> wrote: > I think you’re proposing making the JSON_ functions smarter at runtime. My > general philosophy is to have the smarts at prepare time and make the > runtime operators dumb. I think that philosophy can be applied here. Some > extra logic would kick in when preparing a query that has JSON_ functions, > and that logic would fold together multiple nested JSON_ functions. Perhaps > we need to add a new (internal) JSON_ function that can do all of the steps. > > Julian > > > > On Mar 18, 2022, at 8:50 AM, Gavin Ray <[email protected]> wrote: > > > > 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 > >
