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.