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:

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}

   ]}
]

CodePudding user response：

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

CodePudding user response：

I used group by and array_agg to list all the timestamps and temperatures per orderid

select sensorid
      ,array_agg ('timestamp:'|| ' ' || timestamp || ', ' || 'temperature:' || ' ' || temperature order by timestamp) as data
from t
group by sensorid
order by sensorid

Fiddle