Let's say we'd like to get a list of all of the status updates that alice has starred; we can display this as part of her user profile. In order to do this, we'd like to be able to look up all the rows in the user_status_updates table, where the starred_by_users column contains the value alice. This is similar to the use case for a secondary index that we explored in the Using secondary indexes to avoid denormalization section of Chapter 5, Establishing Relationships, except that in this case, we'd like to be able to perform a lookup based on a single value within a collection column.
Fortunately, it is entirely valid to put a secondary index on a collection column. The syntax for this is identical to putting an index on any other column:
CREATE INDEX ON "user_status_updates" ("starred_by_users");
So far so good.
Now, we'll introduce the CONTAINS operator, which can be used to look up rows by a value in a collection column:
SELECT * FROM "user_status_updates...