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 have published a couple of articles on JSON Data Type.
You can start from here.
Recently, I have received a couple of comments on my FB page related use of PostgreSQL JSONB.
A JSONB data type does not preserve any white spaces. Whatever format we have specified, It removes and store without white spaces.
PostgreSQL 9.5 introduced jsonb_pretty() to get JSON formatted data.
When we are troubleshooting JSON related data at that time we require to get JSON in readable format.
Below is a small demonstration of jsonb_pretty():
Create table with 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 |
CREATE TABLE tbl_TestJSON ( EmpID INTEGER PRIMARY KEY ,DepartName VARCHAR(250) ,EmployeeDetails JSONB ); INSERT INTO tbl_TestJSON VALUES ( 1 ,'Sales' ,' {"employees":[ {"firstName":"Anvesh", "lastName":"Patel"} ]}' ) ,( 2 ,'Production' ,' {"employees":[ {"firstName":"Nancy", "lastName":"Shah"} ]}' ); |
Using jsonb_pretty format function:
|
1 |
SELECT jsonb_pretty(EmployeeDetails) AS EmployeeDetails FROM tbl_TestJSON; |
Result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
employeedetails ----------------------------------- { + "employees": [ + { + "lastName": "Patel", + "firstName": "Anvesh"+ } + ] + } { + "employees": [ + { + "lastName": "Shah", + "firstName": "Nancy" + } + ] + } (2 rows) |
