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

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?

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));

StackExchange Database Administrators Q#117139, answer score: 7