PostgreSQL 22033 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22033: invalid_sql_json_subscript

PostgreSQL error 22033 (invalid_sql_json_subscript) occurs when you use an invalid subscript in a SQL/JSON path expression — for example, applying an array index to a non-array JSON value or using an out-of-range index on a JSON array. This error commonly surfaces when working with jsonb_path_query, jsonb_path_exists, JSON_QUERY, or JSON_VALUE functions introduced and expanded in PostgreSQL 12 and later.

Top 3 Causes

1. Applying an Array Index to a Non-Array JSON Value

Using [0] or any integer subscript on a JSON string, number, or object triggers this error immediately.

-- ERROR: applying array index to a string value
SELECT jsonb_path_query('{"name": "Alice"}'::jsonb, '$.name[0]');
-- ERROR:  22033: invalid SQL/JSON subscript

-- FIX: use lax mode to suppress the error (returns null instead)
SELECT jsonb_path_query_first(
  '{"name": "Alice"}'::jsonb,
  'lax $.name[0]'
);
-- Result: null

-- FIX: check type before accessing
SELECT
  CASE
    WHEN jsonb_typeof(data->'name') = 'array' THEN (data->'name')->0
    ELSE data->'name'
  END AS safe_name
FROM (SELECT '{"name": "Alice"}'::jsonb AS data) t;

2. Out-of-Range or Negative Array Index

Accessing an index beyond the array length or using a negative subscript in strict mode will raise error 22033.

-- ERROR: index 5 is out of range for array of length 3
SELECT jsonb_path_query('[10, 20, 30]'::jsonb, '$[5]');
-- ERROR:  22033: invalid SQL/JSON subscript

-- FIX: validate array length before access
SELECT
  CASE
    WHEN jsonb_array_length('[10, 20, 30]'::jsonb) > 5
    THEN ('[10, 20, 30]'::jsonb)->5
    ELSE NULL
  END AS safe_value;

-- FIX: use lax mode
SELECT jsonb_path_query_first('[10, 20, 30]'::jsonb, 'lax $[5]');
-- Result: null (no error)

3. Using an Integer Subscript on a JSON Object

JSON objects require string key access. Using $[0] on an object — even one with numeric-looking string keys — is invalid.

-- ERROR: cannot use integer subscript on a JSON object
SELECT jsonb_path_query('{"0": "zero", "1": "one"}'::jsonb, '$[0]');
-- ERROR:  22033: invalid SQL/JSON subscript

-- FIX: use string key notation in JSON Path
SELECT jsonb_path_query(
  '{"0": "zero", "1": "one"}'::jsonb,
  '$.\"0\"'
);
-- Result: "zero"

-- FIX: use the -> or ->> operators directly
SELECT '{"0": "zero", "1": "one"}'::jsonb ->> '0';
-- Result: zero

Quick Fix Solutions

Switch to lax mode: Prepend lax to your JSON Path expression. In lax mode, PostgreSQL returns null instead of raising an error on type mismatches or out-of-range indexes. This is the fastest mitigation for production queries.
Use jsonb_typeof(): Always check whether your target value is 'array', 'object', or a scalar before applying subscript access.
Use jsonb_array_length(): Before accessing a specific array index, verify the array has enough elements.
Wrap in exception handling (PL/pgSQL):

DO $$
BEGIN
  PERFORM jsonb_path_query('{"name": "Alice"}'::jsonb, '$.name[0]');
EXCEPTION WHEN sqlstate '22033' THEN
  RAISE NOTICE 'Caught invalid_sql_json_subscript — check your JSON path.';
END;
$$;

Prevention Tips

1. Default to lax mode in application queries.
Unless you explicitly need strict validation (e.g., data quality checks), always use lax in JSON Path expressions. It prevents 22033 and related errors (22034, 22035) from crashing your queries when JSON structure is inconsistent.

-- Safe pattern for production queries
SELECT COALESCE(
  jsonb_path_query_first(payload, 'lax $.items[0].price'),
  '0'::jsonb
) AS price
FROM events;

2. Enforce JSON schema at insert time with CHECK constraints.
Validate JSON structure when data enters the database so downstream queries can trust the shape of the data.

ALTER TABLE orders
  ADD CONSTRAINT chk_items_is_array
  CHECK (jsonb_typeof(data->'items') = 'array');

Related Errors

Code	Name	Notes
22032	invalid_json_text	Malformed JSON string — precedes 22033
22034	sql_json_array_not_found	Expected array but none found
22035	sql_json_member_not_found	Missing object key in strict mode
22036	sql_json_number_not_found	Expected number but found another type

All errors in the 2203x family can typically be mitigated by switching from strict to lax mode in your SQL/JSON path expressions.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community