Postgres grouping query result by row

user_15736538 0
Viewed 62

Im new to Postgres and using it now for the first time with nodejs. I got a table with sensor readings (temperature in this case). The Table is something like:

sensorID timestamp temperature
1 8:22pm 22C
2 8:23pm 21C
3 8:25pm 24C
2 8:26pm 27C
2 8:28pm 19C
1 8:31pm 28C

Is there a way to create a single query to get the data to nodejs formatted like this:

[
   {sensorID: 1,
   data: [
      {timestamp:8:22pm, temperature:22C},
      {timestamp:8:31pm, temperature:28C}

   ]},
   {sensorID: 2,
   data: [
      {timestamp:8:23pm, temperature:21C},
      {timestamp:8:26pm, temperature:27C},
      {timestamp:8:28pm, temperature:19C}
   ]},
   {sensorID: 3,
   data: [
      {timestamp:8:25pm, temperature:24C}

   ]}
]
express node.js postgresql sql
1 Answers

Welcome to SO.

To build and aggregate json objects in PostgreSQL (via SQL) you can use the functions jsonb_build_object and jsonb_agg:

WITH j AS (
  SELECT 
    sensorid,
    jsonb_build_object(
      'data',
       jsonb_agg(
         jsonb_build_object(
           'timestamp',timestamp,
           'temperature',temperature)))
  FROM t
  GROUP BY sensorid
  ORDER BY sensorid) 
SELECT jsonb_agg(j) FROM j;

Demo: db<>fiddle

user_22753880
Related