postgresql-json

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL JSON Skill

Atomic skill for JSONB operations

用于JSONB操作的原子化Skill

Overview

概述

Production-ready patterns for JSONB queries, indexing, and transformations.

适用于生产环境的JSONB查询、索引和转换模式。

Prerequisites

前置要求

PostgreSQL 16+
Understanding of JSON structure

PostgreSQL 16+
了解JSON结构

Parameters

参数

yaml

parameters:
  operation:
    type: string
    required: true
    enum: [query, index, transform, aggregate]
  json_path:
    type: string

yaml

parameters:
  operation:
    type: string
    required: true
    enum: [query, index, transform, aggregate]
  json_path:
    type: string

Quick Reference

快速参考

JSONB Operators

JSONB操作符

Operator	Description	Example
`->`	Get object	`data->'user'`
`->>`	Get as text	`data->>'name'`
`@>`	Contains	`data @> '{"active":true}'`
`?`	Key exists	`data ? 'email'`

操作符	描述	示例
`->`	获取对象	`data->'user'`
`->>`	以文本形式获取	`data->>'name'`
`@>`	包含	`data @> '{"active":true}'`
`?`	键存在	`data ? 'email'`

Index Patterns

索引模式

sql

CREATE INDEX idx_data ON t USING GIN(data);  -- Containment
CREATE INDEX idx_data_path ON t USING GIN(data jsonb_path_ops);  -- Faster @>
CREATE INDEX idx_status ON t ((data->>'status'));  -- Specific key

sql

CREATE INDEX idx_data ON t USING GIN(data);  -- Containment
CREATE INDEX idx_data_path ON t USING GIN(data jsonb_path_ops);  -- Faster @>
CREATE INDEX idx_status ON t ((data->>'status'));  -- Specific key

Common Operations

常见操作

sql

-- Nested update
UPDATE docs SET data = jsonb_set(data, '{user,verified}', 'true');

-- Array append
UPDATE docs SET data = jsonb_set(data, '{tags}', (data->'tags') || '"new"');

-- Aggregate
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) FROM users;

sql

-- 嵌套更新
UPDATE docs SET data = jsonb_set(data, '{user,verified}', 'true');

-- 数组追加
UPDATE docs SET data = jsonb_set(data, '{tags}', (data->'tags') || '"new"');

-- 聚合
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) FROM users;

Troubleshooting

故障排除

Error	Cause	Solution
`22P02`	Invalid JSON	Validate syntax
Slow @>	No GIN index	Create GIN index
NULL path	Key missing	Check with ?

错误	原因	解决方案
`22P02`	无效JSON	验证语法
缓慢的@>操作	无GIN索引	创建GIN索引
NULL路径	键缺失	使用?检查

Usage

使用方法

Skill("postgresql-json")

Skill("postgresql-json")