How to fix Invalid SQL/JSON subscript in PostgreSQL

SELECT jsonb_typeof(json_column) FROM your_table LIMIT 1;

SELECT json_column FROM your_table LIMIT 1;

-- Correct: accessing array element at index 0
SELECT json_array[0] FROM your_table;

-- Correct: accessing last element using negative index
SELECT json_array[-1] FROM your_table;

-- Wrong: using string subscript on array
SELECT json_array['0'] FROM your_table;  -- INVALID

-- Correct: accessing object value by string key
SELECT json_object['key_name'] FROM your_table;

-- Correct: nested object access
SELECT json_object['level1']['level2'] FROM your_table;

-- Wrong: using integer subscript on object
SELECT json_object[0] FROM your_table;  -- INVALID

-- Protect against NULL subscripts
SELECT json_object[COALESCE(my_key, 'default')] FROM your_table;

-- Check if the path exists before accessing deeper levels
SELECT json_object['key'] FROM your_table WHERE json_object['key'] IS NOT NULL;

-- Use jsonb_path_exists() to test path existence first
SELECT json_object FROM your_table
WHERE jsonb_path_exists(json_object, '$.key.subkey');

-- PostgreSQL 14+: Direct subscript assignment
UPDATE your_table SET json_column['key'] = 'new_value'::jsonb
WHERE id = 1;

-- This automatically creates intermediate objects if they don't exist
UPDATE your_table SET json_column['level1']['level2'] = 'value'::jsonb
WHERE id = 1;

-- For PostgreSQL < 14, use jsonb_set() instead
UPDATE your_table SET json_column = jsonb_set(json_column, '{key}', 'new_value'::jsonb)
WHERE id = 1;

-- Correct: both sides are JSONB
SELECT * FROM your_table WHERE json_column['status'] = '"active"'::jsonb;

-- Note: JSON strings need double-quotes inside the jsonb value
-- The outer quotes define the literal, inner quotes are part of the JSON
SELECT * FROM your_table WHERE json_column['name'] = '"John"'::jsonb;

-- Avoid type mismatch
SELECT * FROM your_table WHERE json_column['status'] = 'active';  -- May fail