Hi I'm looking for some pointers on how to address this requirement:
I have records that have an array field, this array could have 20 entries at the very outside: e.g ['GROUP1', 'GROUP2', 'GROUP3'] A user can be a member of many groups again, comparatively small numbers, think 20 or so e.g. User1 in GROUP1, GROUP3 User2 in GROUP3, GROUP4, GROUP5 User3 in GROUP5, GROUP6, GROUP7 How would you filter records using the intersection of the user's groups and the array field. 1) I could maybe use a JOIN if Phoenix allows a JOIN on literal array e.g. SELECT groupArrayField from blah JOIN VALUES(...) But it looks like this, and the UNNEST keyword are not implemented. (I gather UNNEST is calcite only) 2) Create a custom UDF that takes two arrays and returns true if they have any element in common I have found the doco on UDFs a bit sparse and I'm a little hesitant to go down this route unless it's the only option 3) I can't think of 3. Has anyone tried something like this? Cheers in advance S
