This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The PostgreSQL JSON data type is also one of the my favorite topic.
I have written multiple article on PostgreSQL JSON data type.
PostgreSQL 9.5 introduced one new operator (||) which used to perform Concatenate and Overwrite of JSON Document.
The requirement is something like: we have stored one JSON string or document into PostgreSQL. Now we require to add any new key or elements into that document.
It was not possible in the previous version of PostgreSQL, but now we can use (||) this operator to add or overwrite particular key into the JSON document.
Please go through this full practical demonstration:
Create a table with Sample 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 |
CREATE TABLE tbl_TestJSON ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSONB ,Address JSONB ); INSERT INTO tbl_TestJSON VALUES ( 1 ,'Sales' ,' {"employees":[ {"firstName":"Anvesh", "lastName":"Patel"}, {"firstName":"Eric", "lastName":"Marin"}, {"firstName":"Martin", "lastName":"Loother"} ]}' ,'{"address" : "Hyderabad"}' ) ,( 2 ,'Production' ,' {"employees":[ {"firstName":"Neevan", "lastName":"Patel"}, {"firstName":"Roy", "lastName":"Boon"}, {"firstName":"Nancy", "lastName":"Shah"} ]}' ,'{"address" : "Ahmedabad"}' ); |
Using (||) operator can concat two JSON String:
In the below example, you can see “address” key is merged with “employees” key.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT EmployeeDetails || Address AS JsonData FROM tbl_TestJSON; /* JsonData ------------------------------------------------ {"address": "Hyderabad" ,"employees": [{"lastName": "Patel", "firstName": "Anvesh"} ,{"lastName": "Marin", "firstName": "Eric"} ,{"lastName": "Loother", "firstName": "Martin"} ] } {"address": "Ahmedabad" ,"employees": [{"lastName": "Patel", "firstName": "Neevan"} ,{"lastName": "Boon", "firstName": "Roy"} ,{"lastName": "Shah", "firstName": "Nancy"} ] }*/ |
Using (||) operator can overwrite the JSON string:
In the below example, you can see the replacement of “address” value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT EmployeeDetails || Address || '{"address": "Pune"}'FROM tbl_TestJSON; /* JsonData ------------------------------------------------- {"address": "Pune" ,"employees": [{"lastName": "Patel", "firstName": "Anvesh"} ,{"lastName": "Marin", "firstName": "Eric"} ,{"lastName": "Loother", "firstName": "Martin"} ] } {"address": "Pune" ,"employees": [{"lastName": "Patel", "firstName": "Neevan"} ,{"lastName": "Boon", "firstName": "Roy"} ,{"lastName": "Shah", "firstName": "Nancy"} ] }*/ |
