Complex Index for Postgres JSONB
PostgreSql takes on no-sql approach as hybrid using JSONB data type is quite impressive, it provides the versatility of RDBMS and nosql. I will not discuss further on it, for more information you can read at: https://www.postgresql.org/docs/9.5/functions-json.html. This page focus on one difficulty that developer often encounter: now that I have a complex json inside the jsonb column, how do we index it? and how do we index the sub-object inside json array ? Suppose we have Postgres table name sample with: CREATE TABLE IF NOT EXISTS public.sample ( id integer NOT NULL DEFAULT nextval('sample_id_seq'::regclass), name character varying(100) COLLATE pg_catalog."default" NOT NULL, more_info jsonb, CONSTRAINT sample_pkey PRIMARY KEY (id) ) And the following is the structure of json data inside the more_info column: '{ "info":{ "address":[ { "addressType":"PPOR