0Pricing
PostgreSQL Performance & Query Optimization · 강의

JSONB 연산자와 포함 쿼리

GIN 인덱스로 실제 가속할 수 있는 포함 및 경로 연산자를 사용하는 방법을 배웁니다.

JSONB 연산자와 포함 쿼리은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 1번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Operator Choice Decides Index Use

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 @>

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.

SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS contains_a,
       '{"a":1}'::jsonb @> '{"a":1,"b":2}'::jsonb AS contains_both;

Building a GIN Index for Containment

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.

CREATE INDEX idx_events_data
  ON events
  USING GIN (data);

Containment Filters in WHERE

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.

SELECT id, created_at
FROM events
WHERE data @> '{"user":{"plan":"pro"}}'
ORDER BY created_at DESC
LIMIT 50;

Key Existence Operators ? ?| ?&

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.

SELECT '{"email":"x@y.z","phone":"123"}'::jsonb ? 'email'        AS has_email,
       '{"email":"x@y.z"}'::jsonb ?| array['phone','email']      AS has_any,
       '{"email":"x@y.z"}'::jsonb ?& array['phone','email']      AS has_all;

The Trap: Path Extraction Operators -> and ->>

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.

-- Slow on a plain GIN index (seq scan):
SELECT * FROM events WHERE data ->> 'status' = 'active';

-- Fast equivalent (uses GIN):
SELECT * FROM events WHERE data @> '{"status":"active"}';

Rescuing ->> with an Expression Index

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.

CREATE INDEX idx_events_status
  ON events ((data ->> 'status'));

-- Now this can use the B-tree index:
SELECT * FROM events WHERE (data ->> 'status') = 'active';

jsonb_path_ops: Smaller, Faster, Containment-Only

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.

CREATE INDEX idx_events_data_path
  ON events
  USING GIN (data jsonb_path_ops);

Containment Against Arrays

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.

SELECT '["a","b","c"]'::jsonb @> '["b"]'::jsonb   AS has_b,
       '{"tags":["urgent","billing"]}'::jsonb
         @> '{"tags":["urgent"]}'::jsonb            AS is_urgent;

Verify With EXPLAIN

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.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE data @> '{"status":"active"}';

Putting It Together: A Decision Rule

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.

Quick Check

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

Recap

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.

자주 묻는 질문

“JSONB 연산자와 포함 쿼리” 강의는 무료인가요?

네 — “JSONB 연산자와 포함 쿼리” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“JSONB 연산자와 포함 쿼리”에서 뭘 배우나요?

GIN 인덱스로 실제 가속할 수 있는 포함 및 경로 연산자를 사용하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 1번째 강의입니다.

“JSONB 연산자와 포함 쿼리” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. JSONB 연산자와 포함 쿼리
  2. JSONB에서 GIN 인덱스와 표현식 인덱스 비교
  3. JSONPath로 JSONB 조회하기
  4. JSONB에서 정규화할 시점
← PostgreSQL Performance & Query Optimization(으)로 돌아가기