HQL syntax sugar for array operations

[beikov]

Since we implemented array functions now as discussed via #7392, #7462 and #7480, we were thinking about making it easier for people to work with arrays.

One such simplification was by implementing syntax sugar for concatenating arrays with the || (double pipe) operator.

@gavinking brought up some other possible simplifications. I'll list them here and we can discuss these simplifications:

• Array construction short-hand by using brackets [elem1, elem2, ...]
• Support arrays on the RHS of the in predicate
• Access array element by index via bracket index syntax i.e. array[1]
• Overload/add functions without the array_ prefix i.e. length() for arrays

Some proposals which I don't think will fly are the following:

• Overload concat. I don't think it's worth it, since we have support for the || operator already. I think this would only complicate the rendering/construction in the concat function, but we can certainly try this.
• Setting elements of an array via bracket index syntax. How should that work or look i.e. how to differentiate it from a comparison?

Here are additional some ideas I have for simplification:

• Introduce overlaps and contains predicates i.e. where e.array contains 'abc'. These can be used for range type support and Hibernate Spatial as well.
• Add slicing syntax like PostgreSQL i.e. array_slice(array, 1, 10) => array[1:10]

Here is a list of the functions we have or plan to have:

Function	Syntax sugar
array()	Short-hand bracket [elem1, elem2]
array_agg()	none
array_position()	Overload position()
array_positions()	none
array_length()	Overload length()
array_concat()	Double-pipe operator
array_prepend()	Double-pipe operator
array_append()	Double-pipe operator
array_contains()	Custom contains predicate
array_contains_nullable()	none
array_overlaps()	Custom overlaps predicate
array_overlaps_nullable()	none
array_get()	Bracket index syntax array[1]
array_set()	none
array_remove()	none
array_remove_index()	none
array_slice()	PostgreSQL slicing syntax array[1:10]
array_replace()	none
array_trim()	none
array_fill()	none
array_to_string()	none

Thoughts or concerns with the presented syntax sugar? Ideas about further syntax sugar? Let me know in the comments!

[gavinking]

How should that work or look i.e. how to differentiate it from a comparison?

To be clear I have not thought it through, but I was thinking of something like this:

update Entity set itsArray[0] = :theElement

array_to_string()

I think it would be much nicer if this was handled by the existing str() function. (Or even by cast(array as String).)

[beikov]

To be clear I have not thought it through, but I was thinking of something like this:

I certainly would like to support this syntax in HQL/Criteria, but know that the array_set function is also "useful" if you want to produce a new array with an element set a specified index, so not just for actually updating an array column.

I think it would be much nicer if this was handled by the existing str() function. (Or even by cast(array as String).)

Steve actually said something similar, but the function also takes a separator argument 😄

[gavinking]

Steve actually said something similar, but the function also takes a separator argument 😄

Well, sure, but we have already taken the plunge on allowing functions to be overloaded, so I guess that's fine.

[beikov]

I created https://hibernate.atlassian.net/browse/HHH-17375 for this work. Further comments/questions/suggestions can happen on the Jira issue.