---
import DocsLayout from '../../layouts/DocsLayout.astro';
import CodeBlock from '../../components/CodeBlock.astro';
const jsonPath = `use prax::json::{JsonPath, PathSegment};
// Build JSON paths programmatically
let path = JsonPath::new("settings")
.field("notifications")
.field("email")
.field("enabled");
// Generates database-specific syntax:
// PostgreSQL: settings->'notifications'->'email'->'enabled'
// MySQL: JSON_EXTRACT(settings, '$.notifications.email.enabled')
// SQLite: json_extract(settings, '$.notifications.email.enabled')
// MSSQL: JSON_VALUE(settings, '$.notifications.email.enabled')
// Array access
let first_item = JsonPath::new("items").index(0).field("name");
// PostgreSQL: items->0->'name'
// MySQL: JSON_EXTRACT(items, '$[0].name')
// Get as text (unquoted)
let email_text = JsonPath::new("profile").field("email").text();
// PostgreSQL: profile->>'email'
// MySQL: JSON_UNQUOTE(JSON_EXTRACT(profile, '$.email'))`;
const jsonFilter = `use prax::json::{JsonFilter, JsonOp};
// Filter by JSON field
let users = client
.user()
.find_many()
.where(JsonFilter::path("settings.theme").equals("dark"))
.exec()
.await?;
// PostgreSQL: WHERE settings->>'theme' = 'dark'
// Contains (JSONB containment)
let admins = client
.user()
.find_many()
.where(JsonFilter::path("roles").contains(json!(["admin"])))
.exec()
.await?;
// PostgreSQL: WHERE roles @> '["admin"]'
// Has key
let verified = client
.user()
.find_many()
.where(JsonFilter::path("metadata").has_key("verified_at"))
.exec()
.await?;
// PostgreSQL: WHERE metadata ? 'verified_at'
// Has any key
let with_social = client
.user()
.find_many()
.where(JsonFilter::path("social").has_any_key(["twitter", "github"]))
.exec()
.await?;
// PostgreSQL: WHERE social ?| array['twitter', 'github']
// JSON path match (PostgreSQL 12+)
let expensive = client
.product()
.find_many()
.where(JsonFilter::path_match("$.price ? (@ > 100)"))
.exec()
.await?;`;
const jsonMutation = `use prax::json::JsonOp;
// Set a JSON field
client
.user()
.update(user::id::equals(1))
.json_set("settings", "theme", json!("dark"))
.exec()
.await?;
// PostgreSQL: UPDATE users SET settings = jsonb_set(settings, '{theme}', '"dark"')
// Insert into JSON (only if key doesn't exist)
client
.user()
.update(user::id::equals(1))
.json_insert("settings", "new_feature", json!(true))
.exec()
.await?;
// Remove a JSON key
client
.user()
.update(user::id::equals(1))
.json_remove("settings", "deprecated_field")
.exec()
.await?;
// PostgreSQL: UPDATE users SET settings = settings - 'deprecated_field'
// Array append
client
.user()
.update(user::id::equals(1))
.json_array_append("settings", "tags", json!("vip"))
.exec()
.await?;
// PostgreSQL: UPDATE users SET settings = jsonb_set(settings, '{tags}', settings->'tags' || '"vip"')
// Increment numeric value in JSON
client
.product()
.update(product::id::equals(1))
.json_increment("stats", "views", 1)
.exec()
.await?;`;
const jsonAgg = `use prax::json::JsonAgg;
// Aggregate rows into JSON array
let result = client
.raw_query(
r#"
SELECT
u.id,
u.name,
json_agg(json_build_object('id', p.id, 'title', p.title)) as posts
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id, u.name
"#,
[]
)
.await?;
// Build JSON object from columns
let stats = JsonAgg::build_object([
("total_users", "COUNT(*)"),
("active_users", "COUNT(*) FILTER (WHERE active)"),
("avg_age", "AVG(age)"),
])
.build_postgres();
// PostgreSQL: json_build_object('total_users', COUNT(*), 'active_users', COUNT(*) FILTER (WHERE active), ...)
// Aggregate into array with ordering
let ordered = JsonAgg::array_agg("name")
.order_by("created_at DESC")
.filter("active = true")
.build_postgres();
// PostgreSQL: json_agg(name ORDER BY created_at DESC) FILTER (WHERE active)`;
const jsonIndex = `use prax::json::{JsonIndex, JsonIndexBuilder};
// GIN index for JSONB containment queries (PostgreSQL)
let gin_index = JsonIndexBuilder::new("user_settings_idx")
.table("users")
.column("settings")
.using("GIN")
.ops_class("jsonb_path_ops") // Optimized for @> queries
.build();
// CREATE INDEX user_settings_idx ON users USING GIN (settings jsonb_path_ops)
// Expression index for specific JSON path
let email_index = JsonIndexBuilder::new("user_email_idx")
.table("users")
.expression("(profile->>'email')")
.build();
// CREATE INDEX user_email_idx ON users ((profile->>'email'))
// MySQL generated column + index
let mysql_index = JsonIndexBuilder::new("user_theme_idx")
.table("users")
.generated_column("theme", "settings->>'$.theme'", "VARCHAR(50)")
.build();
// ALTER TABLE users ADD COLUMN theme VARCHAR(50) GENERATED ALWAYS AS (settings->>'$.theme') STORED;
// CREATE INDEX user_theme_idx ON users (theme);`;
const mongoDocument = `use prax::json::mongodb::{UpdateOp, ArrayOp, UpdateBuilder};
// MongoDB document operations
let update = UpdateBuilder::new()
// Set fields
.set("profile.bio", "Software Engineer")
.set("updatedAt", Bson::DateTime(now()))
// Increment
.inc("stats.loginCount", 1)
// Unset (remove field)
.unset("tempField")
// Rename field
.rename("oldName", "newName")
// Min/Max (only update if new value is less/greater)
.min("stats.minScore", 50)
.max("stats.maxScore", 100)
// Multiply
.mul("balance", 1.1) // Increase by 10%
.build();
// Array operations
let array_update = UpdateBuilder::new()
// Push to array
.push("tags", "premium")
// Push multiple with sort and slice
.push_each("scores", [95, 87, 92])
.sort(-1) // Sort descending
.slice(10) // Keep top 10
// Pull (remove from array)
.pull("tags", "trial")
// Pull matching condition
.pull_all("notifications", [
doc! { "read": true, "age": { "$gt": 30 } }
])
// Add to set (only if not exists)
.add_to_set("roles", "member")
// Pop first or last
.pop("queue", -1) // Remove first element
.build();
// Positional updates (update matched array element)
client.orders().update_one(
doc! { "_id": order_id, "items.productId": product_id },
doc! { "$set": { "items.$.quantity": 5 } } // Update matched item
).await?;
// Array filters for nested arrays
client.orders().update_one(
doc! { "_id": order_id },
doc! { "$set": { "items.$[elem].shipped": true } },
UpdateOptions::builder()
.array_filters([doc! { "elem.status": "ready" }])
.build()
).await?;`;
const nestedDocuments = `// Schema with embedded documents
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
email String @unique
profile Profile // Embedded document
settings Json // Flexible JSON
}
type Profile {
firstName String
lastName String
avatar String?
social Social?
}
type Social {
twitter String?
github String?
linkedin String?
}
// Query nested fields
let users = client
.user()
.find_many()
.where(user::profile::is(
profile::firstName::contains("John")
))
.exec()
.await?;
// MongoDB dot notation
// { "profile.firstName": { "$regex": "John" } }
// Update nested fields
client
.user()
.update(user::id::equals(user_id))
.data(user::Update {
profile: Some(user::profile::update(profile::Update {
avatar: Some("https://example.com/avatar.jpg".into()),
..Default::default()
})),
..Default::default()
})
.exec()
.await?;`;
---
<DocsLayout title="JSON & Document Operations - Prax ORM">
<article class="max-w-4xl mx-auto px-6 py-12">
<header class="mb-12">
<h1 class="text-4xl font-bold mb-4">JSON & Document Operations</h1>
<p class="text-xl text-muted">
Work with JSON columns, nested documents, and flexible schemas across all databases.
</p>
</header>
<div class="space-y-12">
<!-- Introduction -->
<section>
<h2 class="text-2xl font-semibold mb-4">Overview</h2>
<p class="text-muted mb-4">
Prax provides comprehensive JSON support for storing and querying semi-structured data,
from PostgreSQL's JSONB to MongoDB's native BSON documents.
</p>
<div class="overflow-x-auto">
<table class="w-full text-sm">
<thead>
<tr class="border-b border-border">
<th class="text-left py-3 px-4 font-semibold">Feature</th>
<th class="text-left py-3 px-4 font-semibold">PostgreSQL</th>
<th class="text-left py-3 px-4 font-semibold">MySQL</th>
<th class="text-left py-3 px-4 font-semibold">SQLite</th>
<th class="text-left py-3 px-4 font-semibold">MSSQL</th>
<th class="text-left py-3 px-4 font-semibold">MongoDB</th>
</tr>
</thead>
<tbody class="text-muted">
<tr class="border-b border-border">
<td class="py-3 px-4">JSON Type</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> JSONB</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> JSON</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> JSON</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> NVARCHAR</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> BSON</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">Path Queries</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> ->, ->></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> ->, ->></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> json_extract</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> JSON_VALUE</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> Dot notation</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">JSON Indexing</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> GIN</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> Generated</td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> Native</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">Containment</td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> @></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> JSON_CONTAINS</td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-muted">❌</span></td>
<td class="py-3 px-4"><span class="text-success-400">✅</span> $elemMatch</td>
</tr>
</tbody>
</table>
</div>
</section>
<!-- JSON Path -->
<section>
<h2 class="text-2xl font-semibold mb-4">JSON Path Queries</h2>
<p class="text-muted mb-4">
Navigate JSON structures with a cross-database path API.
</p>
<CodeBlock code={jsonPath} lang="rust" filename="src/json.rs" />
</section>
<!-- Filtering -->
<section>
<h2 class="text-2xl font-semibold mb-4">Filtering JSON Data</h2>
<p class="text-muted mb-4">
Use JSON-specific filters in your queries.
</p>
<CodeBlock code={jsonFilter} lang="rust" filename="src/queries.rs" />
</section>
<!-- Mutations -->
<section>
<h2 class="text-2xl font-semibold mb-4">JSON Mutations</h2>
<p class="text-muted mb-4">
Update specific fields within JSON columns without replacing the entire document.
</p>
<CodeBlock code={jsonMutation} lang="rust" filename="src/mutations.rs" />
</section>
<!-- Aggregations -->
<section>
<h2 class="text-2xl font-semibold mb-4">JSON Aggregations</h2>
<p class="text-muted mb-4">
Build JSON objects and arrays from query results.
</p>
<CodeBlock code={jsonAgg} lang="rust" filename="src/aggregations.rs" />
</section>
<!-- Indexes -->
<section>
<h2 class="text-2xl font-semibold mb-4">JSON Indexing</h2>
<p class="text-muted mb-4">
Create indexes for efficient JSON queries.
</p>
<CodeBlock code={jsonIndex} lang="rust" filename="src/indexes.rs" />
<div class="mt-4 grid md:grid-cols-2 gap-4">
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-primary-400">GIN Index (PostgreSQL)</h4>
<p class="text-muted text-sm">
Use <code>jsonb_path_ops</code> for containment queries (@>).
Use default ops for key existence (?).
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-primary-400">Generated Columns (MySQL)</h4>
<p class="text-muted text-sm">
Extract JSON values into virtual columns and index those
for efficient filtering.
</p>
</div>
</div>
</section>
<!-- MongoDB -->
<section>
<h2 class="text-2xl font-semibold mb-4">MongoDB Document Operations</h2>
<p class="text-muted mb-4">
MongoDB's native document model with atomic update operators.
</p>
<CodeBlock code={mongoDocument} lang="rust" filename="src/mongodb.rs" />
</section>
<!-- Nested Documents -->
<section>
<h2 class="text-2xl font-semibold mb-4">Nested Documents</h2>
<p class="text-muted mb-4">
Define embedded document types in your schema and query them type-safely.
</p>
<CodeBlock code={nestedDocuments} lang="prax" filename="prax/schema.prax" />
</section>
<!-- Best Practices -->
<section>
<h2 class="text-2xl font-semibold mb-4">Best Practices</h2>
<div class="grid gap-4">
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-success-400">Use JSONB over JSON (PostgreSQL)</h4>
<p class="text-muted text-sm">
JSONB is binary, supports indexing, and is faster for most operations.
Use JSON only when you need to preserve key order or whitespace.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-success-400">Index Hot Paths</h4>
<p class="text-muted text-sm">
If you frequently query a specific JSON path, create an expression index
on that path rather than a full GIN index.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-warning-400">Validate JSON Structure</h4>
<p class="text-muted text-sm">
JSON columns accept any valid JSON. Use CHECK constraints or application
validation to ensure schema consistency.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-info-400">Consider Embedded vs Relations</h4>
<p class="text-muted text-sm">
In MongoDB, embed frequently-accessed data together. In SQL databases,
use JSON for truly flexible data, not as a substitute for proper relations.
</p>
</div>
</div>
</section>
</div>
</article>
</DocsLayout>