ตัวดำเนินการ JSONB และคำค้นแบบตรวจสอบการบรรจุ

In PostgreSQL, a column of type jsonb can be searched many different ways, but not every operator can use an index. Performance here is almost entirely about choosing operators that a GIN index can accelerate.

• A GIN index (Generalized Inverted Index) stores the keys and values inside your JSON documents so lookups skip the full table.
• The two operators that matter most are containment (@>) and key existence (?, ?|, ?&).

This lesson teaches exactly which operators those are, and how to write queries that stay index-friendly.

The containment operator @> asks: does the left JSONB contain the right JSONB? The right side is a fragment, and Postgres checks that every key/value in it appears in the left document.

• '{"a":1,"b":2}' @> '{"a":1}' is true.
• '{"a":1}' @> '{"a":1,"b":2}' is false (the right side has more).

This is the workhorse for filtering rows: WHERE data @> '{"status":"active"}' finds every row whose JSON includes that pair.

A plain GIN index on a jsonb column supports both containment and key-existence operators. This is the index you reach for first.

• The default jsonb_ops operator class indexes every key and value.
• It accelerates @>, ?, ?|, and ?&.

Create it once, and containment filters that previously scanned the whole table become bitmap index scans.

Once the GIN index exists, write the filter as a containment check so the planner can use it. Matching a nested fragment works too, because containment is recursive.

• Top-level match: data @> '{"status":"active"}'.
• Nested match: data @> '{"user":{"plan":"pro"}}'.

Notice we pass a JSON object literal on the right, not a column reference or function call. That literal shape is what makes the query index-eligible.

Sometimes you only care whether a key is present, regardless of its value. The existence operators handle this and are also GIN-accelerated.

• data ? 'email' — true if the top-level key email exists.
• data ?| array['phone','email'] — true if any of these keys exist.
• data ?& array['phone','email'] — true if all of these keys exist.

Important: ? checks top-level keys only, and for arrays it checks whether the string is an element.

The extraction operators look convenient but are not accelerated by a standard GIN index:

• data -> 'status' returns the value as jsonb.
• data ->> 'status' returns the value as text.

A query like WHERE data ->> 'status' = 'active' forces a sequential scan on a plain GIN index, because the index does not index extracted scalar comparisons. Prefer the containment form data @> '{"status":"active"}' instead.

If you genuinely need range or pattern comparisons on one field, a B-tree expression index on the extracted text is the right tool — not GIN.

• Index the exact expression you query.
• Then comparisons like =, <, >, and BETWEEN can use it.

The query's expression must match the indexed expression character for character, or the planner ignores the index.

The alternative operator class jsonb_path_ops indexes hashed root-to-leaf paths instead of every key.

• It produces a smaller index and is typically faster for @> queries.
• Trade-off: it supports only containment (@>), not the existence operators ?, ?|, ?&.

Choose jsonb_path_ops when your workload is dominated by containment filtering and you never need key-existence searches.

Containment also matches inside JSON arrays, which makes it ideal for tag-style data. To ask "does this array contain a value," wrap the value in an array on the right side.

• '["a","b","c"]' @> '["b"]' is true.
• For a tagged document: data @> '{"tags":["urgent"]}' finds rows whose tags array includes urgent.

This stays fully index-eligible on a GIN index, so tag filtering scales well.

Never assume the index is used — confirm it. Run EXPLAIN and look for a Bitmap Index Scan on your GIN index. A Seq Scan means your operator or expression defeated the index.

• Good sign: Bitmap Index Scan on idx_events_data.
• Bad sign: Seq Scan on events with a JSON filter.

Use EXPLAIN (ANALYZE, BUFFERS) to also see real timing and how many pages were read.

Use this quick rule when writing a JSONB filter:

• Matching key/value or nested fragment? Use @> with a GIN index.
• Only checking a key is present? Use ?/?|/?& with default jsonb_ops GIN.
• Containment-only workload, want the smallest index? Use jsonb_path_ops GIN.
• Range or pattern on one scalar field? Use a B-tree expression index on ->>.

Avoid ->> equality filters without a matching expression index — they trigger sequential scans.

You have a default jsonb_ops GIN index on events.data. Which WHERE clause can use that index?

You learned which JSONB operators actually benefit from indexing:

• @> (containment) is the primary GIN-accelerated filter, including nested objects and arrays.
• ?, ?|, ?& (key existence) are GIN-accelerated, but only with the default jsonb_ops class, and check top-level keys.
• jsonb_path_ops gives a smaller, faster containment-only index.
• -> and ->> extraction filters do NOT use a plain GIN index; rewrite as @> or add a B-tree expression index.
• Always confirm with EXPLAIN that you get a Bitmap Index Scan, not a Seq Scan.