How to get rows as array of JSON object instead of array of array in MySQL X DevAPI select query?

Question

I am using MySQL X DevAPI for relational tables in MySQL.

// Working with Relational Tables
var mysqlx = require('@mysql/xdevapi');
var myTable;

// Connect to server using a connection URL
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: 33060
  })
  .then(function (session) {
    // Accessing an existing table
    myTable = session.getSchema('test').getTable('my_table');

    // Insert SQL Table data
    return myTable
      .insert(['name', 'birthday', 'age'])
      .values(['Laurie', '2000-5-27', 19])
      .execute()
  })
  .then(function () {
    // Find a row in the SQL Table
    let columns = ['name', 'birthday']
    return myTable
        .select(columns)
        .where('name like :name && age < :age)')
        .bind('name', 'L%')
        .bind('age', 30)
        .execute();
  })
  .then(function (myResult) {
    let rows = myResult.fetchAll() // this is array of array
    let columns = ['name', 'birthday']
    rows = rows.map((row) => columns.reduce((a, column, index) => Object.assign(a, { [column]: row[index] }), {}))
    console.log(rows); // this is array of JSON object which is usefull for WEB API reposnse.
  });

In this code I have to convert array of arrays into array of json object all the time

function (myResult) {
    let rows = myResult.fetchAll() // this is array of array
    let columns = ['name', 'birthday']
    rows = rows.map((row) => columns.reduce((a, column, index) => Object.assign(a, { [column]: row[index] }), {}))
    console.log(rows); // this is array of JSON object which is usefull for WEB API reposnse.
  }

X Dev API is good library so there would be some good way to get results in JSON format directly.

What are the ways to get rows in JSON format in MySQL X DevAPI?

score 0 · Answer 1 · answered Jul 21 '23 at 14:23

In table mode, you either need to that or you can use something like the JSON_OBJECT() function and then flat the resulting array.

let columns = ['name', 'birthday']
let keyValuePairs = columns.map(c => `"${c}", ${c}`)

const res = await table.select(`JSON_OBJECT(${keyValuePairs})`)
  // ...
  .execute()

const rows = res.fetchAll() 

console.log(rows.flat()) // this is now an array of JSON objects

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js

How to get rows as array of JSON object instead of array of array in MySQL X DevAPI select query?

1 Answers1