Export to JSON

Test Table

# users table
+--------------+-------------------+-----------------------+-----------------------+
| id [integer] | user_id [integer] |   name [string]       |   location [string]   |
+--------------+-------------------+-----------------------+-----------------------+
|            1 |               100 | "Tony"                | "New York"            |
|            2 |               101 | "Angela"              | "Los Angeles"         |
|            2 |               102 | "Samantha"            | "Los Angeles"         |
|            2 |               103 | "David"               | "San Diego"           |
|            2 |               104 | "Rocco"               | "Los Angeles"         |
+--------------+-------------------+-----------------------+-----------------------+

Array of JSON objects

This will return all rows as an array of JSON objects

SELECT array_to_json(array_agg(row_to_json(t)))
    FROM (
      SELECT * FROM users
    ) t

[{"location":"New York", "id": 1, "user_id": 100, "name": "Tony", "location": "New York"},
{"location":"Los Angeles", "id": 2, "user_id": 101, "name": "Angela", "location": "Los Angeles"}
...]

Grouping

This will return all rows grouped by an attribute

SELECT json_agg(t)
  FROM (
    SELECT
      location, json_agg(users.*) as users
    FROM
      users
    GROUP BY
      location
    ) t;

[{"location":"New York","users":[...]},
 {"location":"Los Angeles","users":[...]},
 {"location":"San Diego","users":[...]}]

To be continued

This is just the start. I’ll be adding more examples to this post. More information check Postgres documentation.