中文
devexamples

PostgreSQL JSONB GIN Index Containment

Query JSONB documents with containment operators backed by a GIN index, comparing jsonb_path_query for complex predicates.

SqlData Fetching

Code

snippet
-- PostgreSQL GIN 索引让 @> 包含查询走索引
CREATE INDEX idx_events_payload
  ON events USING gin (payload jsonb_path_ops);

-- 包含运算符：文档包含给定结构即命中
SELECT
  event_id,
  payload->>'kind' AS kind,
  payload->'context'->>'region' AS region
FROM events
WHERE payload @> '{"context": {"region": "eu"}}'
ORDER BY created_at DESC
LIMIT 20;

-- 顶层数组包含：tags 数组含指定值
SELECT event_id, payload->'tags' AS tags
FROM events
WHERE payload->'tags' @> '["checkout"]'
  AND payload ? 'priority';  -- 顶层键存在性

-- jsonpath：复杂条件的结构化表达
SELECT
  event_id,
  jsonb_path_query_first(
    payload,
    '$.metrics[*] ? (@.name == "latency_ms" && @.value > 500).value'
  ) AS slow_latency
FROM events
WHERE jsonb_path_exists(
  payload,
  '$.metrics[*] ? (@.name == "latency_ms" && @.value > 500)'
);

Related Examples