Change column type from JSONB to JSON

Question

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

Just do: `ALTER TABLE table_name ALTER COLUMN jsonb_colum TYPE json`, no `USING`. — Adrian Klaver, Oct 11 '21 at 20:14
Just updated my question, doing so gives me the same error @AdrianKlaver — Emipad, Oct 11 '21 at 20:30
What version of Postgres? To your question add the definition of the table. What happens if you do `select jsonb_column::json from table_name`? — Adrian Klaver, Oct 11 '21 at 20:40
Do you have any index on that column? You need to drop all of them first — , Oct 11 '21 at 20:44
Re @AdrianKlaver: I'm using `PostgreSQL 13.3` and if I execute the statement you gave, it works! which seems odd to me. — Emipad, Oct 11 '21 at 21:58
I thought you said earlier it did not work? Did you drop the indexes? — Adrian Klaver, Oct 11 '21 at 23:22

Erwin Brandstetter · Accepted Answer · 2021-10-12T15:39:30.047

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"}';

Index for finding an element in a JSON array

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

How to query a json column for empty objects?

score 1 · Answer 2 · answered Oct 12 '21 at 02:17

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

M Singh Karnawat · Answer 3 · 2022-04-12T09:59:58.963

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.

edited Apr 12 '22 at 09:59

answered Apr 12 '22 at 09:48

M Singh Karnawat

634
7
8

Change column type from JSONB to JSON

3 Answers3