How to fix SQL/JSON object not found in PostgreSQL

-- 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');

-- 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"';

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

-- Or with explicit value
SELECT jsonb_path_query(
  data,
  'strict $.email',
  silent => true
) AS email;

-- 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;

-- With COALESCE
SELECT COALESCE(
  data->>'email',
  data->>'alternate_email',
  '[email protected]'
) AS email FROM users;

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