Learning PostgreSQL 11 - Third Edition

Indexing a JSON data type

JSONB documents can be indexed using the GIN index, and the index can be used for the following operators:

• @>: Does the left JSON value contain the right value?
• ?: Does the key string exist in the JSON doc?
• ?&: Do any of the elements in the text array exist in the JSON doc?
• ?|: Do all the keys/elements in the text array exist in the JSON doc?

To see the effect of indexing on the json_doc table, let's create an index and disable the sequential scan, as follows:

CREATE INDEX ON json_doc(doc);SET enable_seqscan = off;

We can use the following code to test the index function:

car_portal=# EXPLAIN SELECT * FROM json_doc WHERE doc @> '{"name":"John"}';                                       QUERY PLAN ---------------------------------------------------------------------------------------- ...