Starting from version 9.4 PostgreSQL natively supports the JSON datatype and it is possible to create an index on it. In the following example, we will now see how to perform an indexed search on a JSON type:
pgbench=# create table test_json( id serial, field_json jsonb); CREATE TABLE pgbench=# INSERT INTO test_json (field_json) VALUES ( '{ "customer": "Billy", "items": {"product": "Apple","qty": 2}}' ), ( '{ "customer": "Molly", "items": {"product": "Orange","qty": 1}}' ), ( '{ "customer": "Billy", "items": {"product": "Orange","qty": 10}}' ), ( '{ "customer": "Frank", "items": {"product": "Potato","qty": 2}}' ); INSERT 0 3 create index field_json_idx on test_json using gin (field_json jsonb_ops); pgbench=# select ...
