How to fix SQL/JSON object not found in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 138 views

This error occurs when using strict mode in SQL/JSON path expressions to access a non-existent JSON object member or array element. Switch to lax mode or add error handling clauses to suppress the error.

Quick fix

Fixes SQL/JSON object not found

-- Strict mode (fails if email does not exist)
SELECT jsonb_path_query(data, 'strict $.email');

-- Lax mode (returns empty if email does not exist)
SELECT jsonb_path_query(data, '$.email');
  1. Use lax mode instead of strict mode
  2. Use the @? and @@ operators for safe path checking
  3. Add ON ERROR clause for explicit error handling
  4. Verify JSON object structure before querying
postgresql — error
-- Strict mode (fails if email does not exist)
SELECT jsonb_path_query(data, 'strict $.email');
-- Lax mode (returns empty if email does not exist)
SELECT jsonb_path_query(data, '$.email');
SQL/JSON object not found

What this error means

PostgreSQL SQL/JSON path expressions handle structural errors in two distinct modes. In strict mode, attempting to access a non-existent member of a JSON object or element of an array raises an error. In lax mode (the default), the path engine adapts to the queried data and suppresses structural errors, returning an empty result instead. This error typically occurs when you use strict mode to query a JSON path that does not exist in your data structure, or when using functions like jsonb_path_query() on paths that are not present in the JSON document.

Typical symptoms

  • SQL/JSON path expressions fail with "SQL/JSON object not found" error
  • strict mode JSON queries raise errors instead of returning empty results
  • jsonb_path_query() or jsonb_path_exists() fail on missing keys
  • ON ERROR clauses trigger when accessing non-existent object members

Common causes

  1. Using strict mode in jsonb_path_query() or other path functions without checking if the path exists
  2. Querying JSON objects with path expressions that do not match the actual structure
  3. Attempting to access deeply nested paths without verifying intermediate objects exist
  4. Not using error suppression operators like @? or @@ when querying uncertain paths
  5. Missing ON ERROR or ON EMPTY clauses in jsonpath expressions

How to fix "SQL/JSON object not found"

1Use lax mode instead of strict mode

Lax mode (the default) suppresses structural errors and returns empty results. Remove the strict keyword from your path expression:

sql
-- Strict mode (fails if email does not exist)
SELECT jsonb_path_query(data, 'strict $.email');

-- Lax mode (returns empty if email does not exist)
SELECT jsonb_path_query(data, '$.email');
2Use the @? and @@ operators for safe path checking

The jsonpath operators @? and @@ suppress structural errors automatically:

sql
-- Use @? to check if path exists
SELECT * FROM users WHERE data @? '$.email';

-- Use @@ to filter based on a path condition
SELECT * FROM users WHERE data @@ '$.status == "active"';
3Add ON ERROR clause for explicit error handling

In strict mode, use ON ERROR to specify fallback behavior:

sql
SELECT jsonb_path_query(
  data,
  'strict $.email',
  silent => true
);

-- Or with explicit value
SELECT jsonb_path_query(
  data,
  'strict $.email',
  silent => true
) AS email;
4Verify JSON object structure before querying

Use -> and ->> operators which return NULL instead of failing:

sql
-- Safe: returns NULL if key does not exist
SELECT data->'email' AS email FROM users;

-- Safe: returns text NULL if key does not exist
SELECT data->>'email' AS email FROM users;

-- Compare with path query
SELECT jsonb_path_query_first(data, '$.email') AS email FROM users;
5Use COALESCE or conditional logic for missing keys

Provide default values when JSON keys may not exist:

sql
-- With COALESCE
SELECT COALESCE(
  data->>'email',
  data->>'alternate_email',
  'no-email@example.com'
) AS email FROM users;

-- With CASE
SELECT CASE
  WHEN data ? 'email' THEN data->>'email'
  ELSE 'unknown'
END AS email FROM users;

Advanced notes

PostgreSQL distinguishes between json and jsonb types. The jsonb type supports more operators including @?, @@, and path functions. If using json type, cast to jsonb with ::jsonb. The silent argument is available in jsonb_path_exists(), jsonb_path_match(), jsonb_path_query(), jsonb_path_query_array(), and jsonb_path_query_first() functions (PostgreSQL 12+). For indexing performance, consider using jsonb_path_ops index operator class if querying with @>, @?, or @@ operators. In lax mode, the path engine will attempt to iterate over any array or object it encounters, making it more forgiving but potentially slower for large structures.

Frequently asked questions

What does "SQL/JSON object not found" mean?
PostgreSQL SQL/JSON path expressions handle structural errors in two distinct modes. In strict mode, attempting to access a non-existent member of a JSON object or element of an array raises an error. In lax mode (the default), the path engine adapts to the queried data and suppresses structural errors, returning an empty result instead.
How do I fix "SQL/JSON object not found"?
This error occurs when using strict mode in SQL/JSON path expressions to access a non-existent JSON object member or array element. Switch to lax mode or add error handling clauses to suppress the error.
What causes "SQL/JSON object not found"?
Using strict mode in jsonb_path_query() or other path functions without checking if the path exists Querying JSON objects with path expressions that do not match the actual structure Attempting to access deeply nested paths without verifying intermediate objects exist
Is the "SQL/JSON object 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