Merge "redundant" sqlite left join rows in a javascript array to JSON-CodePudding

I am creating a REST API in expressjs for my website, currently I have a Product table like this:

id | name 
1  | Shirt

Products can have known flaws, listed in a seperate table Flaw

id | desc          | productId
1  | short sleeve  | 1
2  | no buttons    | 1

On my front end, I want to have all flaws listed for that product, so I need a JSON like follows:

[{"id": 1, "name": "Shirt", "flaws": [{"desc": "short sleeve"}, {"desc": "no buttons"}]}]

Currently, my SQL query in express looks like this:

var sql = "SELECT Product.id, Product.name, Flaw.desc FROM Product LEFT JOIN Flaw ON Flaw.productId = Product.id"

and this is how I do the call and get it to JSON:

    db.all(sql, (err, rows) => {
      if(err){
        res.status(400).json({"error": err.message});
      }
      res.status(200).json(rows);
    });

However, this results me in the following:

[{"id": 1, "name": "Shirt", "desc": "short sleeve"}, {"id": 1, "name": "Shirt", "desc": "no buttons"}]

I understand that this is due to the nature of my SQL query (using LEFT JOIN), but I'd like to know a good way of "merging" the redundant parts into one item (id, name) and have a "sub pair" called flaws in my JSON, where the non redundant flaw parts are listed.

CodePudding user response：

Get your json directly from a query:

SELECT json_object(
         'id', p.id, 
         'name', p.name,
         'flaws', json_group_array(json_object('desc', f.desc))
       ) result 
FROM Product p LEFT JOIN Flaw f
ON f.productId = p.id
GROUP BY p.id;

See the demo.