This article is half-done without your Comment! *** Please share your thoughts via Comment ***
JSON is one of the interesting topics or new RDBMSs, now with the new version of PostgreSQL 9.4, we can store JSON formatted data.
You can visit this article, on the overview of PostgreSQL 9.4 features.
Now We can say, we have PostgreSQL 9.4 with the RDBMS + NOSQL concepts.
We can also store JSON data as text, but the JSON Data types have the advantage of enforcing each stored for validing JSON rules or formats.
PostgreSQL 9.4 also introduced some set of JSON specific functions and operators.
Two type of SON data types:
json: It stores an exact copy of the input json text, while processing functions it reparses for each execution. Our insertion process may faster by using this json data type.
The JSON data type also preserves a white space between tokens and order of the keys because it stores an exact copy of json input text.jsonb:
It stores the JSON formatted data in a decomposed binary format so input slightly slower because of the conversion overhead.
(While inserting, it is validating the correct format of JSON. If JSON format is invalid, you cannot insert it.)The jsonb data type does not preserve white space and order of object keys.
Automatically, it also removes a duplicate key from the input string and keep last value only.
If we do not have any problem with ordering of object key, we should use jsonb data type to store JSON formatted data.
Below is a small practical demonstration of this:
First, create sample table with jsonb data type:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_TestJSON ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSONB ); |
Insert correct JSON formatted data:
Please also test and try to insert some incorrect JSON formatted data, you will get an error like: invalid input syntax for type json.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
INSERT INTO tbl_TestJSON VALUES ( 1 ,'Sales' ,' {"employees":[ {"firstName":"Anvesh", "lastName":"Patel"}, {"firstName":"Eric", "lastName":"Marin"}, {"firstName":"Martin", "lastName":"Loother"} ]}' ) ,( 2 ,'Production' ,' {"employees":[ {"firstName":"Neevan", "lastName":"Patel"}, {"firstName":"Roy", "lastName":"Boon"}, {"firstName":"Nancy", "lastName":"Shah"} ]}' ); |
SELECT data by searching the key value using WHERE clause:
|
1 2 |
SELECT * FROM tbl_TestJSON WHERE EmployeeDetails @> '{"employees":[{"lastName":"Loother"}]}' |
The Result: Returned only one record.
