aggregate 3 Tables to json postgresql

Question

I have 3 tables:

customers:

    _______________
    | id  |  name |
    |-----|-------|
    | 1   |  John |
    | 2   |  Adam |
    | 3   | Smith |
    ---------------

cities:
__________________
| id  |  city    |
|-----|----------|
| 1   |   LA     |
| 2   |   PHI    |
| 3   |   HOU    |
| 4   |   DET    |
------------------

customers_cities:
________________________________
| id  |  customer_id | city_id |
|-----|--------------|----------
| 1   |   1          |   1     |
| 2   |   1          |   2     |
| 3   |   2          |   3     |
| 4   |   3          |   2     |
| 5   |   1          |   3     |
--------------------------------

I wanted to aggregate and show in a json format all the cities and their customers listed in them. Some customers can be in different cities too.

Output should look like:

- LA:
   |- John
- PHI
   |- John
   |- Smith
- Hou
   |- Adam
   |-John

The JSON output looks like this:

   "data":{
        "customers_in_cities": [
          "LA" : [
              {
               "id": 1,
               "name": "John"
               }],
          "PHI" : [
              {
               "id": 1,
               "name": "John"
               },
              {
               "id": 3,
               "name": "Adam"
               }],
         "HOU" : [
              {
               "id": 3,
               "name": "Adam"
               },
              {
               "id": 1,
               "name": "John"
               }],
    ]
    
    }

Something like the above json output.

I tried:

SELECT cus.name AS customer_name, cty.city AS city_name
FROM cities cty 
INNER JOIN customers_cities cc ON cc.city_id = cty.id 
INNER JOIN customers cus ON cc.customer_id = cus.id;

will appreciate any help

Showing the results you want would really help. For instance, you say you want JSON, but your sample results are not JSON. — Gordon Linoff, Feb 02 '21 at 22:42
Does this answer your question? [Create nested json from sql query postgres 9.4](https://stackoverflow.com/questions/42222968/create-nested-json-from-sql-query-postgres-9-4) — astentx, Feb 02 '21 at 22:45

score 0 · Answer 1 · answered Feb 03 '21 at 02:36

I am going to post the SQL query that took me a couple of hours to find out! actually paying the credit to this post Return as array of JSON objects in SQL (Postgres) e by Erwin Brandstetter

the sql query goes like this:

SELECT c.*,
json_agg(row_to_json(c)) AS customers_in_cities
                  
FROM cities c
LEFT JOIN customers_cities cc ON cc.city_id = c.id
LEFT JOIN customers cust ON cust.id = cc.customer_id
GROUP BY c.id;

thank you

aggregate 3 Tables to json postgresql

1 Answers1