This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous post, I discussed newly introduced JSON data type of PostgreSQL 9.4.
In this post, I am showing some of the basic JSON functions and operators which are also introduced by PostgreSQL 9.4.
Create sample table with jsonb data type and insert sample 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" ] } ' ); |
SELECT JSON Data using ‘->’ and ‘->>’ operators:
|
1 2 3 4 5 6 7 8 9 |
SELECT EmployeeDetails->'firstName' AS FirstName FROM tbl_TestJSONData; /* Result as a JSON object. (Using ->) firstname ---------- "Anvesh" "Neevan" */ |
|
1 2 3 4 5 6 7 8 9 |
SELECT EmployeeDetails->>'firstName' AS FirstName FROM tbl_TestJSONData; /* Result as a TEXT. (Using ->>) firstname ---------- Anvesh Neevan */ |
JSON Key value criteria in WHERE clause:
|
1 2 3 4 |
SELECT *FROM tbl_TestJSONData WHERE EmployeeDetails @> '{"age":28}'; /* one sales record is returned. */ |
JSON_EACH and JSONB_EACH Functions:
JSON_EACH and JSONB_EACH function to expand a JSONs data and nested JSONs are not expanded.
JSON_EACH for json data type and JSONB_EACH for jsonb data type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT jsonb_each(EmployeeDetails) AS JSONData FROM tbl_TestJSONData; /*Result: JSONData --------------- "(age,28)" "(role,"[""admin"", ""vendor""]")" "(gender,"""Male""")" "(address,"""Hyederabad""")" "(lastName,"""Patel""")" "(firstName,"""Anvesh""")" "(age,20)" "(role,"[""user"", ""production""]")" "(gender,"""Male""")" "(address,"""Hyederabad""")" "(lastName,"""Patel""")" "(firstName,"""Neevan""")" */ |
JSON_OBJECT_KEYS and JSONB_OBJECT_KEYS Functions:
Returns all set of keys of JSON object and nested keys are not displayed.
JSON_OBJECT_KEYS is for json data type and JSONB_OBJECT_KEYS is for jsonb data type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT jsonb_object_keys(EmployeeDetails) AS JSONKeys FROM tbl_TestJSONData; /*Result: age role gender address lastName firstName age role gender address lastName firstName */ |
JSON_TYPEOF and JSONB_TYPEOF Functions:
This both functions respectively, for each data type and return the data type of the JSON keys.
|
1 2 |
SELECT jsonb_typeof(EmployeeDetails->'firstName') AS JSONKeyDataType FROM tbl_TestJSONData; |
For more reference you can visit this official PostgreSQL document.
