Return flat object from sequelize with association

Question

I am working on converting all my queries in sequelize. The problem I have come across is that when select queries include associations (ex. one to many), the object I get is an array of nested objects.

It looks something like:

[   
  {
    "field1": "someval",
    "field2": "someval1",
    "assoc_table": {
      "field_a": 1,
      "field_b": "someval"
    }   
  },   
  {
    "field1": "someval",
    "field2": "someval3",
    "assoc_table": {
      "field_a": 5,
      "field_b": "someval"
    }   
  },   
  {
    "field1": "someval",
    "field2": "someval3",
    "assoc_table": {
      "field_a": 12,
      "field_b": "someval"
    }   
   } 
]

I tried to use different modules to flatten the objects (inside a loop, each object individually), but I always got an error telling that what I was trying to flatten were not just objects.

Moreover, I would prefer avoiding the part where objects are flattened, and simply get a flat result with sequelize.

The sequelize code looks something like this:

models.table1.findAll({
    attributes: ['field1', 'field2'],
    where: {field1: someval},
    include: [{model: models.assoc_table, required: true, attributes:['field_a', 'field_b']}]
}).then(function (result) {
    res.send(result);
}).catch(function(error) {
    console.log(error);
});

Answer 1

Part of your issue is probably that your result is an array of model instances, so you might be having issues flattening it if you didn't call toJSON on the elements in the array. I provided code that would flatten your example:

result.forEach(obj => { 
    Object.keys(obj.toJSON()).forEach(k => {
        if (typeof obj[k] === 'object') {       
            Object.keys(obj[k]).forEach(j => obj[j] = obj[k][j]);
        }
    });
});

You can also add raw: true as an option to findAll, which will flatten your object, but it will look like this:

[   
  {
    "field1": "someval",
    "field2": "someval1",
    "assoc_table.field_a": 1,
    "assoc_table.field_b": "someval"
  },
  ...
]

Answer 2

Old question, but as I was attempting to do this also and found a pure sequelize solution that does not require the "after" mapping, I wanted to add that here. So to have sequelize itself return the desired object format, it would be this, where the attributes are explicitly assigned based off column return values from the include table:

models.table1.findAll({
    attributes: [
      'field1', 
      'field2',
      [sequelize.col('models.assoc_table.field_a'), 'field_a'], // Set key
      [sequelize.col('models.assoc_table.field_b'), 'field_b'], // Set key
    ],
    where: {field1: someval},
    include: [
     {model: models.assoc_table, 
      required: true, 
      attributes:[], // Explicitly do not send back nested key's
     }
   ]
})

Answer 3

old question, but with new sequelize updates, use both:

raw:true,
nest:true

Answer 4

Using raw: true a helper function can simplify the return keys. This will make sure no values are over-written and gives a way to keep some of string-based nesting (IDs for example).

/**
  Simplify keys returned by a sequelize {raw: true} query. Makes sure no values
  are over-written and gives a way to keep some of string-based nesting (IDs for
  example).

  @example result.map(r => trimKeys(r))
*/
function trimKeys(obj, deepin = ['id']) {
  const keys = Object.keys(obj)
  const ret = {}
  for (var i = 0; i < keys.length; i++) {
    const key = keys[i]
    const keyParts = key.split('.')
    let idx = 1
    let newKey = keyParts[keyParts.length - idx]
    while((ret[newKey] || deepin.find(d => newKey === d)) && idx >= 0) {
      idx++
      newKey = keyParts[keyParts.length - idx] + '.' + newKey
    }
    ret[newKey] = obj[key]
  }
  return ret
}