Restful API with Nodejs and Mysql with Multiple Columns (I want to return json levels)

Asked

Viewed 258 times

1

all right. Someone can help me with a matter of Angular, or the API with Node.js?

I consult Mysql in an api with Node.js with Inner Join in 2 tables, but how to expect mysql returns me everything in a single array, but I think it is more correct and feasible to return the arrays in level.

Example returning the Results directly from mysql:

[
    {
        coluna1: 'AAAAA',
        coluna2: 'XXXXX'
    },{
        coluna1: 'AAAAA',
        coluna2: 'YYYYY'
    },{
        coluna1: 'BBBBB',
        coluna2: 'ZZZZZ'
    },
]

What I want is to turn into that:

[
    {
        coluna1: 'AAAAA',
        outra_arary: [
            {
                coluna2:  'XXXXX'
            },{
                coluna2:  'YYYYY'
            }
        ]
    },{
        coluna1: 'BBBBB',
        outra_arary: [
            {
                coluna2:  'ZZZZZ'
            }
        ]
    }
]

An example of how the method is:

router.get('/', (req, res, next) => {
    res.locals.connection.query(`select *
                                   from tabela1
                             inner join tabela2
                                on tabela1.id = tabela2.id;`, (error, results, fields) => {
        if (error) {
            res.send({
                "status" : 500,
                "error" : error,
                "response" : null
            });
        } else {
            res.send({
                "status" : 200,
                "error" : null,
                "response" : results
            });
        }
    });
});
node.js api

1 answer

0


One idea is to use:

// Exemplo de resposta do MYSQL
const resultados = [
  {
      coluna1: 'AAAAA',
      coluna2: 'XXXXX'
  },{
      coluna1: 'AAAAA',
      coluna2: 'YYYYY'
  },{
      coluna1: 'BBBBB',
      coluna2: 'ZZZZZ'
  },
]

// Função que retorna o array "outra_array", que contém vários objetos com os dados de coluna2
function retornaColuna(coluna) {
  const colunaFiltrada = new Array()
  resultados.forEach(valor => {
    if(valor.coluna1 == coluna)
      colunaFiltrada.push({ coluna2: valor.coluna2 })
  })
  return colunaFiltrada
}

// Itera sobre o array de resposta do MYSQL, criando a estrutura desejada. Nesse caso, coluna1 pode estar repetido.
let resposta = new Array()
resultados.forEach(valor => {
  resposta.push({ coluna1: valor.coluna1, outra_array: retornaColuna(valor.coluna1) })
})

// Remove as colunas1 duplicadas.
resposta = resposta.filter((a) => !this[JSON.stringify(a)] && (this[JSON.stringify(a)] = true))

console.log(resposta) // Retornará o resultado esperado

The last function, which removes possible duplicated objects in the Array, is available here at the Stack

  • Thank you João Pedro, I didn’t really understand the idea but I will try to do the test and put the answer here. I am new with Node.js. To this day I still pick up a little to understand these concepts.

  • I got John Peter, I understood the logic too, it was super simple. Very good, thank you.

  • John, now when I run it 2 times in a row, it returns my null array, as if I were storing cache, sometimes returns 304

  • The code or the request?

  • 1

    It was the request that I was doing in the API, but then I discovered that there was something missing in that last line where we removed the duplicates, I realized that comparing the difference of your code here with the link you mentioned Fixed looks like this: retornoFinal = retornoFinal.filter(function (a) {
 return !this[JSON.stringify(a)] && (this[JSON.stringify(a)] = true);
 }, Object.create(null)); Now it works!! Thank you very much!

  • Well noted. I’m glad it all worked out ;D

Show 1 more comment

Browser other questions tagged node.js api

You are not signed in. Login or sign up in order to post.