To optimize queries checking for a nested key-value pair like {"location": {"city": "Seattle"}} in a JSONB column profile, which indexing approach is best?

hard📝 optimization Q8 of 15

PostgreSQL - JSON and JSONB

To optimize queries checking for a nested key-value pair like {"location": {"city": "Seattle"}} in a JSONB column profile, which indexing approach is best?

ACreate a GIST index on the JSONB column

BCreate a GIN index using the jsonb_path_ops operator class

CCreate a BTREE index on the JSONB column

DCreate a GIN index using the default jsonb_ops operator class

Step-by-Step Solution

Solution:

Step 1: Understand Operator Classes
jsonb_ops supports indexing all keys and values, including nested structures, enabling complex queries.
Step 2: Compare with jsonb_path_ops
jsonb_path_ops is smaller but only supports existence of top-level keys, not nested key-value pairs.
Step 3: Choose Index Type
BTREE and GIST are not suitable for JSONB containment queries.
Final Answer:
Create a GIN index using the default jsonb_ops operator class -> Option D
Quick Check:
Does the option support nested key-value indexing? Yes [OK]

Quick Trick: Use jsonb_ops for nested JSONB key-value indexing [OK]

Common Mistakes:

Choosing jsonb_path_ops which ignores nested values
Using BTREE or GIST indexes for JSONB containment

Master "JSON and JSONB" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

To optimize queries checking for a nested key-value pair like {"location": {"city": "Seattle"}} in a JSONB column profile, which indexing approach is best?

Step 1: Understand Operator Classes

Step 2: Compare with jsonb_path_ops

Step 3: Choose Index Type

Final Answer:

Quick Check:

Want More Practice?