Error
Error Code:
22033
PostgreSQL Error 22033: Invalid JSON Path Expression
Description
Error 22033, 'invalid sql json subscript', indicates a problem with the SQL/JSON path expression used in your query. This typically occurs when the path syntax is incorrect, or you are attempting to access a non-existent element or an element with an incompatible data type within a JSON document.
Error Message
invalid sql json subscript
Known Causes
3 known causesMalformed Path Syntax
The SQL/JSON path expression contains syntax errors, such as incorrect delimiters, unquoted keys, or improper handling of special characters.
Accessing Non-existent Element
The path expression attempts to access a key or array index that does not exist within the target JSON document, leading to an invalid subscript operation.
Incorrect Data Type Subscript
An attempt was made to subscript a JSON value as an array when it is an object, or as an object when it is a scalar or array, causing a type mismatch.
Solutions
3 solutions available1. Correct JSON Path Syntax for Array Indices easy
Ensure array indices in JSON path expressions are enclosed in square brackets.
1
When accessing elements of a JSON array using a path expression, use square brackets `[]` around the numerical index. For example, to access the first element of an array at the root, use `$[0]` instead of `$0`.
SELECT json_column -> '$[0]' FROM your_table WHERE id = 1;2
Review your JSON path expressions, especially those targeting array elements. Common mistakes include omitting brackets or using incorrect syntax for array traversal.
SELECT json_column -> 'some_array[1].nested_key' FROM your_table WHERE id = 2;2. Validate JSON Structure and Path Existence medium
Verify the JSON data's structure matches your path and that the path actually exists within the JSON.
1
Inspect the actual JSON data to understand its structure. You can do this by selecting the JSON column directly.
SELECT json_column FROM your_table WHERE id = 1;2
Use PostgreSQL's `jsonb_typeof()` or `json_typeof()` function to check the type of a JSON element at a specific path. This can help identify if you're trying to access an array element on a non-array type or vice versa.
SELECT jsonb_typeof(json_column -> 'some_key') FROM your_table WHERE id = 1;3
If the path doesn't exist or the element has an unexpected type, your JSON path expression will fail. Adjust your query to reflect the actual JSON structure or handle cases where the path might not exist.
SELECT json_column -> 'optional_array[0]' FROM your_table WHERE id = 1; -- This might fail if 'optional_array' doesn't exist or is not an array.3. Use `jsonb_path_query` for More Robust Path Evaluation medium
Leverage `jsonb_path_query` for more flexible and powerful JSON path operations, including error handling.
1
Consider using the `jsonb_path_query` function, which is more powerful and can handle more complex path expressions. It also provides better control over how missing paths are handled.
SELECT jsonb_path_query(json_column, '$.some_array[0].nested_key') FROM your_table WHERE id = 1;2
The `jsonb_path_query` function returns a set of JSON values. If the path doesn't match, it returns no rows, which can be more predictable than an error in some scenarios. You can combine it with other SQL constructs for conditional logic.
SELECT * FROM your_table WHERE EXISTS (SELECT 1 FROM jsonb_path_query(json_column, '$.some_array[0]'));