Combining nested JSONB to get average value-CodePudding

Currently i'm having a dataset (jsonb) like this:

create table tbl(timebucket time, data jsonb);
insert into tbl values
('18:18:00',
  '[
  {"id1": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id1": 
    {
      "energySensor": { "value": 4 }
    }
  },
  {"id2": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id2": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id3": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id3": 
    {
      "energySensor": { "value": 2 }
    }
  }
  ]'
),
('18:20:00',
  '[
  {"id1": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id1": 
    {
      "energySensor": { "value": 4 }
    }
  },
  {"id2": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id2": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id3": 
    {
      "energySensor": { "value": 2 }
    }
  },
  {"id3": 
    {
      "energySensor": { "value": 2 }
    }
  }
  ]'
)

The id's are actually UUID-s and are represented as machines in this context.

I'm also using timescaleDB extension to use a timebucket.

In this example I use data with interval of "2 minutes". This returns 2 sensor readings for each machine in a minute.

What i would like to get now is to query this data via postgresql that it would return the machine with its average for the "energySensor" value.

The final result should be something like this =>

[{
  "timebucket": "18:18:00",
  "data": {
      "id1": {
          "energySensor": { "value": 3 }
       },
       "id2": {
          "energySensor": { "value": 2 }
       },
       "id3": {
          "energySensor": { "value": 2 }
       },
   }
},
{
  "timebucket": "18:20:00",
  "data": {
      "id1": {
          "energySensor": { "value": 3 }
       },
       "id2": {
          "energySensor": { "value": 2 }
       },
       "id3": {
          "energySensor": { "value": 2 }
       },
   }
}]

I currently have no good solution as to do this..

CodePudding user response：

One option to solve this problem involves:

transforming the jsonb to a table
computing the averages
getting back the jsonb

The process of extracting the information from the jsonb data can be carried out by applying on each row (CROSS JOIN LATERAL) using:

JSONB_ARRAY_ELEMENTS, which expands the top-level JSON array into a set of JSON values
JSONB_EACH, which expands the top-level JSON object into a set of key/value pairs

Hence the average gets computed with the AVG aggregate function.

Once done, we can rebuild the JSONB data using:

JSONB_BUILD_OBJECT, which builds a JSONB object out of a list of arguments
JSONB_AGG, which aggregates multiple JSONB object into a single JSON.

WITH cte AS (
    SELECT timebucket,
           idval,
           sensor,
           AVG((json3->>'value')::int)::int AS average
    FROM tbl
    CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(data) AS value1(json1)
    CROSS JOIN LATERAL JSONB_EACH(json1)          AS value2(idval, json2)
    CROSS JOIN LATERAL JSONB_EACH(json2)          AS value3(sensor, json3)
    GROUP BY timebucket,
             idval,
             sensor
    ORDER BY timebucket, 
             idval,
             sensor
), jsons AS ( 
    SELECT JSONB_BUILD_OBJECT('timebucket', timebucket, 'data',
               JSONB_AGG(
                   JSONB_BUILD_OBJECT(idval,
                       JSONB_BUILD_OBJECT(sensor, 
                           JSONB_BUILD_OBJECT('value', average)))))
    FROM cte
    GROUP BY timebucket
)       
SELECT JSONB_AGG(jsonb_build_object)
FROM jsons

Check the demo here.