Postgres - Return result set as JSON
This indispensable library has brought me a great deal of joy when it comes to json-ifying objects and primitives.
Variable data to JSON
No complexity here when it comes to Strings:
```javaRather than having a database call returning a result set which is then parsed into a database object, it is sometimes useful to have the result set returned as JSON.
Using the row_to_json function we can return each record as a JSON string
```sqlselect row_to_json(u.*, false)
from users u
Returns:
| row_as_json |
----------------------------------------------------------------------------------------------
| 1 | "{"id":1,"username":"admin","first_name":"Administrator","last_name":"Administrator"}" |
----------------------------------------------------------------------------------------------
| 2 | "{"id":2,"username":"jsmith","first_name":"John","last_name":"Smith"}" |
----------------------------------------------------------------------------------------------
| 3 | "{"id":3,"username":"jdoe","first_name":"Jane","last_name":"Doe"}" |
----------------------------------------------------------------------------------------------
If we only need the IDs and usernames of the users, we can modify our query like so:
```sqlselect row_to_json(u.*, false) from ( select id, username from users ) u
Which returns:
```text-------------------------------------
|row_as_json |
-------------------------------------
| 1 | "{"id":1,"username":"admin"}" |
-------------------------------------
| 2 | "{"id":2,"username":"jsmith" |
-------------------------------------
| 3 | "{"id":3,"username":"jdoe" |
-------------------------------------
Rather than returning multiple rows we can return a single row with all the records as a JSON array. To do this we will utilise the array_agg function to aggregate all the records as an array in a single row and then the array_to_json function will convert the array to a JSON array:
```sqlselect array_to_json(array_agg(row_to_json(u, false)), false) from ( select id, first_name, last_name from users ) u
Which returns:
```text------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|row_as_json |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | "[{"id":1,"first_name":"Administrator","last_name":"Administrator"},{"id":10,"first_name":"John","last_name":"Smith"},{"id":13,"first_name":"Jane","last_name":"Doe"}]"|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------