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
CodePudding user response：
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:
CodePudding user response：
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