# Postgresql JSON

> Work with JSONB data - queries, indexing, transformations

- Skill: `majiayu000/postgresql-json` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add majiayu000/postgresql-json`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/postgresql-json/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/postgresql-json

---


# 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

```yaml
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
```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;
```

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