Database Reference
In-Depth Information
Secondary indexes on map columns
We just created a secondary index on a set column, which allows us to retrieve rows that
contain a given value in that set. Secondary indexes on list columns work in precisely the
same way. However, map columns have both keys and values, so it's not immediately obvi-
ous what data populates the index.
By default, a secondary index on a map will index the
values
in the map; the
CONTAINS
keyword similarly filters for map values. However, it is possible to create an index on map
keys as well, and then select rows with a given key in a map column.
Let's say we'd like to look up users for whom we have an associated Twitter identity. Recall
that in the
social_identities
column of the
users
table, the map keys are the
names of social networks, and the map values are user IDs from those networks. So, we'll
create an index on the keys of the
social_identities
map:
CREATE INDEX ON "users" (KEYS("social_identities"));
Note the use of the
KEYS
operator to indicate that we are creating an index on the map's
keys; had we omitted this operator, we would have created an index on the map's values.
Now we can look up all users who have an associated Twitter identity, using the
CONTAINS KEY
operator:
SELECT "username", "social_identities"
FROM users
WHERE "social_identities" CONTAINS KEY 'twitter';
The
WHERE…CONTAINS KEY
clause works exactly the same as
WHERE…CONTAINS
, ex-
cept that we are looking up rows that contain the given key in the specified map column. In
this case, we get back the two rows with the key
twitter
in the
social_identities
map:
