How to JOIN tables and return as JSON?

Question

I have got PostgreSQL with follow table structure:

CREATE TABLE "objs"("number" Integer,"name" Text NOT NULL,  "price" Text NOT NULL );
CREATE TABLE "users"("name" Text NOT NULL,"obj_number" Text NOT NULL );

INSERT INTO "objs" ("number","name","price") VALUES ( 1,'Small Red Apples','30' );
INSERT INTO "objs" ("number","name","price") VALUES ( 1,'Big Apples','50' );
INSERT INTO "objs" ("number","name","price") VALUES ( 2,'Small Bottle','24' );
INSERT INTO "objs" ("number","name","price") VALUES ( 2,'Big Bottle','60' );
INSERT INTO "objs" ("number","name","price") VALUES ( 1,'Small green Apples','45' );

INSERT INTO "users" ("name","obj_number") VALUES ( 'Mike','1' );
INSERT INTO "users" ("name","obj_number") VALUES ( 'Jow','2' );
INSERT INTO "users" ("name","obj_number") VALUES ( 'Piter','3' );

I need to return SELECT result as JSON in next format:

[
{
        "id": "1",
        "name": "Mike",
        "objs": 
        [
            {
                "number": 1,
                "name": "Small Red Apples",
                "price": "30"
            },
            {
                "number": 1,
                "name": "Small green Apples",
                "price": "45"
            },
            {
                "number": 1,
                "name": "Big Apples",
                "price": "50"
            }
        ]
    },
    {
        "id": 2,
        "name": "Jow",
        "objs": [{
                "number": 1,
                "name": "Small Bottle",
                "price": "50"
            },
            {
                "number": 1,
                "name": "Small Bottle",
                "price": "24"
            }
        ]
    },
    {
        "id": 2,
        "name": "Jow",
        "objs": []
    }
]

It's seems that I need mix LEFT JOIN and group by, but I do not know how to do it:

SELECT
    u."name",
    u."obj_number",
    o."name",
    o."price"
FROM "users" u 

LEFT JOIN objs o ON u.obj_number = o."number" 

-- GROUP BY u."name"

user_2275388 · Answer

You can use a combination of json_agg and json_build_object to build your json array based on the GROUP BY clause. After that you can turn all rows into json documents using row_to_json in a subquery, e.g.

SELECT row_to_json(j) FROM (
  SELECT
    u.name,
    u.obj_number,
    json_agg(json_build_object('name', o.name, 'price', o.price)) AS objs
  FROM users u 
  LEFT JOIN objs o ON u.obj_number = o.number::text
  GROUP BY u.name,u.obj_number) j;
                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"name":"Piter","obj_number":"3","objs":[{"name" : null, "price" : null}]}
 {"name":"Jow","obj_number":"2","objs":[{"name" : "Small Bottle", "price" : "24"}, {"name" : "Big Bottle", "price" : "60"}]}
 {"name":"Mike","obj_number":"1","objs":[{"name" : "Small Red Apples", "price" : "30"}, {"name" : "Big Apples", "price" : "50"}, {"name" : "Small green Apples", "price" : "45"}]}
(3 Zeilen)

See db<>fiddle 1 or db<>fiddle 2

How to JOIN tables and return as JSON?

1 Answers