0

Introduction

I created a toy example to extract the problem into a smaller part. Here is the schema definition with two tables users and events, users can have multiple events:

create table users (
    id int, name text, history jsonb
);
insert into users(id, name) values (1, 'Mike'), (2, 'Jake'), (3, 'Toots');


create table events (
    id serial, user_id int, external_id int, type text, timestamp timestamp
);

# Generate test data
insert into events (external_id, user_id, type, timestamp) 
    select x % 5 + 1, x % 3 + 1, 'random', NOW() - '1 day'::interval * (random()::int * 100) from generate_series(1, 3000000) as x;

With the actual data, the users table would be lower volume table less than ~1M records and events table quite high ~25M records.

Problem

Update users table history column which would be an aggregated result of the events table as jsonb. The events should be grouped together by external_id, user_id based on that the object with min(timestamp) and max(timestamp) would be great.

History example: history: [{start: '2018-12-12', end: '2018-12-20', external_id: 1}, {start: '2018-11-12', end: '2018-11-20', external_id: 2}]

My example, the grouping part seems to be not correct as an only single object is returned in the subquery. Also by performance wise, this is not the best solution.

 update users
    set history = e.history
    from (
        select 
            user_id,
            json_build_array(
                json_build_object(
                  'start', MIN(timestamp),
                  'end', MAX(timestamp),
                  'external_id', external_id,
                  'is_fetched', true
                )::jsonb) history
        from events 
        group by external_id, user_id
    ) e 
    where users.id = e.user_id
1

I'm not sure if this is the solution you're looking for, but IMHO you should get the aggregated values and then build the array.

WITH t AS 
(
  SELECT
      user_id,
      jsonb_build_object('start', MIN(timestamp), 
                         'end', MAX(timestamp),
                         'external_id', external_id,
                         'is_fetched', true) ev
  FROM 
      events
  GROUP BY
      user_id,
      external_id
), t2 AS
(
  SELECT
    user_id,
    jsonb_agg(ev) history
  FROM
    t
  GROUP BY
    user_id
)
UPDATE users AS usr
SET    history = t2.history
FROM   t2
WHERE  usr.id = t2.user_id;
SELECT * FROM users;
id | name  | history                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-: | :---- | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1 | Mike  | [{"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 1}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 5}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 3}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 4}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 2}]
 2 | Jake  | [{"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 3}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 2}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 4}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 1}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 5}]
 3 | Toots | [{"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 3}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 1}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 4}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 2}, {"end": "2019-01-26T17:14:37.349739", "start": "2018-10-18T17:14:37.349739", "is_fetched": true, "external_id": 5}]

db<>fiddle here

  • Ty for correcting yes the extra step was missing :y: – jurka Jan 26 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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