Model Include option to return attributes as single value with defined alias instead of an object with properties

I have 2 models:

• Property
• Account

A Property hasOne Account

Property

Property.belongsTo(models.Account, {
  as: 'account',
  foreignKey: 'accountNumber'
});

Account

Account.hasOne(models.Property, {
  as: 'property',
  foreignKey: 'accountNumber'
});

On a findAll query I have

const properties = await Property.findAll({
 attributes: ['accountNumber'],
 include: [
      {
        model: Models.Account,
        as: 'account',
        attributes: ['organisation', 'email'],  
      },
 ]
});

This returns an object for each item like;

{ 
  "accountNumber":"AC0012",
  "account":{ 
    "organisation":"Example Org",
    "email":"[e-mail]"
  }
}

However, what I aim to achieve is something like,

{ 
  "accountNumber":"AC0012",
  "accountOrganisation":"Example Org",
  "accountEmail":"[e-mail]"
}

The current MySQL query is as follows;

SELECT `Property`.`id`, 
`Property`.`account_number` AS `accountNumber`, 
`account`.`account_number` AS `account.accountNumber`, 
`account`.`organisation` AS `account`.`organisation`, 
`account`.`email` AS `account.email` 
FROM `property_dev`.`property` 
AS `Property` 
LEFT OUTER JOIN `property_dev`.`account` AS `account` 
ON `Property`.`account_number` = `account`.`account_number`

I need to update the aliases used from;

`account`.`organisation` AS `account`.`organisation`, 
`account`.`email` AS `account.email` 

to

`account`.`organisation` AS `accountOrganisation`, 
`account`.`email` AS `accountEmail` 

How can I achieve this? It seems like something that’s quite simple but I can’t seem to query the correct solution. I may be using the incorrect terms in my searches, going through the official docs haven’t lead me to the solution.

Any help would be greatly appreciated

Answer

You can alias the joined columns using an array with [value, key], where value is the sequelize.col() value of the included model. Since you want just the raw JSON results you can also pass in raw: true to not parse the results to Model Instances for better performance.

const properties = await Property.findAll({
  attributes: [
    'accountNumber',
    [sequelize.col('account.organisation'), 'accountOrganisation'],
    [sequelize.col('account.email'), 'accountEmail'],
  ],
  include: {
    model: Models.Account,
    as: 'account',
    attributes: [],
  },
  raw: true,
});