PostgreSQL 22033 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22033: invalid sql json subscript

PostgreSQL error 22033 (invalid sql json subscript) occurs when you attempt to access a JSON or JSONB value using an invalid subscript in a SQL/JSON path expression. This typically means you've provided a non-integer index for a JSON array, used an out-of-context subscript type, or tried to apply array-style indexing to a JSON object. This error became more visible after PostgreSQL 12, when SQL/JSON Path support was significantly expanded.

---

Top 3 Causes

1. Using a Non-Integer Value as an Array Index

JSON arrays must be accessed with non-negative integer indexes. Passing a string, float, or boolean as an index triggers error 22033.

-- BAD: String used as array subscript (triggers 22033)
SELECT jsonb_path_query('["apple", "banana", "cherry"]'::jsonb, '$["first"]');

-- GOOD: Use integer index
SELECT jsonb_path_query('["apple", "banana", "cherry"]'::jsonb, '$[0]');
-- Result: "apple"

-- GOOD: Use 'last' keyword for the final element
SELECT jsonb_path_query('["apple", "banana", "cherry"]'::jsonb, '$[last]');
-- Result: "cherry"

-- GOOD: Range slice syntax
SELECT jsonb_path_query('["a", "b", "c", "d"]'::jsonb, '$[0 to 2]');
-- Result: "a", "b", "c"

2. Applying Array Subscript to a JSON Object

Attempting to access a JSON object ({}) using a numeric index will fail because objects are not arrays. This is especially common when handling APIs that return either an array or an object depending on context.

-- BAD: Numeric index on a JSON object (triggers 22033)
SELECT jsonb_path_query('{"name": "Alice"}'::jsonb, '$[0]');

-- GOOD: Check type before accessing
SELECT
  CASE
    WHEN jsonb_typeof(data) = 'array'  THEN jsonb_path_query_first(data, '$[0]')::text
    WHEN jsonb_typeof(data) = 'object' THEN data->>'name'
    ELSE NULL
  END AS result
FROM (
  VALUES
    ('["Alice", "Bob"]'::jsonb),
    ('{"name": "Alice"}'::jsonb)
) AS t(data);

3. Malformed JSON Path Expressions in jsonb_path_query

When building JSON path expressions dynamically, a malformed subscript (e.g., a float index or an invalid wildcard combination) will trigger this error.

-- BAD: Float used as array index (triggers 22033)
SELECT jsonb_path_query('[1, 2, 3]'::jsonb, '$[1.5]');

-- GOOD: Wildcard to access all elements
SELECT jsonb_path_query('[1, 2, 3]'::jsonb, '$[*]');
-- Result: 1, 2, 3

-- GOOD: Validate path existence before querying
SELECT jsonb_path_exists(
  '{"items": [10, 20, 30]}'::jsonb,
  '$.items[0]'
);
-- Result: true

-- GOOD: Use jsonb_path_query_first for single-value extraction
SELECT jsonb_path_query_first(
  '{"items": [10, 20, 30]}'::jsonb,
  '$.items[2]'
);
-- Result: 30

---

Quick Fix Solutions

• Always validate that your subscript is a non-negative integer before constructing a JSON path.
• Use jsonb_typeof() to confirm the target value is an 'array' before applying numeric indexing.
• Use jsonb_path_exists() as a pre-check before calling jsonb_path_query().
• Wrap risky JSON access in a helper function that returns NULL gracefully instead of throwing an error.

-- Safe wrapper function for array access
CREATE OR REPLACE FUNCTION safe_json_get(data JSONB, idx INT)
RETURNS JSONB AS $$
BEGIN
  IF jsonb_typeof(data) != 'array' OR idx < 0 OR idx >= jsonb_array_length(data) THEN
    RETURN NULL;
  END IF;
  RETURN data->idx;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT safe_json_get('["x", "y", "z"]'::jsonb, 1);  -- Returns "y"
SELECT safe_json_get('{"key": "val"}'::jsonb, 0);    -- Returns NULL safely

---

Prevention Tips

1. Enforce JSON structure at the database level using CHECK constraints with jsonb_typeof() to guarantee that columns always hold the expected JSON type (array vs. object).

ALTER TABLE my_table
  ADD CONSTRAINT chk_data_is_array
  CHECK (jsonb_typeof(data) = 'array');

1. Validate dynamic JSON path inputs in application code before sending them to PostgreSQL. Never interpolate raw user input directly into a JSON path string. Always verify that index values are non-negative integers and that the target JSONB column contains the expected structure before querying.

---

Related Errors

Error Code	Name	Description
22032	invalid_json_text	Malformed JSON string input
22034	more_json_paths_than_one_expected	Path returns multiple values where one is expected
22P02	invalid_text_representation	Invalid cast to JSONB type

---

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