Extend comparison support for Union arrays against an opaque array

**Is your feature request related to a problem or challenge? Please describe what you are trying to do.**

#8838 introduces initial comparison support for Union arrays in the `cmp` kernel (union-to-union comparisons). With that in place, we should be able to extend comparison support to allow union arrays to be compared against scalar values and other primitive arrays

The ordering semantics would check if the active variant of each union element can be compared with the provided value. When the active variant's type is compatible with the comparison value, the comparison would proceed normally. I think the part we need to establish is when the types are incompatible, should the result be false or null...

**Additional context**
I'd like to be able to coerce union data types in datafusion. At work, we interact with a lot of JSON and accessing data with `->` returns Union types. And being able to apply a filter like `json_val->'id' = 123` would be nice

Here are some related issues
- https://github.com/apache/arrow-rs/issues/8837
- https://github.com/apache/arrow-rs/issues/8828
- https://github.com/apache/datafusion/issues/18825

Start with the cmp kernel and the union-to-union comparison work introduced in #8838, then review related issues #8837 and #8828. Define how comparisons behave when an active union variant is incompatible with a scalar or primitive array, including whether the result is false or null; done means union arrays support the requested comparison cases with agreed semantics.

Written by the indexing model from the issue text.