[CALCITE-5995] Add cache to the dejsonize functions (JSON_VALUE, JSON_EXISTS, JSON_QUERY)

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.35.0
• Fix Version/s: 1.36.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: xiaogang zhou
• Created: 11/Sep/23 12:46
• Updated: 10/Nov/23 11:34
• Resolved: 21/Sep/23 07:56

Description

I used the json_value function to parse json values. And I found calcite's json_value function does not cache the dejsonized objects, which could cause some performance issue in situation below as the dejsonize function being called repeatedly unnecessarily.  

 

select 
json_value(A, 'xxx'),
json_value(A, 'yyy'),
json_value(A, 'zzz'),...
from some_table;

 

 

As project like flink uses the json_value to codegen it's own json_value function, I think this could cause a bad performance for users. So I suggest to introduce a cache in  

 

org.apache.calcite.runtime.JsonFunctions#dejsonize

 

and the solution is very common in projects like hive

https://github.com/apache/hive/blob/storage-branch-2.3/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDTFJSONTuple.java

 

and of course, this feature can be turned on only some certain config is setted. And if this is acceptable, I think I can take the ticket. thx

 

Issue Links

• is related to: CALCITE-5914 Cache compiled regular expressions in SQL function runtime
• links to: GitHub Pull Request #3432
• links to: https://github.com/apache/calcite/pull/3432