How to get an Object without wrapping of square brackets in PostgreSQL using Node.js and express?

Question

I'm using PostgreSQL with node-postgres: pool, Node.js and express to make some simple queries, I get in return the object wrapped with square brackets but I would like to get it without it.

The employeeDA.js:

const Pool = require("pg").Pool;
require("dotenv").config();

const pool = new Pool({
  user: process.env.USER,
  host: process.env.HOST,
  database: process.env.DATABASE,
  password: process.env.PASSWORD,
  port: process.env.PORT
});

const getOneDiet = id => {
  let p = new Promise((resolve, reject) => {
    return pool.query(
      `SELECT employee.id, employee.first_name, employee.last_name, array_agg(diet.description) AS diet_list
      FROM employee_diet
        INNER JOIN employee ON employee_diet.employee_id = employee.id
        INNER JOIN diet ON employee_diet.diet_id = diet.id
      WHERE employee.id = ${id}
      GROUP  BY 1`,
      (error, results) => {
        if (error) {
          console.log(error);
          reject(`ERROR: unable to get count: ${error}`);
        } else {
          resolve(results.rows);
        }
      }
    );
  });
  return p;
};

The conrtoller.js:

  const getEmployeeDiet = async (request, response) => {
  const id = parseInt(request.params.id);
  try {
    const employeeDiet = await employeeDA.getOneDiet(id);
    console.log(employeeDiet)
    response.status(200).json(employeeDiet);
  } catch (error) {
    console.log(error);
  }
}

The result:

[
     {
      "id": 8,
      "first_name": "obos",
      "last_name": "fomo",
      "diet_list": [
          "vegan",
          "paleo"
      ]
     }
]

I want the result to be:

{
      "id": 8,
      "first_name": "obos",
      "last_name": "fomo",
      "diet_list": [
          "vegan",
          "paleo"
      ]
}

As suggested here I saw that in SQL Server there's a command to handle this: WITHOUT_ARRAY_WRAPPER is there something similar in PostgreSQL? I'm looking for a better solution than resolve(results.rows[0])

There is probably some component at play that transforms a query result set to JSON. A result *set* can have several rows, which are presented as an array. The solution is not inside PostgreSQL, but in whatever software transforms the result to JSON. — Laurenz Albe, Apr 10 '19 at 09:45
@a_horse_with_no_name This is not the array I'm talking about, note that the array I'm trying to remove is the outer one. the array_agg() is used to create diet_list. — noam steiner, Apr 10 '19 at 09:47
As Laurenz already mentioned: that result isn't produced by Postgres, but by whatever tool you use to retrieve the data. Postgres returns the data in a tabular format, not as a JSON string. — , Apr 10 '19 at 09:49

score 1 · Answer 1 · answered Apr 10 '19 at 17:05

The connector you're using returns rows as array of objects just as in your result:

[
     {
      "id": 8,
      "first_name": "obos",
      "last_name": "fomo",
      "diet_list": [
          "vegan",
          "paleo"
      ]
     }
]

You can use destructuring assignment to extract the result like:

// this assigns the first entry of the row to employeeDiet, same as employeeDiet = row[0]
const [employeeDiet] = await employeeDA.getOneDiet(id);
console.log(employeeDiet)
// outputs
{
  "id": 8,
  "first_name": "obos",
  "last_name": "fomo",
  "diet_list": [
    "vegan",
    "paleo"
  ]
}

Also the connector you're using already provides Promise support so you don't need to write your own promise wrapper. You could do:

const getOneDiet = async (id) => {
  let p;
  try {
    const result = pool.query(`SELECT employee.id, employee.first_name, employee.last_name, array_agg(diet.description) AS diet_list
    FROM employee_diet
    INNER JOIN employee ON employee_diet.employee_id = employee.id
    INNER JOIN diet ON employee_diet.diet_id = diet.id
    WHERE employee.id = ${id}
    GROUP  BY 1`);
    // object destructuring
    const { rows } = result;
    [p] = rows;
  });
  } catch (e) {
    throw e;
  }
  return p;
};

const employeeDiet = await employeeDA.getOneDiet(id);
// employeeDiet is an object like you wanted

How to get an Object without wrapping of square brackets in PostgreSQL using Node.js and express?

1 Answers1