Search code examples
javascriptjsonnode.jsnode-mysql

How can I create a array of tables using INNER JOIN with node-mysql?

When using node-mysql, this select SELECT shop.shopId, shop.name, address.address FROM shop INNER JOIN address ON shop.shopId = address.shopId returns a json like that:

[{
     "shopId":1,
     "name":"Lorem",
     "street":"Street XXXXX"
},{
     "shopId":1,
     "name":"Lorem",
     "street":"Street YYYYY"
},{
     "shopId":2,
     "name":"Ipsum",
     "street":"Street ZZZZZ"
}]

But what I need is something like this

[{
     "shopId":1,
     "name":"Lorem",
     "street":[{"Street XXXXX"},{"Street YYYYY"}]
},{
     "shopId":2,
     "name":"Ipsum",
     "street":"Street ZZZZZ"
}]

How could i do that using node-mysql? Knex or sequelize can do it?

Solution

  • Try adding GROUP BY shopId . I guess changing the Sql string can give you desired results.