You want to update or create the nested key details.age to 40 in a JSONB column profile, but details may not exist. Which query correctly handles this?
hard📝 Application Q8 of 15
PostgreSQL - JSON and JSONB
You want to update or create the nested key details.age to 40 in a JSONB column profile, but details may not exist. Which query correctly handles this?
AUPDATE table SET profile = jsonb_set(profile, '{details,age}', '40', true);
BUPDATE table SET profile = jsonb_set(profile, '{details,age}', '40');
CUPDATE table SET profile = jsonb_set(profile, '{details}', '{"age":40}', true);
DUPDATE table SET profile = jsonb_set(profile, '{age}', '40', true);
Step-by-Step Solution
Solution:
Step 1: Use create_missing parameter
Setting the fourth argument to true allows jsonb_set to create missing intermediate keys like details.
Step 2: Correct path and value
The path '{details,age}' targets the nested key, and '40' is a JSON number (should be '40' as a JSON string, but PostgreSQL accepts numeric literals).
Step 3: Exclude incorrect options
UPDATE table SET profile = jsonb_set(profile, '{details,age}', '40'); misses true, so it fails if details is missing. UPDATE table SET profile = jsonb_set(profile, '{details}', '{"age":40}', true); replaces details entirely. UPDATE table SET profile = jsonb_set(profile, '{age}', '40', true); updates wrong path.
Final Answer:
UPDATE table SET profile = jsonb_set(profile, '{details,age}', '40', true); -> Option A
Quick Check:
Use create_missing=true to add nested keys [OK]
Quick Trick:Set create_missing=true to add nested keys [OK]
Common Mistakes:
Omitting create_missing parameter
Replacing entire nested object instead of key
Updating wrong JSON path
Master "JSON and JSONB" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
