[CALCITE-2786] Add order by clause support for JSON_ARRAYAGG

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.19.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Hongze Zhang
• Reporter: Hongze Zhang
• Created: 16/Jan/19 06:23
• Updated: 25/Mar/19 00:11
• Resolved: 26/Feb/19 16:16

Description

Both Oracle[1] and SQL:2016[2] supports or introduces an optional order by clause in JSON_ARRAYAGG, the feature can be implemented since Calcite already supports sorted aggregation.

AFAICS, JSON_ARRAYAGG's order by clause has no difference with the definition of WITHIN GROUP's, so I think it might be feasible to do sort by expanding JSON_ARRAYAGG(... ORDER BY ...) to JSON_ARRAYAGG(...) WITHIN GROUP (ORDER BY...) during SQL parsing.

[1] https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_ARRAYAGG.html#GUID-6D56077D-78DE-4CC0-9498-225DDC42E054
[2] http://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip

Issue Links

• links to: GitHub Pull Request #1061