This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I have written a couple of articles about the awesome JSON data type of the PostgreSQL.
I am really very impressed with RDBMS + NoSQL (JSON) implementation at one place.
Recently, I have published one article about to create GIN index on JSON data of PostgreSQL.
In this post, I am sharing one demonstration to create an index on a particular element or property of JSON data.
We can also create GIN index on whole JSON column, but whenever we require an index on particular JSON property, we should create it.
First, create a sample table:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_JSONDataIndex ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSONB ); |
Create an index on JSON property:
I have created one integer index on Employee’s Age property.
|
1 2 |
CREATE INDEX idx_JSON_EmpAge ON tbl_JSONDataIndex(cast(EmployeeDetails->>'age' AS INT)); |
Insert few sample records:
|
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 |
INSERT INTO tbl_JSONDataIndex 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" ] } ' ); |
