The json() function in PostgreSQL 17 provides a robust way to convert text or binary data into JSON values. This new function offers enhanced control over JSON parsing, including options for handling duplicate keys and encoding specifications.

Use json() when you need to:

  • Convert text strings into JSON values
  • Parse UTF8-encoded binary data as JSON
  • Validate JSON structure during conversion
  • Control handling of duplicate object keys

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.

Sign Up

Function signature

The json() function uses the following syntax:

json(  expression -- Input text or bytea  [ FORMAT JSON [ ENCODING UTF8 ]] -- Optional format specification  [ { WITH | WITHOUT } UNIQUE [ KEYS ]] -- Optional duplicate key handling ) → json

Parameters:

  • expression: Input text or bytea string to convert
  • FORMAT JSON: Explicitly specifies JSON format (optional)
  • ENCODING UTF8: Specifies UTF8 encoding for bytea input (optional)
  • WITH|WITHOUT UNIQUE [KEYS]: Controls duplicate key handling (optional)

Example usage

Let's explore various ways to use the json() function with different inputs and options.

Basic JSON conversion

-- Convert a simple string to JSON SELECT json('{"name": "Alice", "age": 30}');
# | json -------------------------------- 1 | {"name": "Alice", "age": 30}
-- Convert a JSON array SELECT json('[1, 2, 3, "four", true, null]');
# | json -------------------------------- 1 | [1, 2, 3, "four", true, null]
-- Convert nested JSON structures SELECT json('{  "user": {  "name": "Bob",  "contacts": {  "email": "bob@example.com",  "phone": "+1-555-0123"  }  },  "active": true }');
# | json --------------------------------------------------------------------------------------------------------------------- 1 | { "user": { "name": "Bob", "contacts": { "email": "bob@example.com", "phone": "+1-555-0123" } }, "active": true }

Handling duplicate keys

-- Without UNIQUE keys (allows duplicates) SELECT json('{"a": 1, "b": 2, "a": 3}' WITHOUT UNIQUE);
# | json ---------------------------- 1 | {"a": 1, "b": 2, "a": 3}
-- With UNIQUE keys SELECT json('{"a": 1, "b": 2, "c": 3}' WITH UNIQUE);
# | json ---------------------------- 1 | {"a": 1, "b": 2, "c": 3}
-- This will raise an error due to duplicate 'a' key SELECT json('{"a": 1, "b": 2, "a": 3}' WITH UNIQUE);
ERROR: duplicate JSON object key value (SQLSTATE 22030)

Working with binary data

-- Convert UTF8-encoded bytea to JSON SELECT json(  '\x7b226e616d65223a22416c696365227d'::bytea  FORMAT JSON  ENCODING UTF8 );
# | json --------------------- 1 | {"name": "Alice"}
-- Convert bytea with explicit format and uniqueness check SELECT json(  '\x7b226964223a312c226e616d65223a22426f62227d'::bytea  FORMAT JSON  ENCODING UTF8  WITH UNIQUE );
# | json ---------------------------- 1 | {"id": 1, "name": "Bob"}

Combining with other JSON functions:

-- Convert and extract SELECT json('{"users": [{"id": 1}, {"id": 2}]}')->'users'->0->>'id' AS user_id;
# | user_id ----------- 1 | 1
-- Convert and check structure SELECT json_typeof(json('{"a": [1,2,3]}')->'a');
# | json_typeof --------------- 1 | array

Error handling

The json() function performs validation during conversion and can raise several types of errors:

-- Invalid JSON syntax (raises error) SELECT json('{"name": "Alice" "age": 30}');
ERROR: invalid input syntax for type json (SQLSTATE 22P02)
-- Invalid UTF8 encoding (raises error) SELECT json('\xFFFFFFFF'::bytea FORMAT JSON ENCODING UTF8);
ERROR: invalid byte sequence for encoding "UTF8": 0xff (SQLSTATE 22021)

Common use cases

Data validation

-- Validate JSON structure before insertion CREATE TABLE user_profiles (  id SERIAL PRIMARY KEY,  profile_data json );  -- Insert with validation INSERT INTO user_profiles (profile_data) VALUES (  json('{  "name": "Alice",  "age": 30,  "interests": ["reading", "hiking"]  }' WITH UNIQUE) );

Additional considerations

  1. Use appropriate input validation:

    • Use WITH UNIQUE when duplicate keys should be prevented
    • Consider FORMAT JSON for explicit parsing requirements

  2. Error handling best practices:

    • Implement proper error handling for invalid JSON
    • Validate input before bulk operations

Learn more