This error occurs when a SQL/JSON path expression tries to access an array element on non-array data. Resolve it by verifying your JSON structure matches the path expression and using jsonb_typeof() to check data types.
PostgreSQL raises "SQL/JSON array not found" when you use an array-specific SQL/JSON path expression (like `$[0]` for accessing array elements) against JSON data that is not an array. This is a structural error in the JSON path language introduced in PostgreSQL 12. The error indicates a mismatch between what your path expression expects (an array) and what the actual JSON data contains (an object, scalar, or null value). PostgreSQL has two modes for handling such errors: lax mode (default) suppresses the error and returns an empty result set, while strict mode raises an error.
Check whether your JSON value is actually an array using the jsonb_typeof() function:
SELECT jsonb_typeof('{"name": "test"}'); -- Returns 'object'
SELECT jsonb_typeof('[1, 2, 3]'); -- Returns 'array'If the result is "object" but your path uses array syntax like $[0], that is the source of your error.
Ensure your path expression matches your JSON structure. For example:
Incorrect (tries to use array access on object):
SELECT jsonb_path_query('{"name": "Alice", "age": 30}', '$[0]');
-- Error: SQL/JSON array not foundCorrect (uses object property access):
SELECT jsonb_path_query('{"name": "Alice", "age": 30}', '$.name');
-- Returns: "Alice"For arrays, use proper array indexing:
SELECT jsonb_path_query('[1, 2, 3]', '$[0]');
-- Returns: 1By default, jsonb_path_query() uses lax mode, which suppresses structural errors and returns empty results instead of throwing an error:
SELECT jsonb_path_query('{"name": "test"}', 'lax $[0]');
-- Returns empty result set (no error)Lax mode is the default, but you can explicitly specify it. This is useful when you are uncertain about the data structure.
When using jsonb_path_query_array() or working with strict mode, add an ON ERROR clause to handle structural errors gracefully:
SELECT jsonb_path_query_array(
'{"name": "test"}',
'strict $[0]'
RETURNING jsonb DEFAULT '[]' ON ERROR
);
-- Returns: []This prevents the query from failing and returns a default value instead.
Ensure your data conforms to expected structure before using SQL/JSON functions:
-- Check if it is an array before using array index access
SELECT CASE
WHEN jsonb_typeof(data) = 'array' THEN jsonb_path_query(data, '$[0]')
ELSE NULL
END
FROM your_table;This prevents errors in production by checking the type first.
Lax vs Strict Mode: PostgreSQL SQL/JSON supports two error handling modes. In lax mode (the default), structural errors like accessing array indices on non-arrays are suppressed, returning empty results. In strict mode, these errors are raised. Choose the mode based on your use case: lax for tolerant queries, strict for validation.
JSON Path Syntax: SQL/JSON path language uses $ for the root, .property for object properties, and [index] for array elements. Complex paths like $.users[0].name combine these syntax elements.
PostgreSQL Versions: SQL/JSON path support was introduced in PostgreSQL 12. Earlier versions do not support this feature. Use jsonb_extract_path() or -> operators for compatibility with older versions.
Performance: For queries on large JSONB columns, ensure you have GIN indexes created with the default JSONB operator class to support path queries efficiently:
CREATE INDEX idx_jsonb_path ON table_name USING GIN (jsonb_column);ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL