How to fix SQL/JSON array not found in PostgreSQL

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 found

Correct (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: 1

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