How to fix SQL/JSON item cannot be cast to target type in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 129 views

This error occurs when using PostgreSQL SQL/JSON functions like JSON_VALUE() or JSON_QUERY() and attempting to cast a JSON value to an incompatible SQL type. It typically indicates a type mismatch between the JSON content and the target data type.

Quick fix

Fixes SQL/JSON item cannot be cast to target type

SELECT JSON_VALUE(data, '$.price' RETURNING numeric DEFAULT NULL ON ERROR)
FROM products;
  1. Use ON ERROR clause for graceful error handling
  2. Cast JSON strings to text first before numeric conversion
  3. Ensure your JSON path expression returns the correct type
  4. Specify the correct RETURNING type in JSON_VALUE
postgresql — error
SELECT JSON_VALUE(data, '$.price' RETURNING numeric DEFAULT NULL ON ERROR)
FROM products;
SQL/JSON item cannot be cast to target type

What this error means

PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, JSON_EXISTS) provide powerful ways to query and extract data from JSON documents. When these functions evaluate a JSON path and attempt to coerce the result to a specific SQL type, PostgreSQL validates that the conversion is valid. This error is raised when the JSON value's type is incompatible with the requested target type—for example, trying to cast a JSON string to a numeric type, a JSON array to a scalar type, or a JSON null to a non-nullable type without proper error handling. The error can occur in two scenarios: during JSON path evaluation when the result doesn't match expectations, or during the type coercion phase when PostgreSQL cannot safely convert the JSON value to the target SQL type.

Typical symptoms

  • Queries with JSON_VALUE() or JSON_QUERY() return "SQL/JSON item cannot be cast to target type" error
  • Casting jsonb values to numeric or boolean types unexpectedly fails
  • Type casting of JSON null values to specific types produces errors
  • Migrations involving JSON column type changes fail with casting errors
  • Application queries fail when trying to extract typed values from JSON columns

Common causes

  1. JSON value contains a string when you're trying to cast it to a numeric type (e.g., "123" cannot be cast directly, only numeric values can)
  2. Attempting to cast JSON null to a specific type like numeric, boolean, or timestamp without using proper null handling
  3. JSON value contains an array or object when you're trying to extract a scalar value without using proper JSON path expressions
  4. Using RETURNING clause in JSON_VALUE with an incompatible target data type
  5. Missing ON ERROR or ON EMPTY clauses that would provide fallback behavior for problematic casts

How to fix "SQL/JSON item cannot be cast to target type"

1Use ON ERROR clause for graceful error handling

Add an ON ERROR clause to your JSON function to specify behavior when casting fails. By default, ON ERROR returns NULL:

sql
SELECT JSON_VALUE(data, '$.price' RETURNING numeric DEFAULT NULL ON ERROR)
FROM products;

Alternatively, use ERROR ON ERROR to see the actual error for debugging:

sql
SELECT JSON_VALUE(data, '$.price' RETURNING numeric ERROR ON ERROR)
FROM products;
2Cast JSON strings to text first before numeric conversion

When dealing with JSON string values that represent numbers, extract as text first, then cast to the target type:

sql
-- Don't do this:
SELECT (data -> 'count')::numeric FROM items;  -- Fails if count is a string

-- Do this instead:
SELECT (data ->> 'count')::text::numeric FROM items;
-- Or with JSON functions:
SELECT CAST(JSON_VALUE(data, '$.count' RETURNING text) AS numeric) FROM items;

The ->> operator extracts as text, which is safer for subsequent type conversions.

3Ensure your JSON path expression returns the correct type

Verify that your JSON path will extract the correct type of value. Use JSON_EXISTS to check if a path exists before trying to extract it:

sql
-- Check if value exists and is the right type
SELECT 
  CASE 
    WHEN JSON_EXISTS(data, '$.price' ? @ > 0) THEN 
      JSON_VALUE(data, '$.price' RETURNING numeric)
    ELSE 
      NULL
  END AS price
FROM products;
4Specify the correct RETURNING type in JSON_VALUE

Make sure your RETURNING clause specifies a type that PostgreSQL can actually cast the JSON value to:

sql
-- For JSON numeric values:
SELECT JSON_VALUE(data, '$.count' RETURNING bigint) FROM items;

-- For JSON string values:
SELECT JSON_VALUE(data, '$.name' RETURNING text) FROM items;

-- For JSON boolean values:
SELECT JSON_VALUE(data, '$.is_active' RETURNING boolean) FROM items;

If the JSON value's actual type doesn't match, use ON ERROR to handle it gracefully.

5Handle JSON null values explicitly

JSON null values cannot be cast directly to most PostgreSQL types. Use COALESCE or DEFAULT to provide a fallback:

sql
-- With ON ERROR (returns NULL by default):
SELECT COALESCE(
  JSON_VALUE(data, '$.age' RETURNING integer),
  0  -- Default value if JSON value is null or casting fails
) FROM users;

-- Or use DEFAULT in ON EMPTY clause:
SELECT JSON_VALUE(
  data, 
  '$.age' 
  RETURNING integer 
  NULL ON EMPTY 
  NULL ON ERROR
) FROM users;

Advanced notes

PostgreSQL's SQL/JSON path language is powerful but strict about type safety. Understanding the difference between JSON types (string, number, boolean, array, object, null) and PostgreSQL types (text, numeric, boolean, etc.) is critical. When working with JSONB columns that might contain heterogeneous data, consider validating the JSON structure at insert time or using JSON schemas. For complex transformations, breaking the operation into multiple steps (extract as text, validate, then cast) is often clearer and more maintainable than trying to do everything in one expression. The -> operator returns jsonb while ->> returns text—using ->> first gives you more flexibility for subsequent operations.

Frequently asked questions

What does "SQL/JSON item cannot be cast to target type" mean?
PostgreSQL's SQL/JSON functions (JSON_VALUE, JSON_QUERY, JSON_EXISTS) provide powerful ways to query and extract data from JSON documents. When these functions evaluate a JSON path and attempt to coerce the result to a specific SQL type, PostgreSQL validates that the conversion is valid. This error is raised when the JSON value's type is incompatible with the requested target type—for example, trying to cast a JSON string to a numeric type, a JSON array to a scalar type, or a JSON null to a non-nullable type without proper error handling.
How do I fix "SQL/JSON item cannot be cast to target type"?
This error occurs when using PostgreSQL SQL/JSON functions like JSON_VALUE() or JSON_QUERY() and attempting to cast a JSON value to an incompatible SQL type. It typically indicates a type mismatch between the JSON content and the target data type.
What causes "SQL/JSON item cannot be cast to target type"?
JSON value contains a string when you're trying to cast it to a numeric type (e.g., "123" cannot be cast directly, only numeric values can) Attempting to cast JSON null to a specific type like numeric, boolean, or timestamp without using proper null handling JSON value contains an array or object when you're trying to extract a scalar value without using proper JSON path expressions
Is the "SQL/JSON item cannot be cast to target type" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading