Postgres json_scalar() Function

The json_scalar() function in PostgreSQL 17 provides a straightforward way to convert SQL scalar values into their JSON equivalents. This function is particularly useful when you need to ensure proper type conversion and formatting of individual values for JSON output.

Use json_scalar() when you need to:

Convert SQL numbers to JSON numbers
Format timestamps as JSON strings
Convert SQL booleans to JSON booleans
Ensure proper null handling in JSON context

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_scalar() function uses the following syntax:

json_scalar(expression) → json

Parameters:

expression: Any SQL scalar value to be converted to a JSON scalar value

Example usage

Let's explore various ways to use the json_scalar() function with different types of input values.

Numeric values

-- Convert integer
SELECT json_scalar(42);

# | json_scalar
---------------
1 | 42

-- Convert floating-point number
SELECT json_scalar(123.45);

# | json_scalar
---------------
1 | 123.45

String values

-- Convert text
SELECT json_scalar('Hello, World!');

# |     json_scalar
--------------------
1 | "Hello, World!"

Date and timestamp values

-- Convert timestamp
SELECT json_scalar(CURRENT_TIMESTAMP);

# |            json_scalar
---------------------------------------
1 | "2024-12-04T06:19:14.458444+00:00"

-- Convert date
SELECT json_scalar(CURRENT_DATE);

# |  json_scalar
----------------
1 | "2024-12-04"

Boolean values

-- Convert boolean true
SELECT json_scalar(true);

# | json_scalar
--------------
1 | true

NULL handling

-- Convert NULL value
SELECT json_scalar(NULL);

# | json_scalar
--------------
1 |

Common use cases

Building JSON objects

-- Create a JSON object with properly formatted values
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMP WITH TIME ZONE
);

INSERT INTO users (name, created_at)
VALUES
    ('Alice', '2024-12-04T14:30:45.000000+00:00'),
    ('Bob', '2024-12-04T15:30:45.000000+00:00');

SELECT json_build_object(
    'id', json_scalar(id),
    'name', json_scalar(name),
    'created_at', json_scalar(created_at)
)
FROM users;

# |                              json_build_object
-----------------------------------------------------------------------------------
1 | {"id" : 3, "name" : "Alice", "created_at" : "2024-12-04T14:30:45.000000+00:00"}
2 | {"id" : 4, "name" : "Bob", "created_at" : "2024-12-04T15:30:45.000000+00:00"}

Data type conversion

-- Convert mixed data types in a single query
SELECT json_build_array(
    json_scalar(42),
    json_scalar('text'),
    json_scalar(CURRENT_TIMESTAMP),
    json_scalar(NULL)
);

# |                 json_build_array
----------------------------------------------------------
1 | [42, "text", "2024-12-04T06:25:29.928376+00:00", null]

Type conversion rules

The function follows these conversion rules:

NULL -> SQL NULL
Numbers → JSON numbers (preserving exact value)
Booleans → JSON booleans
All other types → JSON strings with appropriate formatting:
- Timestamps include timezone when available
- Text is properly escaped according to JSON standards