Skip to content
Advertisement

What’s the proper index for querying structures in arrays in Postgres jsonb?

I’m experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4:

I’m executing queries like:

How would I create an index on that data for queries like the above to utilize? Does this sound reasonable design for a few million rows that each contain ~10 events in that column?

Worth noting that it seems I’m still getting sequential scans with:

which I’m guessing is because the first thing I’m doing in the query is converting data to json array elements.

Advertisement

Answer

First of all, you cannot access JSON array values like that. For a given json value:

A valid test against the first array element would be:

But you probably don’t want to limit your search to the first element of the array. With the jsonb data type in Postgres 9.4 you have additional operators and index support. To index elements of an array you need a GIN index.

The built-in operator classes for GIN indexes do not support “greater than” or “less than” operators > >= < <=. This is true for jsonb as well, where you can choose between two operator classes. The manual:

(jsonb_ops being the default.) You can cover the equality test, but neither of those operators covers your requirement for >= comparison. You would need a btree index.

Basic solution

To support the equality check with an index:

This might be good enough if the filter is selective enough.
Assuming end_time >= start_time, so we don’t need two checks. Checking only end_time is cheaper and equivalent:

Utilizing an implicit JOIN LATERAL. Details (last chapter):

Careful with the different data types! What you have in the JSON value looks like timestamp [without time zone], while your predicates use timestamp with time zone literals. The timestamp value is interpreted according to the current time zone setting, while the given timestamptz literals must be cast to timestamptz explicitly or the time zone would be ignored! Above query should work as desired. Detailed explanation:

More explanation for jsonb_array_elements():

Advanced solution

If the above is not good enough, I would consider a MATERIALIZED VIEW that stores relevant attributes in normalized form. This allows plain btree indexes.

The code assumes that your JSON values have a consistent format as displayed in the question.

Setup:

Related answer for jsonb_populate_recordset():

Also including location_id to allow index-only scans. (See manual page and Postgres Wiki.)

Query:

Or, if you need full rows from the underlying locations table:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement