1

I'm not sure how I should go about converting int[] to json[], here's what I've tried so far:

db=# alter table product alter column images type json[] using images::json[];
ERROR:  cannot cast type integer[] to json[]
LINE 1: ...roduct alter column images type json[] using images::json[];
                                                              ^
-- I thought converting to text[] first would help
db=# alter table product alter column images type text[] using images::text[];
ALTER TABLE

db=# alter table product alter column images type json[] using images::json[];
ERROR:  could not identify a comparison function for type json


-- Going back to int[]
db=# alter table product alter column images type int[] using images::integer[];
ALTER TABLE

-- Trying some json functions to help conversion
db=# alter table product alter column images type json[] using array_agg(array_to_json(images))::json[];
ERROR:  aggregate functions are not allowed in transform expressions
LINE 1: ...ble product alter column images type json[] using array_agg(...

-- I thought a subquery would prevent the above error
db=# ALTER TABLE product
ALTER COLUMN images TYPE json[]
USING (
  SELECT array_agg(array_to_json(images))::json[]
  FROM product
);
ERROR:  cannot use subquery in transform expression
LINE 3: USING (
              ^

I've read these links:

https://stackoverflow.com/questions/37686187/how-to-convert-json-array-into-postgres-int-array-in-postgres-9-3

How to turn JSON array into Postgres array?

Convert postgres array to jsonb

Shayan
  • 111
  • 3
  • 2
    jsonb[] or json[] almost never makes sense. You should convert it into a jsonb column that contains a JSON array –  Mar 29 '23 at 09:51
  • @a_horse_with_no_name I think you're right, I may just have to add an extra key {"images": the_array}. I have come across many errors using even int[] or text[] when inserting from the Peewee ORM. Might just forget that array fields exist at all – Shayan Mar 29 '23 at 14:53
  • 1
    Why would you need an additional key? If you have an int array now ('{1,2,3}') , you'll have a JSON array afterwards [1, 2, 3]. But avoiding array completely is not a bad strategy. –  Mar 29 '23 at 16:02

0 Answers0