Handling array fields in Vertica
I've been struggling in figuring out how can we store a field with multiple values. I thought of below three approaches
1. creating a hybrid flex table with storing array type fields as virtual. Vertica implicitly stores array types as a map so I can use MAPCONTAINSVALUE() while looking up for a specific field. But I found MAPCONTAINSVALUE() to be extremely slow and not sure how I can materialize such fields if it's possible.
2. Keep such attributes in a separate table that's how SQL maintains the one-many relationship.
3. Storing a value as CSV but while querying we need to use like operator instead of =. e.g. where column like '%somevalue%'. This looks bad too.
What do you think is the best approach to handle array type fields in Vertica?
Can we materialize array type fields from the flex table?
Any downside of using normalized tables and joins?