Converting JSON to JSONB in PostgreSQL

Bjorn Krols

Bjorn Krols

Published on
18 January 2022

Converting a column from json to jsonb and vice versa is fairly straightforward in PostgreSQL.

Implicit casting is supported, a USING clause does not need to be provided.

Converting JSON to JSONB

ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE json;

Converting JSONB to JSON

ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE jsonb;

Example

-- Create a table with a json column named "data"
CREATE TABLE my_table(data json);

-- Insert a row
INSERT INTO my_table(data) VALUES ('{"message": "Hello, World!"}');

-- Convert the column from json to jsonb
ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE jsonb;

SELECT * FROM my_table; -- {"message":"Hello, World!"}

-- Convert the column back from jsonb to json
ALTER TABLE my_table
    ALTER COLUMN data
        SET DATA TYPE json;

SELECT * FROM my_table; -- {"message":"Hello, World!"}

Subscribe to our newsletter

The latest news, articles, and resources, sent to your inbox weekly.

More like this