# JSON & Document Operations

Work with JSON columns, nested documents, and flexible schemas across all databases.

## Overview

Prax provides comprehensive JSON support for storing and querying semi-structured data, from PostgreSQL's JSONB to MongoDB's native BSON documents.

Feature | PostgreSQL | MySQL | SQLite | MSSQL | MongoDB
--- | --- | --- | --- | --- | ---
JSON Type | ✅ JSONB | ✅ JSON | ✅ JSON | ✅ NVARCHAR | ✅ BSON
Path Queries | ✅ ->, ->> | ✅ ->, ->> | ✅ json_extract | ✅ JSON_VALUE | ✅ Dot notation
JSON Indexing | ✅ GIN | ✅ Generated | ❌ | ✅ | ✅ Native
Containment | ✅ @> | ✅ JSON_CONTAINS | ❌ | ❌ | ✅ $elemMatch

## JSON Path Queries

Navigate JSON structures with a cross-database path API.

## Filtering JSON Data

Use JSON-specific filters in your queries.

## JSON Mutations

Update specific fields within JSON columns without replacing the entire document.

## JSON Aggregations

Build JSON objects and arrays from query results.

## JSON Indexing

Create indexes for efficient JSON queries.

#### GIN Index (PostgreSQL)

Use `jsonb_path_ops` for containment queries (@>). Use default ops for key existence (?).

#### Generated Columns (MySQL)

Extract JSON values into virtual columns and index those for efficient filtering.

## MongoDB Document Operations

MongoDB's native document model with atomic update operators.

## Nested Documents

Define embedded document types in your schema and query them type-safely.

## Best Practices

#### Use JSONB over JSON (PostgreSQL)

JSONB is binary, supports indexing, and is faster for most operations. Use JSON only when you need to preserve key order or whitespace.

#### Index Hot Paths

If you frequently query a specific JSON path, create an expression index on that path rather than a full GIN index.

#### Validate JSON Structure

JSON columns accept any valid JSON. Use CHECK constraints or application validation to ensure schema consistency.

#### Consider Embedded vs Relations

In MongoDB, embed frequently-accessed data together. In SQL databases, use JSON for truly flexible data, not as a substitute for proper relations.