Search code examples
jsonpostgresqlddljsonb

Change column type from JSONB to JSON

I know that most of the users look for the jsonb type where the retrieval of values is way faster than json type. Still, I need the order in my key-values and so far I believe that the best way to achieve this is migrating my column type to json. I have tried the following:

ALTER TABLE table_name
ALTER COLUMN jsonb_colum TYPE json

and

ALTER TABLE table_name
ALTER COLUMN jsonb_colum TYPE json
USING jsonb_colum::json

In both cases I get this error:

ERROR: Operator class "jsonb_path_ops" does not accept data type json.

I have also tried to use the USING clause with an expression as the following:

ALTER TABLE table_name
ALTER COLUMN jsonb_column TYPE json
USING jsonb_column::to_json(jsonb_colum)

But with no luck either, getting the following error:

ERROR: Type "to_json" does not exist

Is there any workaround to make my wish come true?

I'm using PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) 64-bit

Solution

  • jsonb_path_ops is an operator class used for GIN indexes. Like:

    CREATE INDEX foo ON table_name USING gin (jsonb_column jsonb_path_ops);

    The existence of such an index would produce your first error message exactly. You need to drop any such index before you can change the column type - with your first, valid ALTER TABLE statement.

    However, the existence of such an index would indicate the need to support one or more of these jsonb operators: @>, @?, @@. If so, consider creating an expression index after the conversion to json to replace the old one. Like:

    CREATE INDEX foo ON table_name USING gin ((now_json_column::jsonb) jsonb_path_ops);

    (Parentheses required.)
    Then a query like this would still be supported (even if at slightly higher cost):

    SELECT * FROM table_name
WHERE  now_json_column::jsonb @> '{"some_key": "some_val"}';

    Related:

    Also, be wary of the missing basic equality and inequality operators for json. See: