majiayu000 PostgreSQL JSON Skill
Atomic skill for JSONB operations
Overview
Production-ready patterns for JSONB queries, indexing, and transformations.
Prerequisites
- PostgreSQL 16+
- Understanding of JSON structure
Parameters
parameters:
operation:
type: string
required: true
enum: [query, index, transform, aggregate]
json_path:
type: string
Quick Reference
JSONB Operators
| Operator | Description | Example |
|---|---|---|
-> |
Get object | data->'user' |
->> |
Get as text | data->>'name' |
@> |
Contains | data @> '{"active":true}' |
? |
Key exists | data ? 'email' |
Index Patterns
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
-- 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;
Troubleshooting
| Error | Cause | Solution |
|---|---|---|
22P02 |
Invalid JSON | Validate syntax |
| Slow @> | No GIN index | Create GIN index |
| NULL path | Key missing | Check with ? |
Usage
Skill("postgresql-json")