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
– Gordon Linoff, Commented Feb 2, 2021 at 22:42
Does this answer your question? Create nested json from sql query postgres 9.4 — astentx
– astentx, Commented Feb 2, 2021 at 22:45

Chosen · Accepted Answer · 2021-02-03 02:36:03Z

0

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

answered Feb 3, 2021 at 2:36

Chosen

8922 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

aggregate 3 Tables to json postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related