JSONB existence (?) operator in PostgreSQL - Time & Space Complexity

Time Complexity: JSONB existence (?) operator

O(n)

Understanding Time Complexity

We want to understand how checking if a key exists in a JSONB column grows as the data grows.

How does the time to find a key change when the JSONB data gets bigger?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT *
FROM products
WHERE attributes ? 'color';
    

This query finds all rows where the JSONB column attributes contains the key color.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Checking each row's JSONB data for the key color.
• How many times: Once per row in the products table.

How Execution Grows With Input

As the number of rows grows, the database checks more JSONB values for the key.

Input Size (n rows)	Approx. Operations
10	10 key checks
100	100 key checks
1000	1000 key checks

Pattern observation: The number of checks grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows checked.

Common Mistake

[X] Wrong: "Checking for a key in JSONB is instant no matter how big the data is."

[OK] Correct: The database must look inside each JSONB value, so bigger or more rows mean more work.

Interview Connect

Understanding how JSONB key checks scale helps you explain query performance clearly and shows you know how databases handle semi-structured data.

Self-Check

"What if we added a GIN index on the JSONB column? How would the time complexity change?"