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 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
----------------------------------------------------------------------------------------
Index Only Scan using json_doc_doc_idx on json_doc (cost=0.13..12.16 rows=1 width=32)
Filter: (doc @> '{"name": "John"}'::jsonb)
(2 rows)
A JSON document can be converted into a set using the json_to_record() function. This is quite useful since we can sort and filter data as in normal tables. In addition to that, one can aggregate data from several rows and construct an array of JSON objects via the jsosn_agg() function.