Altering JSON column to INTEGER[] ARRAY

Question

I have a JSON column that contains an array of integers. I am trying to convert it to an INTEGER[] column, but I'm running into casting errors.

Here's my final alter version:

ALTER TABLE namespace_list ALTER COLUMN namespace_ids TYPE INTEGER[] USING string_to_array(namespace_ids::integer[], ',');

However, this throws this error: ERROR: cannot cast type json to integer[]

Any ideas how I can abouts this conversion? I've tried several things but I end up with the same error. Seems like going json --> string --> --> array does not work. What are my options?

Edit:

Table definition:

db => \d+ namespace_list;

Column         |   Type   |  Table "kiwi.namespace_list" Modifiers|
---------------+----------+--------------------------------------+
id             | integer  | not null default nextval('namespace_list_id_seq'::regclass)
namespace_ids  | json     | not null default '[]'::json

Sample data:

id | namespace_ids | 
-------------------+
1 | [1,2,3]        |

Answer 1

Assuming no invalid characters in your array.

IN Postgres 9.4 or later use a conversion function as outlined here:

• How to turn JSON array into Postgres array?

CREATE OR REPLACE FUNCTION json_arr2int_arr(_js json)
  RETURNS int[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js)::int)';

ALTER TABLE namespace_list
  ALTER COLUMN namespace_ids DROP DEFAULT
, ALTER COLUMN namespace_ids TYPE int[] USING json_arr2int_arr(namespace_ids);

db<>fiddle here

---

For Postgres 9.3 or older:

ALTER TABLE namespace_list
ALTER COLUMN namespace_ids TYPE INTEGER[]
      USING translate(namespace_ids::text, '[]','{}')::int[];

The specific difficulty is that you cannot have a subquery expression in the USING clause, so unnesting & re-aggregating is not an option:

SELECT ARRAY(SELECT(json_array_elements(json_col)::text::int))
FROM   namespace_list;

Therefore, I resort to string manipulation to produce a valid string constant for an integer array and cast it.

column DEFAULT

If there is a column default like DEFAULT '[]'::json in your actual table definition added later, drop it before you do the above. You can add a new DEFAULT afterwards if you need one. Best in the same transaction (or even command):

ALTER TABLE namespace_list
   ALTER COLUMN namespace_ids DROP DEFAULT
,  ALTER COLUMN namespace_ids TYPE INT[] USING translate(namespace_ids::text, '[]','{}')::int[]
,  ALTER COLUMN namespace_ids SET DEFAULT '{}';

db<>fiddle here
_{Old sqlfiddle}