Formatting JSON output using pg-promise, encapsulating foreign keys as objects

Question

I am building a very simple REST platform using Node.JS and PostgreSQL, using pg-promise to access the database. My goal is to create an incredibly simple ticketing system with users and tickets. At this point I simply want to be able to query the server for a list of all tickets.

My database consists of two tables in the following format:

CREATE TABLE people (
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR(128)
);

CREATE TABLE tickets (
  ID SERIAL PRIMARY KEY,
  SUBMITTED_BY int4 REFERENCES people(ID),
  TITLE VARCHAR(128)

As you can see there is a single foreign key pointing tickets to persons. I have populated my database with the following data:

coop=> SELECT * FROM people;
 id |   name   
----+----------
  1 | John Doe
(1 row)

coop=> SELECT * FROM tickets;
 id | submitted_by |      title       
----+--------------+------------------
  1 |            1 | My first ticket!
(1 row)

And I use pg-promise with the following query function to render a JSON response:

// GET ALL query function
function getAllTickets(req, res, next) {
  db.any('select * from tickets left join people on tickets.submitted_by = people.id;')
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ALL tickets'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

The function works, I can retrieve JSON output in the following format:

:: GET /api/tickets
    {"status":"success","data":[{"id":1,"submitted_by":1,"title":"My first ticket!","name":"John Doe"}],"message":"Retrieved ALL tickets"}

However this is not the format I want. I want to encapsulate the 'persons' object within the 'tickets' object, replacing the foreign key as follows:

{"status":"success","data":[{"id":1,submitted_by: {"id": 1, "name":"John Doe"},"title":"My first ticket!"}],"message":"Retrieved ALL tickets"}

The reason I want to do this so so I can easily consume this API from an Angular2 service and translate both ticket and person into object in my component. However I have absolutely no idea how to go about doing this and the pg-promise documentation has not been helpful.

I have been web-programming with Django for the past year but since I switched to Node.JS I feel like a complete novice. Could somebody offer me a hand or point me in the right direction?

score 1 · Accepted Answer · edited May 23 '17 at 11:46

pg-promise executes queries directly, and provides data exactly as it is sent by the server. It does not do any additional transformation, as it is not an ORM.

If you want your data transformed the way you described, then just do it yourself, for it is trivial.

The only way pg-promise would be able to transform data in the way you described, if there were a separation on the query level, i.e. if there were a parent query + child query instead of a single join query. For this kind of example see Get a parents + children tree with pg-promise.

It is however not something I would advise here, because a single join query is much more efficient. You should simply transform the data on your end once you receive it.

There are, of course, some ORM-s out there that can do it for you, if you decide to go that way, which would be a very different approach, away from direct query execution.

vitaly-t, I don't suppose you could give me a quick example of how to use string formatting to transform the result. That would be incredibly helpful. — Fox, Aug 16 '16 at 02:03
@Fox the best example can be found here: https://stackoverflow.com/questions/39805736/get-join-table-as-array-of-results-with-postgresql-nodejs — vitaly-t, Aug 05 '17 at 19:41

Formatting JSON output using pg-promise, encapsulating foreign keys as objects

1 Answers1