PostgreSQL JSON Skill Skill

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")

Agent Skills: PostgreSQL JSON Skill

Skill Files