KNEX: How to create an array from the result of the query in Sqlserver?

Question

I am using the Knex to make a query that united several tables from a bank Sqlserver (use two version of Sqlserver 9.0.3042 and 12.0.5000.0), example:

knex('tabelaA')
    .join('tabelaB', 'tabelaB.idTabelaA', '=', 'tabelA.id')
    .select([
        'tabelaA.coluna1', 
        'tabelaA.coluna2',
        ['tabelaB.coluna1', 'tabelaB.coluna2']    
    ])
    .groupBy('tabelaA.coluna1', 'tabelaA.coluna2')

I need the result of each JOIN to come as an array in JSON, example:

[{
    tabelaAColuna1: 'Texto',
    tabelaAcoluna2: 'Texto',
    tabelaB: [
        {
            tabelaBColua1: 'Texto',
            tabelaBColuna2: 'Texto'
        },
        {
            tabelaBColua1: 'Texto',
            tabelaBColuna2: 'Texto'
        }
    ]

}]

In Postgresql I saw an example using ARRAY_AGG, but this function does not exist in Sqlserver.

knex.raw('ARRAY_AGG(tabelaB.coluna1) as tabelaBColua1')

How this should be done in the Knex using the Sqlserver?

Answer 1

As a solution to this problem I did so:

1. I called a bank appointment with the first attribute;
2. I made a MAP on the object and for each value went in the database and looked for the next attribute I wanted and put in the object.

For this I had a problem with the Knex Promises but I resolved creating a Promise in the process and using it to await the result.

I did this using with ASYNC/AWAIT, :(

var atributo1, atributo2, resultado

atributo1 = await knex('tabela1')
  .distinct()

var resultado= atributo1

var resultsPromise = atributo1 .map(async (obj) => {
  atributo2 = await knex('tabela2')
    .whare('tabela2.atributo1', ojb.idAtributo1)
    .distinct()

  obj.atributo2 = atributo2

  return obj
}

resultado = await Promise.all(resultsPromise)

That’s right, if anyone has any better solutions I’m still looking.

Hugs!

Antré Oliveira