Most efficient way of transforming query result snakecase attributes to camelcase

user_7184007 0
Viewed 721

I'm looking for the most recommended (best practice) and most efficient way of transforming my postgresql query result from snake_case to camelCase before returning it in the api JSON response using postgresql and express.js

I know there are a few options, e.g. looping through the results and manually altering the names, using sql, etc. However, what would be the most optimal way?

user.js controller:

const getProfile = async (req, res, next) => {    
  try {
    const user = await db.query("SELECT first_name, middle_name, last_name, display_name FROM user_account WHERE id = $1", [req.user]);

    res.status(200).json({profile: user.rows[0]});
  }
  catch (err) {
    console.error(err.message);
    res.status(500).json("Server Error");
  }
};

Current Response:

"profile": {
    "first_name": "Joseph",
    "middle_name": "Robinette",
    "last_name": "Biden",
    "display_name": "Joe"
}

Expected Response:

"profile": {
    "firstName": "Joseph",
    "middleName": "Robinette",
    "lastName": "Biden",
    "displayName": "Joe"
}
node-postgres express node.js postgresql
2 Answers

Both approaches are fine. You can do it either in SQL:

const {rows} = await db.query(
  `SELECT
    first_name AS "firstName",
    middle_name AS "middleName",
    last_name AS "lastName",
    display_name AS "displayName"
  FROM user_account
  WHERE id = $1`,
  [req.user]
);

res.status(200).json({profile: rows[0]});

or in JS:

const { rows: [user] } = await db.query("SELECT first_name, middle_name, last_name, display_name FROM user_account WHERE id = $1", [req.user]);

res.status(200).json({
  profile: {
    firstName: user.first_name,
    middleName: user.middle_name,
    lastName: user.last_name,
    displayName: user.display_name,
  },
});

The second would be preferable if you build more complex objects or do transformations on the field values.

A third alternative would be not doing this on your own, but using an ORM that has this feature built-in.

user_10485720

You can camelCase some word using this sentence:

select string_agg(palabra,'') from (
  select case when row_number>1 then upper(substr(palabra,1,1))||substr(palabra,2) else palabra end
    from (
      select *, row_number() over () from regexp_split_to_table('uno_dos_tres','_') palabra
    ) x
) x

Running example: http://sqlfiddle.com/#!17/56f78/1033

Then you can use that to apply to an json object:

select json_object_agg(key, value) from (
  select (
    select string_agg(palabra,'') from (
      select case when row_number>1 then upper(substr(palabra,1,1))||substr(palabra,2) else palabra end
        from (
          select *, row_number() over () from regexp_split_to_table(key,'_') palabra
        ) x
    ) x
) as key, value
  from json_each('{
    "first_name": "Joseph",
    "middle_name": "Robinette",
    "last_name": "Biden",
    "display_name": "Joe",
    "id": "do_not_change"
  }'::json) rows
) pairs

Running example: http://sqlfiddle.com/#!17/56f78/1035

user_52032710
Related