1

I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's result keys are unknow and have to be add to it if these keys are meet during the aggregate function. They can't be define on the query.

datetime             data
2018-05-06 18:17:00  {"speed":23.3,"orientation":1.3,"o2":75.2,"pm25":12.1}
2018-05-06 19:17:00  {"speed":20.3,"pm25":13.1}
2018-05-07 15:02:00  {"speed":21.3,"orientation":1.3,"pm10":72.2}

Imagine here we want the result aggregate by day

The result wanted :

day         data
2018-05-06  {"speed":21.8,"orientation":1.3,"o2":75.2,"pm25":12.6}
2018-05-07  {"speed":21.3,"orientation":1.3,"pm10":72.2}

The first two rows has been aggregate doing an average on recurrent keys and kept all of the existing keys.

1 Answer 1

1

I don't know how to do this, so I'll learn myself as we go!

Test table and data:

postgres=# CREATE TABLE testtable (
postgres(#     dt date,
postgres(#     data jsonb
postgres(# );
CREATE TABLE
postgres=# 
postgres=# INSERT INTO testtable 
postgres-# VALUES 
postgres-# ('2018-05-06 18:17:00','{"speed":23.3,"orientation":1.3,"o2":75.2,"pm25":12.1}'),
postgres-# ('2018-05-06 19:17:00','{"speed":20.3,"pm25":13.1}'),
postgres-# ('2018-05-07 15:02:00','{"speed":21.3,"orientation":1.3,"pm10":72.2}');
INSERT 0 3
postgres=#

First, we don't need the time component of the data, so we cast:

postgres=# select dt::date from testtable ;
     dt     
------------
 2018-05-06
 2018-05-06
 2018-05-07
(3 rows)

postgres=# 

Next, we want to decompose the JSON object to its individual elements:

postgres=# SELECT dt::date, jsonb_each_text(data)
FROM testtable;
     dt     |  jsonb_each_text  
------------+-------------------
 2018-05-06 | (o2,75.2)
 2018-05-06 | (pm25,12.1)
 2018-05-06 | (speed,23.3)
 2018-05-06 | (orientation,1.3)
 2018-05-06 | (pm25,13.1)
 2018-05-06 | (speed,20.3)
 2018-05-07 | (pm10,72.2)
 2018-05-07 | (speed,21.3)
 2018-05-07 | (orientation,1.3)
(9 rows)

postgres=# 

Now split out the key<>value pairs from the above:

postgres=# SELECT dt::date, jsondata.key, jsondata.value 
postgres-# FROM testtable, jsonb_each_text(data) as jsondata; 
     dt     |     key     | value 
------------+-------------+-------
 2018-05-06 | o2          | 75.2
 2018-05-06 | pm25        | 12.1
 2018-05-06 | speed       | 23.3
 2018-05-06 | orientation | 1.3
 2018-05-06 | pm25        | 13.1
 2018-05-06 | speed       | 20.3
 2018-05-07 | pm10        | 72.2
 2018-05-07 | speed       | 21.3
 2018-05-07 | orientation | 1.3
(9 rows)

postgres=#

Aggregate the above data:

postgres=# SELECT dt::date, jsondata.key, sum(jsondata.value::float) as total
postgres-# FROM testtable, jsonb_each_text(data) as jsondata
postgres-# Group by dt::date, jsondata.key; 
     dt     |     key     | total 
------------+-------------+-------
 2018-05-06 | o2          |  75.2
 2018-05-06 | speed       |  43.6
 2018-05-07 | orientation |   1.3
 2018-05-07 | speed       |  21.3
 2018-05-06 | orientation |   1.3
 2018-05-07 | pm10        |  72.2
 2018-05-06 | pm25        |  25.2
(7 rows)

postgres=# 

Now turn the above resultset into JSON:

postgres=# With sourcedata as ( 
postgres(# SELECT dt::date as dt, jsondata.key as key, sum(jsondata.value::float) as total
postgres(# FROM testtable, jsonb_each_text(data) as jsondata
postgres(# Group by dt::date, jsondata.key
postgres(# )
postgres-# Select dt, jsonb_object_agg(key, total)
postgres-# From sourcedata
postgres-# Group by dt; 
     dt     |                       jsonb_object_agg                        
------------+---------------------------------------------------------------
 2018-05-07 | {"pm10": 72.2, "speed": 21.3, "orientation": 1.3}
 2018-05-06 | {"o2": 75.2, "pm25": 25.2, "speed": 43.6, "orientation": 1.3}
(2 rows)

postgres=# 
2
  • Thanks ! I'm afraid of the execution time with 100k rows. I'll wait before accept it, if there is a gentle way to do this
    – Raphiki
    Commented Aug 16, 2018 at 12:51
  • I just tried it with some test data (180k rows) and it executed in less than a second
    – Philᵀᴹ
    Commented Aug 16, 2018 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.