Postgres json_scalar() Function
Convert SQL Scalar Values to JSON Scalar Values
The json_scalar() function introduced in PostgreSQL 17 provides a straightforward way to convert SQL scalar values into their JSON equivalents. Use it when you need to ensure proper type conversion and formatting of individual values for JSON output.
Use json_scalar() when you need to:
- Convert
SQLnumbers toJSONnumbers - Format timestamps as JSON strings
- Convert
SQLbooleans toJSONbooleans - Ensure proper null handling in
JSONcontext
Function signature
Section titled “Function signature”The json_scalar() function uses the following syntax:
json_scalar(expression) → jsonParameters:
expression: AnySQLscalar value to be converted to aJSONscalar value
Example usage
Section titled “Example usage”Let's explore various ways to use the json_scalar() function with different types of input values.
Numeric values
Section titled “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.45String values
Section titled “String values”-- Convert text
SELECT json_scalar('Hello, World!');# | json_scalar
--------------------
1 | "Hello, World!"Date and timestamp values
Section titled “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
Section titled “Boolean values”-- Convert boolean true
SELECT json_scalar(true);# | json_scalar
--------------
1 | trueNULL handling
Section titled “NULL handling”-- Convert NULL value
SELECT json_scalar(NULL);# | json_scalar
--------------
1 |Common use cases
Section titled “Common use cases”Building JSON objects
Section titled “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
Section titled “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
Section titled “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