Question 47081 in Debian Question posted in
The oficial documentation can be found here.

Change column type from JSONB to JSON – Debian

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

Tags:

3

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 0

    A newly made json formatted column can be updated in place from the existing jsonb column using a query and selecting out the keys in the order desired. An example table, dataset,and update query is below.

    create table crayons (
id serial,
color_json json,
color_jsonb jsonb,
primary key (id))

ugautil=> d crayons
Table "public.crayons"
Column    |  Type   | Collation | Nullable |               Default             
-------------+---------+-----------+----------+-----------------------------------
id          | integer |           | not null | nextval('crayons_id_seq'::regclass)
color_json  | json    |           |          | 
color_jsonb | jsonb   |           |          | 
Indexes:
"crayons_pkey" PRIMARY KEY, btree (id)

    The column color_json will preserve the same text as in the original data
    file. The column color_jsonb will not preserve key order,
    duplicate keys, additional spaces, etc.

    The first 5 rows of data:

    ugautil=> select color_json from crayons order by id limit 5;
-----------------------------------------------------------
{"hex":"#EFDECD","name":"Almond","rgb":"(239, 222, 205)"}
{"hex":"#CD9575","name":"Antique Brass","rgb":"(205, 149, 117)"}
{"hex":"#FDD9B5","name":"Apricot","rgb":"(253, 217, 181)"}
{"hex":"#78DBE2","name":"Aquamarine","rgb":"(120, 219, 226)"}
{"hex":"#87A96B","name":"Asparagus","rgb":"(135, 169, 107)"}

    When we select the same data from the jsonb column, notice it changed the key ordering.
    Name comes after rgb

    ugautil=> select color_jsonb from crayons order by id limit 5;
-------------------------------------------------------------
{"hex": "#EFDECD", "rgb": "(239, 222, 205)", "name": "Almond"}
{"hex": "#CD9575", "rgb": "(205, 149, 117)", "name": "Antique Brass"}
{"hex": "#FDD9B5", "rgb": "(253, 217, 181)", "name": "Apricot"}
{"hex": "#78DBE2", "rgb": "(120, 219, 226)", "name": "Aquamarine"}
{"hex": "#87A96B", "rgb": "(135, 169, 107)", "name": "Asparagus"}

    We can use the function to_json() to convert the column color_jsonb back to json format, but it does not
    return the original key ordering.

    ugautil=> select to_json(color_jsonb) from crayons limit 5;
----------------------------------------------------------------
{"hex": "#EFDECD", "rgb": "(239, 222, 205)", "name": "Almond"}
{"hex": "#CD9575", "rgb": "(205, 149, 117)", "name": "Antique Brass"}
{"hex": "#FDD9B5", "rgb": "(253, 217, 181)", "name": "Apricot"}
{"hex": "#78DBE2", "rgb": "(120, 219, 226)", "name": "Aquamarine"}
{"hex": "#87A96B", "rgb": "(135, 169, 107)", "name": "Asparagus"}

    However we can pick out the individual keys and format them.

    ugautil=>   select format('{"hex": %s,"name": %s, "rgb": %s}', 
color_jsonb->'hex',color_jsonb->'name', color_jsonb->'rgb') 
from crayons limit 5;
----------------------------------------------------------------
{"hex": "#EFDECD","name": "Almond", "rgb": "(239, 222, 205)"}
{"hex": "#CD9575","name": "Antique Brass", "rgb": "(205, 149, 117)"}
{"hex": "#FDD9B5","name": "Apricot", "rgb": "(253, 217, 181)"}
{"hex": "#78DBE2","name": "Aquamarine", "rgb": "(120, 219, 226)"}
{"hex": "#87A96B","name": "Asparagus", "rgb": "(135, 169, 107)"}

    Alter the table to create a new column to hold the json type you want

    ugautil=> alter table crayons add column color_json2 json;

    Update the table in place with a query that selects from the jsonb
    column, and formats the keys in the order you want, and uses that
    to update the new color_json2 column.

    with subquery as (
select id,
format('{"hex": %s,"name": %s, "rgb": %s}', 
color_jsonb->'hex',color_jsonb->'name', 
color_jsonb->'rgb') as "color_json2_fmt"
from crayons
)
update crayons
set color_json2 = subquery.color_json2_fmt::json
from subquery
where crayons.id = subquery.id;


ugautil=> select color_json2 from crayons limit 5;
color_json2                              
---------------------------------------------------------------
{"hex": "#EFDECD","name": "Almond", "rgb": "(239, 222, 205)"}
{"hex": "#CD9575","name": "Antique Brass", "rgb": "(205, 149, 117)"}
{"hex": "#FDD9B5","name": "Apricot", "rgb": "(253, 217, 181)"}
{"hex": "#78DBE2","name": "Aquamarine", "rgb": "(120, 219, 226)"}
{"hex": "#87A96B","name": "Asparagus", "rgb": "(135, 169, 107)"}

    Explanation of JSONB introduced by PostgreSQL
    updating table rows in postgres using subquery

    Login or Signup to reply.
  3. 0

    Please use below query for alter existing column in postgresql for text to jsonb

    ALTER TABLE schema_name.table_name
ALTER COLUMN column_name TYPE jsonb USING column_name::jsonb;

    In above query column_name must be shown highlighted if you using pgAdmin4. Otherwise you got error when run this query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search