0

I have a table in Postgres 13 that looks something like:

Column | Type | 
----------------
id       bigint
ts_begin timestamp with time zone
ts_end   timestamp with time zone
data     jsonb not null '{}'::jsonb

Unfortunately the data is an array of key value pairs of with a UUID as the key and a timestamp as the value. E.g.

{"20220101234-deadbeef": "2021-11-18T09:09:09-05:00", "2022010234-cafefeed": "2021-11-18T08:08:08-05:00", ... }

My task to try and get the min and max values from that JSON object as update the ts_begin and ts_end values for each row.

Because the keys are not consistent, I think that the -> or ->> operators are not going to work for this task. Is that correct?

Is there a way to use json_each or json_each_text (or some other technique in SQL) to accomplish this task?

GSP
  • 3,763
  • 3
  • 32
  • 54
  • Does this help: https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4 ? – pringi Feb 18 '22 at 17:47
  • Thank you for the response, but if I'm understanding correctly that's about updating a value within a JSON object. I'm trying to set a column value from the aggregated data in the JSON object. – GSP Feb 18 '22 at 18:19

1 Answers1

0

I think I have an answer using the jsonb_each_text function and then a cross join adapted from the answer here: Querying nested json based on wildcard key

update my_table as me
set ts_begin = (select min(cast(value as timestamp with time zone)) as min 
                from my_table me_too 
                cross join jsonb_each_text(data) 
                where me.id = me_too.id),
    ts_end = (select max(cast(value as timestamp with time zone)) as max 
              from my_table me_too 
              cross join jsonb_each_text(data) 
              where me.id = me_too.id);

To break it into pieces, the jsonb_each_text builds a table with two columns key and value which can easily be aggregated with the min and max function. Using a cross join with a where clause that limits it to the current row ( me.id = me_too.id ) allows the correct values to be set.

EDIT:

I think this can be simplified to just:

update my_table
set ts_begin = (select min(cast(value as timestamp with time zone) as min 
                from jsonb_each_text(data)),
    ts_end = (select max(cast(value as timestamp with time zone) as max 
              from jsonb_each_text(data));
GSP
  • 3,763
  • 3
  • 32
  • 54
  • 1
    You can simplify this even further `set (ts_begin, ts_end) = (select min(...), max(...) from ...)` –  Feb 18 '22 at 20:43