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.
Fixes SQL/JSON array not found
SELECT jsonb_typeof('{"name": "test"}'); -- Returns 'object'
SELECT jsonb_typeof('[1, 2, 3]'); -- Returns 'array'SELECT jsonb_typeof('{"name": "test"}'); -- Returns 'object'SELECT jsonb_typeof('[1, 2, 3]'); -- Returns 'array'SQL/JSON array not found
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);