5

Can we create an index for a key/value of a JSONB data type?

For example, for these schema:

CREATE TABLE x (
  id BIGSERIAL,
  data JSONB
);
CREATE TABLE y (
  id BIGSERIAL,
  data JSONB
);

The slow query:

SELECT *
FROM x
  LEFT JOIN y
    ON (y.data->>'x_id')::BIGINT = x.id

How to create an index for y.data->>'x_id' that can be used for that kind of query?

2

1 Answer 1

7

I suggest an expression index on the value of the key 'x_id', cast to bigint - plain (default) B-tree, not GIN. One pitfall here: the short notation for type casts requires an additional set of parentheses to make the syntax work for index creation:

CREATE INDEX y_data_xid_idx ON y (((y.data->>'x_id')::bigint));

Alternatively, use the explicit form (to the same effect):

CREATE INDEX y_data_xid_idx ON y (cast(y.data->>'x_id' AS bigint));
1
  • 5
    Is it possible to add such an index to a JSONB field with dynamic keys?
    – Tarlen
    Oct 26, 2016 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.