JSONB Casting ​

JSONB can be cast to many different types. These casts can be separated into groups that share some similarities.

JSON Cast ​

Casting a JSONB expression to JSON gives back the JSON representation for that JSONB. The output is always valid JSON, or NULL if the JSON expression given was NULL.

SELECT '{"b":2,"a":1}'::JSONB::JSON;
-- {"a":1,"b":2}

SELECT '"string"'::JSONB::JSON;
--  "string"

SELECT 'null'::JSONB::JSON;
--  null

SELECT NULL::JSONB::JSON;
-- <NULL>

Extraction Casts ​

Extraction casts are intended for getting values out from leaf JSON values such as strings, numbers and booleans. The casts in this group are the SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, VARCHAR and BOOLEAN casts. These casts:

• Return NULL if the input JSONB is a SQL NULL, or if it is a leaf JSON null value.
• Return the JSON representation of the JSONB, except:
  • Strip the outer double quotes if the JSONB expression is a string.
  • Unescape any characters that previously had to be escaped if the JSONB expression is a string.

SELECT '{"b":2,"a":1}'::JSONB::VARCHAR;
--  {"a":1,"b":2}

SELECT '"string"'::JSONB::VARCHAR;
-- string

SELECT 'null'::JSONB::BOOLEAN;
-- <NULL>

SELECT NULL::JSONB::REAL;
-- <NULL>
 
SELECT '"84"'::JSONB::DECIMAL(4, 2);
--  84.00

BYTEA Cast ​

Casting to BYTEA returns the internal binary representation of the JSONB, or NULL if the expression is a SQL NULL value.

SELECT '"84"'::JSONB::BYTEA;
-- \x010000002500000038340000

There is no cast from BYTEA back to JSONB.