CODEWITHSUNDEEP
sqlpostgresqljsonb
Matt
Matt

Reputation: 303

How to query an array of values within a JSONB field dictionary?

I have a jsonb column that contains a dictionary which has a key that points to an array of string values. I need to query against that array.

The table (called "things") looks like this:

------------------
| my_column      |
|----------------|
| { "a": ["X"] } |
------------------

I need to write two queries:

Does the array contain value "X"?

Does the array not contain value "X"?

my_column has a non-null constraint, but it can contain an empty dictionary. The dictionary can also contain other key/value pairs.

The first query was easy:

SELECT * FROM things
  WHERE my_column -> 'a' ? 'X';`

The second one is proving to be more challenging. I started there with:

SELECT * FROM things
  WHERE NOT my_column -> 'a' ? 'X';

... but that excluded all the records that had dictionaries that didn't include key 'a'. So I modified it like so:

SELECT * FROM things
  WHERE my_column -> 'a' IS NULL OR NOT
        my_column -> 'a' ? 'X';

This works, but is there a better way? Also, is it possible to index this query, and if so, how?

Upvotes: 1

Views: 1604

Answers (1)

jmelesky
jmelesky

Reputation: 3970

I'm not sure if there's any better way -- that honestly looks pretty straightforward to me.

As for indexing, there are a couple things you can do.

First, you can index the jsonb field. Putting a GIN index on that field should help with any use of "exists"-type operators (like ?).

If that isn't the solution you want for whatever reason, Postgres supports functional and partial indexes. A functional index might look like:

CREATE INDEX ON things ( my_column -> 'a' );

(note: It looks like postgres is having trouble with that syntax, which might be a bug. The concept holds, though.)

A partial index would get even more specific, and could even look like:

CREATE INDEX ON things (my_column)
  WHERE my_column -> 'a' IS NULL OR NOT
    my_column -> 'a' ? 'X';

Obviously, that won't help for more general queries.

At a guess, indexing the whole column with a GIN index is the right way to go, or at least the right place to start.

Upvotes: 1

Related Questions