How do I return columns in timeseries data as arrays

Question

I am using postgres and timescaledb to record data that will be used for dashboards/charting.

I have no issues getting the data I need I'm just not sure if I'm doing it the most efficient way.

Say I have this query

   SELECT time, queued_calls, active_calls 
   FROM call_data  ​
   ​ORDER BY time DESC LIMIT 100;

My front end receives this data for charting like such.

I feel like this is very inefficient by repeating the column name for each value.

Would it be better to send the data in a more efficient way like each column as an array of data like such.

{
time: [...], 
queued_calls: [...], 
active_calls: [...]
}

I guess my question is, should I be restructuring my query so the column data is in arrays somehow or is this something I should be doing after the query on the server before sending it to the client?

-- Update -- Additional Information

I'm using Node.js with Express and Sequelize as the ORM, however in this case I'm just executing a raw query via Sequelize.

The charting library I'm using on the front end also takes the series data as arrays so I was trying to kill two birds with one stone.

Frontend chart data format:

xaxis:{
  categories: [...time]
}
series:[
   {name: "Queued Calls", data: [...queued_calls]},
   {name: "Active Calls", data: [...active_calls]}
]  

Backend code:

async function getLocationData(locationId) {
  return await db.sequelize.query(
    'SELECT time, queued_calls, active_calls FROM location_data WHERE location_id = :locationId ORDER BY time DESC LIMIT 100;',
    {
      replacements: { locationId },
      type: QueryTypes.SELECT,
    }
  );
}

...

app.get('/locationData/:locationId', async (req, res) => {
  try {
    const { locationId } = req.params;
    const results = await getLocationData(parseInt(locationId));
    res.send(results);
  } catch (e) {
    console.log('Error getting data', e);
  }
});

Answer 1

Maybe you're looking for array aggregation?

Let me see if I follow the idea:

create table call_data (time timestamp, queued_calls integer, active_calls integer);
CREATE TABLE

I skipped the location_id just to simplify here.

Inserting some data:

tsdb=> insert into call_data values ('2021-03-03 01:00', 10, 20);
INSERT 0 1
tsdb=> insert into call_data values ('2021-03-03 02:00', 11, 22);
INSERT 0 1
tsdb=> insert into call_data values ('2021-03-03 03:00', 12, 25);
INSERT 0 1

And now check the data:

SELECT time, queued_calls, active_calls FROM call_data;
        time         | queued_calls | active_calls
---------------------+--------------+--------------
 2021-03-03 01:00:00 |           10 |           20
 2021-03-03 02:00:00 |           11 |           22
 2021-03-03 03:00:00 |           12 |           25
(3 rows)

And if you want to get only the dates you'll have:

 SELECT time::date, queued_calls, active_calls FROM call_data;
    time    | queued_calls | active_calls
------------+--------------+--------------
 2021-03-03 |           10 |           20
 2021-03-03 |           11 |           22
 2021-03-03 |           12 |           25
(3 rows)

but still not grouping, so, you can use group by combined with array_agg for it:

SELECT time::date, array_agg(queued_calls), array_agg(active_calls) FROM call_data group by time::date;
    time    | array_agg  | array_agg
------------+------------+------------
 2021-03-03 | {10,11,12} | {20,22,25}

Answer 2

If the server is compressing data as it sends it won't make much difference over the network layer if you send the data in the array structure you're thinking of.

If you use the array structure you're thinking of you're breaking one of the benefits of JSON - structure with data. You might gain some speed increase but if you want to see the active calls for a time you'd have to have the correct index - and open the possibilty of index errors.

I recommend leaving the data as it is.