knex: what is the appropriate way to create an array from results?
14,315
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')
Comments
-
corvid almost 2 years
I have an endpoint that joins the
user
anduser_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: '[email protected]' }, { address: '[email protected]' } ] }, { id: 2, name: 'Jimmy', emails: [ { address: '[email protected]' } ] }]
How should this be done in knex?
-
Kannaj about 8 yearsif i understand correctly , you're getting 1 row for each address but you want it to be nested to 'name'?
-
corvid about 8 yearsnested into
emails
as an array but yes
-
-
corvid about 8 yearsWorks absolutely perfectly! Thank you! Although, if you happen to know, can this be used to make an array of objects?
-
Kannaj about 8 yearsif 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).
-
corvid about 8 yearsAh, that worked, but lead to a nifty link for
json_build_object
as well -
Erik Rybalkin over 3 yearsFor some reason, I am getting only one value in the array. What could be the reason for that?