How to fix SQL/JSON array not found in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 189 views

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.

Quick fix

Fixes SQL/JSON array not found

SELECT jsonb_typeof('{"name": "test"}');  -- Returns 'object'
SELECT jsonb_typeof('[1, 2, 3]');         -- Returns 'array'
  1. Verify your JSON data structure with jsonb_typeof()
  2. Review and correct your JSON path expression
  3. Use lax mode (default) for error suppression
  4. Add error handling with ON ERROR clause
postgresql — error
SELECT jsonb_typeof('{"name": "test"}');  -- Returns 'object'
SELECT jsonb_typeof('[1, 2, 3]');         -- Returns 'array'
SQL/JSON array not found

What this error means

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.

Typical symptoms

  • jsonb_path_query() returns empty results when accessing array indices
  • jsonb_path_query_array() throws an error with "SQL/JSON array not found"
  • JSON path expressions using square bracket notation `$[0]` fail on objects
  • Queries work in lax mode but fail after switching to strict mode

Common causes

  1. Using array index accessor `$[n]` on JSON objects instead of arrays
  2. Incorrect JSON path expression that does not match your data structure
  3. Using strict mode in jsonb_path_query() without proper error handling
  4. Attempting to access nested arrays without proper path syntax
  5. JSON data structure differs from expected format in your application logic

How to fix "SQL/JSON array not found"

1Verify your JSON data structure with jsonb_typeof()

Check whether your JSON value is actually an array using the jsonb_typeof() function:

sql
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.

2Review and correct your JSON path expression

Ensure your path expression matches your JSON structure. For example:

Incorrect (tries to use array access on object):

sql
SELECT jsonb_path_query('{"name": "Alice", "age": 30}', '$[0]');
-- Error: SQL/JSON array not found

Correct (uses object property access):

sql
SELECT jsonb_path_query('{"name": "Alice", "age": 30}', '$.name');
-- Returns: "Alice"

For arrays, use proper array indexing:

sql
SELECT jsonb_path_query('[1, 2, 3]', '$[0]');
-- Returns: 1
3Use lax mode (default) for error suppression

By default, jsonb_path_query() uses lax mode, which suppresses structural errors and returns empty results instead of throwing an error:

sql
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.

4Add error handling with ON ERROR clause

When using jsonb_path_query_array() or working with strict mode, add an ON ERROR clause to handle structural errors gracefully:

sql
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.

5Validate JSON structure before path queries

Ensure your data conforms to expected structure before using SQL/JSON functions:

sql
-- 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.

Advanced notes

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:

sql
CREATE INDEX idx_jsonb_path ON table_name USING GIN (jsonb_column);

Frequently asked questions

What does "SQL/JSON array not found" mean?
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).
How do I fix "SQL/JSON array not found"?
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.
What causes "SQL/JSON array not found"?
Using array index accessor $[n] on JSON objects instead of arrays Incorrect JSON path expression that does not match your data structure Using strict mode in jsonb_path_query() without proper error handling
Is the "SQL/JSON array not found" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading