Postgres json_serialize() Function
newConvert JSON Values to Text or Binary Format
The json_serialize() function in PostgreSQL 17 provides a flexible way to convert JSON values into text or binary format. This function is particularly useful when you need to control the output format of JSON data or prepare it for transmission or storage in specific formats.
Use json_serialize() when you need to:
- Convert
JSONvalues to specific text formats - Transform
JSONinto binary representation - Ensure consistent
JSONstring formatting - Prepare
JSONdata for external systems or storage
Try it on Neon!
Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.
Function signature
The json_serialize() function uses the following syntax:
json_serialize(
expression -- Input JSON expression
[ FORMAT JSON [ ENCODING UTF8 ] ] -- Optional input format specification
[ RETURNING data_type -- Optional return type specification
[ FORMAT JSON [ ENCODING UTF8 ] ] ] -- Optional output format specification
) → text | byteaParameters:
expression: InputJSONvalue or expression to serializeFORMAT JSON: Explicitly specifiesJSONformat for input (optional)ENCODING UTF8: SpecifiesUTF8encoding for input/output (optional)RETURNING data_type: Specifies the desired output type (optional, defaults to text)
Example usage
Let's explore various ways to use the json_serialize() function with different inputs and output formats.
Basic serialization
-- Serialize a simple JSON object to text
SELECT json_serialize('{"name": "Alice", "age": 30}');# | json_serialize
--------------------------------
1 | {"name": "Alice", "age": 30}-- Serialize a JSON array
SELECT json_serialize('[1, 2, 3, "four", true, null]');# | json_serialize
----------------------------------
1 | [1, 2, 3, "four", true, null]Binary serialization
-- Convert JSON to binary format
SELECT json_serialize(
'{"id": 1, "data": "test"}'
RETURNING bytea
);# | json_serialize
--------------------------------------------------------
1 | \x7b226964223a20312c202264617461223a202274657374227dWorking with complex structures
-- Serialize nested JSON structures
SELECT json_serialize('{
"user": {
"name": "Bob",
"settings": {
"theme": "dark",
"notifications": true
},
"tags": ["admin", "active"]
}
}');# | json_serialize
---------------------------------------------------------------------------------------------------------------------
1 | { "user": { "name": "Bob", "settings": { "theme": "dark", "notifications": true }, "tags": ["admin", "active"] } }Comparison with json() function
While both json_serialize() and json() work with JSON data, they serve different purposes:
json()converts text or binary data intoJSONvaluesjson_serialize()convertsJSONvalues into text or binary formatjson()focuses on input validation (e.g.,WITH UNIQUEkeys)json_serialize()focuses on output format control
Think of them as complementary functions:
-- json() for input conversion
SELECT json('{"name": "Alice"}'); -- Text to JSON
-- json_serialize() for output conversion
SELECT json_serialize('{"name": "Alice"}'::json); -- JSON to TextCommon use cases
Data export preparation
-- Create a table with JSON data
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_data json
);
-- Insert sample data
INSERT INTO events (event_data) VALUES
('{"type": "login", "user_id": 123}'),
('{"type": "purchase", "amount": 99.99}');
-- Export data in specific format
SELECT id, json_serialize(event_data RETURNING text)
FROM events;Error handling
The function handles various error conditions:
-- Invalid JSON input (raises error)
SELECT json_serialize('{"invalid": }');ERROR: invalid input syntax for type json (SQLSTATE 22P02)