CODEWITHSUNDEEP
postgresqljsonb
Karpalypy
Karpalypy

Reputation: 33

postgresql jsonb - from list of integers to list of Objects

I have a question regarding jsonb in postgresql.

I have a table that has a column of type jsonb, where I store a list of integers.

For example list_integers column: [1, 2, 3, 4]

I want to add a new column in this table, and insert in this column the same IDs, but the form would be list of objects, where the ID field corresponds to the integer.

For example list_ids column: [{"id": 1}, {"id": 2}, {"id": 3}, {"id": 4}]

What would be the best way to do this?

Upvotes: 1

Views: 55

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

To transform:

test=> SELECT jsonb_agg(jsonb_build_object('id', id))
test-> FROM   jsonb_array_elements(jsonb '[1, 2, 3, 4]') id;
                  jsonb_agg                   
----------------------------------------------
 [{"id": 1}, {"id": 2}, {"id": 3}, {"id": 4}]
(1 row)```

Upvotes: 1

Related Questions