This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous two parts, I shared basic about JSON data types, operators and functions of PostgreSQL 9.4.
In this post, I am showing to create a GIN INDEX on the jsonb data type of PostgreSQL 9.4.
If we are thinking to store large JSON document in PostgreSQL, we should also apply proper indexing on it.
The GIN indexing is very useful to search key and value pairs from the large jsonb documents.
The GIN index supports queries with this all @>, ?, ?& and ? different operators.
Below is a small demonstration on GIN index:
First, create a sample table with jsonb data type and JSON data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
CREATE TABLE tbl_TestJSONData ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSONB ); INSERT INTO tbl_TestJSONData VALUES ( 1 ,'Sales' ,' { "firstName":"Anvesh" ,"lastName":"Patel" ,"gender":"Male" ,"address":"Hyederabad" ,"age":28 ,"role": [ "admin" ,"vendor" ] } ' ) ,( 2 ,'Production' ,' { "firstName":"Neevan" ,"lastName":"Patel" ,"gender":"Male" ,"address":"Hyederabad" ,"age":20 ,"role": [ "user" ,"production" ] } ' ); |
Create a GIN index on jsonb column:
|
1 2 |
CREATE INDEX idx_tbl_TestJSONData_EmployeeDetails ON tbl_TestJSONData USING GIN (EmployeeDetails); |
The SELECT query which makes use of this created index:
|
1 2 3 4 5 6 |
SELECT EmployeeDetails -> 'firstName' ,EmployeeDetails -> 'lastName' ,EmployeeDetails -> 'gender' FROM tbl_TestJSONData WHERE EmployeeDetails @> '{"age":28}' |
Create expression indexes for particular internal items and keys:
|
1 2 |
CREATE INDEX idx_tbl_TestJSONData_EmployeeDetails_Roles ON tbl_TestJSONData USING GIN ((EmployeeDetails -> 'role')); |
To SELECT internal ‘role’ key which uses the index:
|
1 2 3 4 5 6 |
SELECT EmployeeDetails -> 'firstName' ,EmployeeDetails -> 'lastName' ,EmployeeDetails -> 'gender' FROM tbl_TestJSONData WHERE EmployeeDetails -> 'role' ? 'admin'; |
