This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Recently, PostgreSQL introduced JSON data type which is very powerful to manipulate JSON formatted data.
I published a couple of articles on JSON Data Type.
You can start from here.
In this post, I am showing aggregation of table data into a JSON formatted array using json_agg().
Our requirement is to aggregate non json formatted data into JSON array and even also required to aggregate JSON formatted data.
I prepared a small demonstration using json_agg().
Create a table with sample JSON formatted data:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_TestJSON ( ID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSONB ); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
INSERT INTO tbl_TestJSON VALUES ( 1 ,'Sales' ,' {"employees":[ {"firstName":"Anvesh", "lastName":"Patel"} ]}' ) ,( 2 ,'Production' ,' {"employees":[ {"firstName":"Nancy", "lastName":"Shah"} ]}' ); |
Aggregates JSON formatted data:
|
1 2 3 4 5 |
SELECT json_agg(employeedetails) AS JsonArray FROM tbl_TestJSON; JsonArray ------------------------ [{"employees": [{"lastName": "Patel", "firstName": "Anvesh"}]}, {"employees": [{"lastName": "Shah", "firstName": "Nancy"}]}] |
Aggregates non JSON formatted data:
|
1 2 3 4 5 |
SELECT json_agg(DepartName) AS JsonArray FROM tbl_TestJSON; JsonArray ------------------------ ["Sales", "Production"] |
