knex: what is the appropriate way to create an array from results?

Question

I have an endpoint that joins the user and user_emails table as a one-to-many relationship (postgresql). It look as follows.

router.get('/', function (req, res, next) {
  db.select('users.id', 'users.name', 'user_emails.address')
    .from('users')
    .leftJoin('user_emails', 'users.id', 'user_emails.user_id')
    .then(users => res.status(200).json(users))
    .catch(next)  // go to error handler
});

However, this will return a new document for each email address. What I want is an array of documents that looks as follows:

[{
  id: 1,
  name: 'Steve',
  emails: [
    { address: 'hello@world.org' },
    { address: 'meow@meow.org' }
  ]
}, {
  id: 2,
  name: 'Jimmy',
  emails: [
    { address: 'jimmy@jimbo.org' }
  ]
}]

How should this be done in knex?

if i understand correctly , you're getting 1 row for each address but you want it to be nested to 'name'? — Jayaram, May 17 '16 at 12:04

score 29 · Accepted Answer · answered May 17 '16 at 14:29

29

Assuming you're using Postgres - you need to use array_agg function to generate arrays. I would suggest using knex.raw

Please let me know if this works.

   knex('users')
    .innerJoin('user_emails','users.id','user_emails.user_id')
    .select([
      'users.id as userID',
      'users.name as userName',
      knex.raw('ARRAY_AGG(user_emails.adress) as email')
    ])
    .groupBy('users.id','users.name')

answered May 17 '16 at 14:29

Jayaram

6,276
12
42
78

Works absolutely perfectly! Thank you! Although, if you happen to know, can this be used to make an array of objects? – corvid May 17 '16 at 15:08
if you're talking about creating an array of json objects , postgres 9.4 and above support the use of json_object(col 1, col 2). – Jayaram May 17 '16 at 15:16
3

Ah, that worked, but lead to a nifty link for `json_build_object` as well – corvid May 17 '16 at 15:26
2

For some reason, I am getting only one value in the array. What could be the reason for that? – Erik Rybalkin Oct 15 '20 at 09:50

knex: what is the appropriate way to create an array from results?

1 Answers1

Linked