Converting types in JSONB

When working with JSONB data in Postgres, you might need to convert values between different types.

Consider this example of a table that stores information for a ticketing system. Most of the information is stored in a JSONB column, and we want to ensure consistent types: tags should always be an array, view_count should be an integer, and is_active should be a boolean.

CREATE TABLE tickets (
    id serial PRIMARY KEY,
    metadata jsonb
);

-- Initial data with inconsistent types
INSERT INTO tickets (metadata) VALUES
('{
    "title": "Fix API validation",
    "tags": "backend",
    "priority": 10,
    "assignee": "alice",
    "view_count": "156",
    "is_active": "true"
}'),
('{
    "title": "Update React components",
    "tags": ["frontend", "react"],
    "priority": "20",
    "assignee": "bob",
    "view_count": 238,
    "is_active": false
}');

String to array

UPDATE tickets
SET metadata = jsonb_set(
    metadata,
    '{tags}',
    CASE
        WHEN jsonb_typeof(metadata #> '{tags}') = 'string'
        THEN jsonb_build_array(metadata #> '{tags}')
        ELSE metadata #> '{tags}' -- Essential for preserving existing arrays
    END
);
-- UPDATE 2

SELECT id, metadata -> 'tags' AS tags FROM tickets;
--  id |           tags            
-------+--------------------------
--   1 | ["backend"]
--   2 | ["frontend", "react"]

String to number

UPDATE tickets
SET metadata = jsonb_set(
    metadata,
    '{priority}',
    to_jsonb((metadata #>> '{priority}')::integer)
);
-- UPDATE 2

SELECT id, metadata -> 'priority' AS priority FROM tickets;
--  id | priority 
-------+----------
--   1 | 10
--   2 | 20

String to boolean

UPDATE tickets
SET metadata = jsonb_set(
    metadata,
    '{is_active}',
    to_jsonb((metadata #>> '{is_active}')::boolean)
);
-- UPDATE 2

SELECT id, metadata -> 'is_active' AS is_active FROM tickets;
--  id | is_active 
-------+-----------
--   1 | true
--   2 | false

The #>> operator returns text (needed for casting), while #> returns JSONB.

For better performance in production environments, you should add WHERE clauses to target only the rows that need conversion. For example:

UPDATE tickets
SET metadata = jsonb_set(
    metadata,
    '{priority}',
    to_jsonb((metadata #>> '{priority}')::integer)
) WHERE jsonb_typeof(metadata #> '{priority}') != 'number';