Postgres: Create jsonb object with given set of keys and a default value

Question

I am using PostgreSQL 9.6. I have an array like ARRAY['a', 'b']::text[] which comes from application code and is transformed a bit in SQL, so I do not know its length in an application code.

In a table I have a field of type jsonb which I need to set to a json object, where keys are values from the given array and the values are all the same and equal to current timestamp, i.e

| id | my_field                                         |
---------------------------------------------------------
| 1  | {"a":"1544605046.21065", "b":"1544605046.21065"} |

I am trying to find an update query to perform this update, e.g. something like

UPDATE mytable 
SET my_field = some_function(ARRAY['a','b']::text[], EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE <some_condition>;

I was looking at jsonb_build_object function, which is likely to help me, if I could transform my array, interleaving its elements with current timestamp, however I did not find a way to do this.

Please note, that I am likely to have hundreds of thousands of records to update, therefore I am looking for a fast implementation.

I would be grateful for any advice on this matter.

S-Man · Accepted Answer · 2018-12-12 14:23:33Z

3

demo:db<>fiddle

UPDATE my_table
SET my_field = s.json_data
FROM (
    SELECT jsonb_object_agg(key, extract(epoch from current_timestamp)) as json_data
    FROM unnest(array['a', 'b']) as u(key)
) s
WHERE <some condition>

For using the array element as keys for the json object you need to separate them by unnest. This creates one row for each element.
Aggregating the rows with jsonb_object_agg(key, value). As key your are taking the column of array elements. As value the current_timestamp. This function aggregates into your expected syntax.
Putting this into a subquery allows you to do an update.

edited Dec 12, 2018 at 14:23

answered Dec 12, 2018 at 13:47

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: Create jsonb object with given set of keys and a default value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related