Sadly, the ARRAY functions don’t work in the way that people are expecting them to work in Airtable.
And that leaves us with unresolvable problems in Airtable.
The ARRAY functions work under a very limited set of circumstances, but it says right on the formula field reference page that they are supposed to work with lookup fields, rollup fields, and linked fields as an input.
This just isn’t true, as you can see from this thread here.
ARRAYJOIN, ARRAYFLATTEN, ARRAYCOMPACT, and ARRAYUNIQUE simply do not work under many common scenarios — even when feeding them the “approved inputs” of lookup fields or rollup fields.
The ARRAY functions seem to only work when you have one input only, but not multiple inputs — even though the examples on the formula field reference page all show multiple inputs.
For example, this problem remains unresolved, because the ARRAY functions don’t work as documented.
Airtable Team: Please revisit these ARRAY functions and make them work under all circumstances.
(And instead of changing the Airtable documentation to downgrade the functionality of the ARRAY functions, it would be better to FIX THE ARRAY FUNCTIONS so that they work as expected.)