1

I am joining 3 tables and getting some duplicated data for certain columns. I want to combine the data to a single row.

The following is the database structure and data.

CREATE TABLE customers (
    id integer NOT NULL,
    fname character varying(50),
    lname character varying(50)
);

COPY customers (id, fname, lname) FROM stdin;
1   Thomas  Jefferson
2   Benjamin    Franklin
\.

CREATE TABLE customers_products (
    id integer NOT NULL,
    customers_id integer,
    products_id integer,
    account_info json
);

COPY customers_products (id, customers_id, products_id, account_info) FROM stdin;
1   1   1   {"account_number":"000001", "date":"05/28/15"}
2   1   2   {"account_number":"000002", "date":"05/28/15"}
3   2   1   {"account_number":"000003", "date":"05/28/15"}
4   2   3   {"account_number":"000004", "date":"05/28/15"}
\.

CREATE TABLE products (
    id integer NOT NULL,
    name character varying(128)
);

COPY products (id, name) FROM stdin;
1   checking
2   savings
3   credit card
\.


This is the query I'm using.

SELECT
    c.id,
    c.fname,
    c.lname,
    p.name,
    cp.account_info
FROM
    customers c
INNER JOIN
    customers_products cp
ON
    c.id = cp.customers_id
INNER JOIN
    products p
ON
    p.id = cp.products_id


This is the resulting dataset.

result dataset

How would I get the dataset back in 2 rows with the account information combined into single rows? This is the data structure I would like to end up with.

[
    {
        "id": 1,
        "fname": "Thomas",
        "lname": "Jefferson",
        "account_info": [
            {
                "name": "checking",
                "account_number":"000001",
                "date":"05/28/15"
            },
            {
                "name": "savings",
                "account_number":"000002",
                "date":"05/28/15"
            }
        ]
    },
    {
        "id": 2,
        "fname": "Benjamin",
        "lname": "Franklin",
        "account_info": [
            {
                "name": "checking",
                "account_number":"000003",
                "date":"05/28/15"
            },
            {
                "name": "credit card",
                "account_number":"000004",
                "date":"05/28/15"
            }
        ]
    }
]

The solution was:

SELECT
    c.id,
    c.fname,
    c.lname,
    string_agg(p.name, ', '),
    json_agg(cp.account_info)
FROM
    customers c
INNER JOIN
    customers_products cp
ON
    c.id = cp.customers_id
INNER JOIN
    products p
ON
    p.id = cp.products_id
GROUP BY
    c.id, c.fname, c.lname
ORDER BY
    c.id;
Bruce Lim
  • 745
  • 6
  • 22
  • 1
    JSON creation is not the SQL job. The data structure you get with the SQL you provided is pretty good starting point to get the data suitable for producing JSON you need. Maybe you may make it a bit more reliable by adding ```order by id``` statement. Then just go through this dataset and create JSON. – Ihor Pomaranskyy May 28 '15 at 18:48
  • Maybe the JSON result set was not a good example to show, I just put that there to show what data structure I would like to end up with. It should be 2 rows instead of 4 rows and account_info combined. There's gotta be some way to achieve this in postgres, I managed to do this with GROUP_CONCAT and CONCAT in mysql before. – Bruce Lim May 28 '15 at 18:51
  • 1
    OK, I got it. Take a look at this: http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query – Ihor Pomaranskyy May 28 '15 at 18:57
  • Thanks for the leads, I'll try it out and post an update. – Bruce Lim May 28 '15 at 21:57
  • The other article only shows how to do string concatenation, I had to use a mix of string_agg and json_agg to get the correct data set. Thanks Igor for pointing me in the right direction. – Bruce Lim May 28 '15 at 23:33

0 Answers0