postgresql-json
Work with JSONB data - queries, indexing, transformations
When & Why to Use This Skill
This Claude skill provides production-ready patterns for managing semi-structured data in PostgreSQL using JSONB. It empowers developers to master complex JSONB queries, optimize performance through specialized GIN indexing, and perform advanced data transformations and aggregations, ensuring efficient handling of flexible schemas within a relational database environment.
Use Cases
- Performance Optimization: Implementing GIN indexes (jsonb_path_ops) to significantly accelerate containment queries (@>) and key existence checks in large-scale document stores.
- Dynamic Data Manipulation: Executing precise nested updates and array appends within JSONB columns using jsonb_set without the need to fetch and rewrite the entire document.
- Schema Flexibility: Managing evolving data structures by storing semi-structured metadata in JSONB while maintaining the ability to perform high-performance SQL filtering and reporting.
- Backend API Efficiency: Using jsonb_agg and jsonb_build_object to transform relational query results directly into structured JSON formats, reducing application-level processing overhead.
| name | postgresql-json |
|---|---|
| description | Work with JSONB data - queries, indexing, transformations |
| version | "3.0.0" |
| sasmp_version | "1.3.0" |
| bonded_agent | 05-postgresql-advanced |
| bond_type | PRIMARY_BOND |
| category | database |
| difficulty | intermediate |
| estimated_time | 2h |
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")