Disclaimer: This project was generated using AI and is provided as-is. It may contain bugs, edge cases that haven't been tested, or unexpected behavior. Use at your own risk and thoroughly test before using in production environments. Contributions and bug reports are welcome!

A powerful Directus extension that seamlessly integrates JSON field querying using the _json operator for filtering and json() function for field selection - perfectly aligned with Directus conventions.

• _json Operator - Filter JSON fields using familiar Directus operators
• json() Function - Select specific values from JSON fields
• Relational Support - Query JSON fields through M2O, O2M, M2M, M2A, and A2O relations
• Consistent API - Works like native Directus functionality
• Full Aggregation Support - Sum, count, average on JSON values with groupBy
• Dynamic Variables - Use $CURRENT_USER, $NOW, etc. in JSON filters
• High Performance - Database-optimized queries
• Dual Access - Use via hook middleware on /items/:collection or dedicated endpoint
• Permission Aware - Respects Directus row-level and field-level permissions

• Architecture
• JSON Explorer Module
• JSON Profiler Module
• JSON Playground Module
• Configuration
• Permissions
• Core Concepts
• Query Parameters Reference
• JSON Field Selection
• JSON Filtering
• Supported Operators
• Sorting by JSON Values
• Aggregation
• Relational Queries
• Dynamic Variables
• Real-World Examples
• Database Support
• Performance Optimization
• Testing

The extension provides two ways to access JSON querying functionality:

Intercepts requests to the standard Directus /items/:collection endpoint when JSON-specific syntax is detected. This provides seamless integration with existing Directus API usage.

A standalone endpoint at /json/:collection for explicit JSON query operations.

When using JSON-specific syntax (json() functions or _json operators), requests to the standard Directus items endpoint are automatically intercepted:

GET /items/:collection?fields=json(data.field)&filter={"data":{"_json":{...}}}

For explicit JSON query operations:

GET /json/:collection

Supports all the same query parameters as the hook-based access.

GET /json/health

Returns extension status and database capabilities (for authenticated users).

The extension includes a visual JSON Explorer module that provides a GUI for building and testing JSON queries directly within Directus.

After installing the extension, navigate to the JSON Explorer module in the Directus sidebar (look for the { } icon).

• Select any collection from the dropdown
• Choose a JSON field to query
• The module automatically detects JSON/code fields

• Automatically analyzes sample data to detect the JSON structure
• Displays a tree view with type indicators:
  • { } Object (purple)
  • [ ] Array (orange)
  • Aa String (green)
  • 123 Number (blue)
  • ✓ Boolean (yellow)
• Click the filter icon on any leaf node to add it as a filter condition

• Build complex filters without writing JSON
• Add conditions with path, operator, and value inputs
• Supported operators:
  • Comparison: equals, not equals, less than, greater than, etc.
  • String: contains, starts with, ends with
  • Null checks: is null, is not null, is empty, is not empty
• Create AND/OR groups for complex logic
• Nest groups within groups for advanced queries
• Toggle between "Match ALL" (AND) and "Match ANY" (OR)

• See the generated Directus filter query in real-time
• Copy the query to clipboard for use in your application

• Run queries and see side-by-side performance metrics:
  • DB JSON Query: Time using database-level JSON filtering
  • API + Client Filter: Time fetching all data and filtering client-side
• Visual indicator shows which method is faster and by how much
• Demonstrates the performance benefits of database-level JSON queries

• View matching records in a table format
• Automatic column detection from result data
• Handles nested JSON display with truncation

1. Select Collection: Choose products from the dropdown
2. Select JSON Field: Choose data (your JSON field)
3. Explore Schema: The tree view shows the structure:

   ├── pricing
   │   ├── base (number)
   │   ├── currency (string)
   │   └── discount (number)
   └── specs
       └── weight (number)
   

4. Add Filter: Click the filter icon on pricing.base
5. Configure Condition: Set operator to "greater than" and value to 50
6. Add More Filters: Click "Add Condition" or "Add Group" for complex logic
7. Run Query: Click "Run Query" to execute
8. View Results: See matching records and performance comparison

The module demonstrates the power of database-level JSON queries:

┌─────────────────────────────┬─────────────────────────────┐
│ DB JSON Query               │ API + Client Filter         │
│ ⚡ 19ms                    │ ☁️ 25ms                    │
│ ✓ 1.3x faster             │                             │
└─────────────────────────────┴─────────────────────────────┘

┌─────────────────────────────┬─────────────────────────────┐
│ DB JSON Query               │ API + Client Filter         │
│ ⚡ 20ms                     │ ☁️ 19ms                    │
│                             │ ✓ 1.1x faster              │
└─────────────────────────────┴─────────────────────────────┘

Note: The performance can vary wildly based on selected fields, filters and sorting.

This comparison shows:

• DB JSON Query: Uses the extension's _json operator for server-side filtering
• API + Client Filter: Fetches all records and filters in JavaScript (simulates apps without JSON query support)

The extension includes a JSON Profiler that analyzes the structure and content of JSON fields across your collections, helping you understand your data before building queries.

After installing the extension, navigate to the JSON Module in the Directus sidebar and select Profiler from the navigation.

• Select any collection from the dropdown
• Optionally filter to specific JSON fields (defaults to all JSON fields)
• Configure sample size for analysis (100 to 20,000 rows)

The profiler analyzes sample data and provides:

• Path Discovery: Automatically discovers all JSON paths in your data
• Type Distribution: Shows what data types exist at each path (string, number, boolean, array, object, null)
• Presence Statistics: Percentage of records containing each path
• Value Statistics:
  • For strings: top occurring values with counts
  • For numbers: min, max, average, sum
  • For arrays: length statistics
• Example Values: Sample values from your actual data

For each discovered path, the profiler shows:

├── user
│   ├── name (string) - 98.5% present
│   │   Top values: "John" (45), "Jane" (32), "Bob" (28)
│   ├── age (number) - 95.2% present
│   │   Min: 18, Max: 65, Avg: 34.5
│   └── tags (array) - 67.3% present
│       Avg length: 3.2, Max length: 10

• Groups records by their JSON structure
• Identifies inconsistencies in your data
• Shows how many records match each structure pattern

1. Before Building Queries: Understand what paths exist and their data types
2. Data Quality Assessment: Find missing fields, type inconsistencies
3. Schema Discovery: Document the actual structure of schemaless JSON data
4. Performance Planning: Identify frequently used paths for indexing

1. Select Collection: Choose events from the dropdown
2. Configure Sample: Set sample size to 1,000 rows
3. Run Profile: Click "Profile" to analyze
4. Review Results:
   • See all discovered paths with statistics
   • Identify paths with low presence (potential data issues)
   • Find numeric fields suitable for aggregation
5. Export Insights: Use findings to build optimized queries in the Explorer

The extension includes a JSON Playground for testing JSON path extraction and understanding how JSON data will be processed.

After installing the extension, navigate to the JSON Module in the Directus sidebar and select Playground from the navigation.

• Paste or type JSON data directly
• Format JSON with one click
• Paste from clipboard support
• Load sample JSON for testing

• Enter JSON paths to test extraction
• See extracted values in real-time
• Understand how json() function syntax works

• Automatically detects structure from input JSON
• Displays type information for each path
• Click paths to copy them

1. Learning: Understand JSON path syntax before using in queries
2. Debugging: Test why a path isn't returning expected values
3. Path Discovery: Explore complex JSON to find the right paths
4. Validation: Verify JSON structure before importing data

1. Paste JSON: Input your JSON data
2. View Structure: See the detected schema tree
3. Test Paths: Try different path expressions
4. Copy to Query: Use discovered paths in your actual queries

The extension can be configured using environment variables. All features are enabled by default.

# Disable hook interception (use only the dedicated endpoint)
JSON_QUERIES_HOOK_ENABLED=false

# Disable the dedicated endpoint (use only hook interception)
JSON_QUERIES_ENDPOINT_ENABLED=false

# Disable the health endpoint
JSON_QUERIES_HEALTH_ENABLED=false

# Disable all features
JSON_QUERIES_HOOK_ENABLED=false
JSON_QUERIES_ENDPOINT_ENABLED=false
JSON_QUERIES_HEALTH_ENABLED=false

# Enable explicitly
JSON_QUERIES_HOOK_ENABLED=true

Note: Environment variables use flexible parsing. Accepted falsy values: false, 0, "" (empty string), no, n (case-insensitive). Any other value (or unset) is treated as enabled.

When a feature is disabled, a log message will be emitted at startup indicating which component has been disabled.

The extension fully respects Directus permissions, ensuring users can only access data they're authorized to see.

Row-level permission filters are automatically applied to all JSON queries. If a user's role has filters configured for a collection (e.g., "only see own items"), those filters are merged with the query filters.

# User with filter: { "owner": { "_eq": "$CURRENT_USER" } }
# Request:
GET /items/documents?filter={"data":{"_json":{"status":{"_eq":"active"}}}}

# Actual query executed includes both filters:
# WHERE owner = <current_user_id> AND json_extract(data, '$.status') = 'active'

Field-level permissions are validated before executing queries:

• Forbidden Fields: If a user requests a field they don't have access to, the request returns 403 Forbidden
• JSON Fields: The base JSON field (e.g., data) must be readable for json(data.path) to work
• Relational Fields: Permission checks follow the relation chain

# User without access to 'data' field
GET /items/products?fields=id,json(data.pricing.base)
# Returns: 403 Forbidden

Aggregation queries also respect permissions:

• Row filters are applied before aggregation
• Field permissions are checked for all aggregated and grouped fields
• Users cannot aggregate on fields they cannot read

• Error messages intentionally don't distinguish between "forbidden" and "not found" to prevent information disclosure
• Permission filters use the same dynamic variable resolution ($CURRENT_USER, $NOW, etc.)
• Admin users bypass permission checks as expected

This extension requires Directus 10.x or later for full permission support, as it uses the PoliciesService for permission resolution.

Use _json to filter JSON fields, similar to other Directus operators:

{
  "field_name": {
    "_json": {
      "path.to.value": {
        "_eq": "something"
      }
    }
  }
}

Extract specific values from JSON fields in field selection:

json(field_name.path.to.value)

Use the alias parameter for custom names:

fields=id,name&alias[custom_name]=json(field_name.path.to.value)

Consider a products collection with a JSON field data:

{
  "id": 1,
  "name": "Product A",
  "status": "published",
  "data": {
    "specs": {
      "weight": 2.5,
      "dimensions": {
        "width": 10,
        "height": 20,
        "depth": 5
      }
    },
    "pricing": {
      "base": 99.99,
      "discount": 10,
      "currency": "USD"
    },
    "tags": ["electronics", "portable", "wireless"],
    "variants": [
      { "color": "black", "sku": "BLK001", "stock": 50 },
      { "color": "white", "sku": "WHT001", "stock": 30 }
    ]
  }
}

Extract values from JSON fields using the json() function:

GET /items/products?fields=id,name,json(data.pricing.base),json(data.specs.weight)

Response:

{
  "data": [{
    "id": 1,
    "name": "Product A",
    "data_pricing_base": 99.99,
    "data_specs_weight": 2.5
  }]
}

Access deeply nested values:

GET /items/products?fields=id,json(data.specs.dimensions.width),json(data.specs.dimensions.height)

Response:

{
  "data": [{
    "id": 1,
    "data_specs_dimensions_width": 10,
    "data_specs_dimensions_height": 20
  }]
}

Access specific array elements using bracket notation:

GET /items/products?fields=id,json(data.variants[0].sku),json(data.variants[0].stock)

Response:

{
  "data": [{
    "id": 1,
    "data_variants_0_sku": "BLK001",
    "data_variants_0_stock": 50
  }]
}

Access multiple array indices:

GET /items/products?fields=id,json(data.variants[0].color),json(data.variants[1].color)

Use the alias parameter to give extracted values custom names:

GET /items/products?fields=id,name&alias={"price":"json(data.pricing.base)","weight":"json(data.specs.weight)"}

Response:

{
  "data": [{
    "id": 1,
    "name": "Product A",
    "price": 99.99,
    "weight": 2.5
  }]
}

Multiple aliases:

GET /items/products?fields=id&alias={
  "base_price": "json(data.pricing.base)",
  "discount_pct": "json(data.pricing.discount)",
  "currency": "json(data.pricing.currency)",
  "first_sku": "json(data.variants[0].sku)"
}

GET /items/products?fields=id,name,status,json(data.pricing.base),json(data.specs.weight)

Filter by a single JSON path:

GET /items/products?filter={"data":{"_json":{"pricing.base":{"_gte":50}}}}

Apply multiple conditions to the same JSON field:

GET /items/products?filter={
  "data": {
    "_json": {
      "pricing.base": { "_between": [50, 200] },
      "specs.weight": { "_lte": 5 },
      "pricing.currency": { "_eq": "USD" }
    }
  }
}

Filter on deeply nested values:

GET /items/products?filter={
  "data": {
    "_json": {
      "specs.dimensions.width": { "_lte": 15 },
      "specs.dimensions.height": { "_gte": 10 }
    }
  }
}

Filter by array element values:

GET /items/products?filter={
  "data": {
    "_json": {
      "variants[0].stock": { "_gte": 40 }
    }
  }
}

Filter across multiple array indices:

GET /items/products?filter={
  "_or": [
    { "data": { "_json": { "variants[0].stock": { "_lte": 10 } } } },
    { "data": { "_json": { "variants[1].stock": { "_lte": 10 } } } }
  ]
}

GET /items/products?filter={
  "data": {
    "_json": {
      "variants[0].sku": { "_starts_with": "BLK" }
    }
  }
}

GET /items/products?filter={
  "data": {
    "_json": {
      "specs.description": { "_contains": "portable" }
    }
  }
}

GET /items/products?filter={
  "_and": [
    { "status": { "_eq": "published" } },
    { "data": {
        "_json": {
          "pricing.currency": { "_eq": "USD" },
          "pricing.base": { "_lt": 100 }
        }
      }
    }
  ]
}

GET /items/products?filter={
  "_or": [
    { "data": { "_json": { "pricing.discount": { "_gte": 20 } } } },
    { "data": { "_json": { "pricing.base": { "_lte": 50 } } } }
  ]
}

GET /items/products?filter={
  "_and": [
    { "status": { "_eq": "published" } },
    {
      "_or": [
        { "data": { "_json": { "pricing.discount": { "_gte": 15 } } } },
        {
          "_and": [
            { "data": { "_json": { "pricing.base": { "_lte": 50 } } } },
            { "data": { "_json": { "specs.weight": { "_lte": 2 } } } }
          ]
        }
      ]
    }
  ]
}

All standard Directus operators work with the _json operator:

Sort by a JSON path (ascending by default):

GET /items/products?sort=json(data.pricing.base)

Prefix with - for descending order:

GET /items/products?sort=-json(data.pricing.base)

GET /items/products?sort=json(data.pricing.currency),-json(data.pricing.base)

This sorts by currency ascending, then by base price descending within each currency.

GET /items/products?sort=status,-json(data.pricing.base),name

GET /items/products?sort=-json(data.variants[0].stock)

GET /items/products?sort=json(data.specs.dimensions.width)

GET /items/products?aggregate={"sum":["json(data.pricing.base)"],"avg":["json(data.specs.weight)"],"count":["*"]}

Response:

{
  "data": [{
    "sum_data_pricing_base": 4599.50,
    "avg_data_specs_weight": 3.2,
    "count": 45
  }]
}

GET /items/products?aggregate={
  "sum": ["json(data.pricing.base)", "json(data.variants[0].stock)"],
  "avg": ["json(data.specs.weight)"],
  "min": ["json(data.pricing.base)"],
  "max": ["json(data.pricing.base)"],
  "count": ["*"]
}

Group by a regular field:

GET /items/products?aggregate={"sum":["json(data.pricing.base)"],"count":["*"]}&groupBy=status

Response:

{
  "data": [
    { "status": "published", "sum_data_pricing_base": 3500.00, "count": 35 },
    { "status": "draft", "sum_data_pricing_base": 1099.50, "count": 10 }
  ]
}

GET /items/products?aggregate={"sum":["json(data.pricing.base)"],"count":["*"]}&groupBy=json(data.pricing.currency)

Response:

{
  "data": [
    { "data_pricing_currency": "USD", "sum_data_pricing_base": 2500.00, "count": 25 },
    { "data_pricing_currency": "EUR", "sum_data_pricing_base": 1800.00, "count": 18 },
    { "data_pricing_currency": "GBP", "sum_data_pricing_base": 299.50, "count": 2 }
  ]
}

GET /items/products?aggregate={
  "sum": ["json(data.variants[0].stock)"],
  "avg": ["json(data.pricing.base)"]
}&groupBy=status,json(data.pricing.currency)

Response:

{
  "data": [
    {
      "status": "published",
      "data_pricing_currency": "USD",
      "sum_data_variants_0_stock": 500,
      "avg_data_pricing_base": 89.99
    },
    {
      "status": "published",
      "data_pricing_currency": "EUR",
      "sum_data_variants_0_stock": 300,
      "avg_data_pricing_base": 79.99
    }
  ]
}

GET /items/products?aggregate={
  "sum": ["json(data.pricing.base)"],
  "count": ["*"]
}&groupBy=json(data.pricing.currency)&filter={
  "status": { "_eq": "published" },
  "data": { "_json": { "pricing.base": { "_gte": 50 } } }
}

The extension supports querying JSON fields through all Directus relation types.

Consider articles with an author field (M2O to authors):

Select related field:

GET /items/articles?fields=title,author.name,author.email

Select JSON from related collection:

GET /items/articles?fields=title,json(author.bio.summary)

Response:

{
  "data": [{
    "title": "Introduction to TypeScript",
    "json_author_bio_summary": "Senior developer with 10 years experience"
  }]
}

Select nested JSON path from related collection:

GET /items/articles?fields=title,json(author.bio.social.twitter)

Filter by related collection JSON field:

GET /items/articles?fields=title,author.name&filter={
  "author": {
    "bio": {
      "_json": {
        "summary": { "_contains": "developer" }
      }
    }
  }
}

Combine local and M2O filters:

GET /items/articles?fields=title,author.name,json(metadata.views)&filter={
  "_and": [
    { "author": { "name": { "_eq": "John Writer" } } },
    { "metadata": { "_json": { "views": { "_gt": 1000 } } } }
  ]
}

Sort by related JSON field:

GET /items/articles?fields=title&alias={"author_summary":"json(author.bio.summary)"}&sort=json(author.bio.summary)

Use aliases for M2O JSON fields:

GET /items/articles?fields=title&alias={
  "writer": "author.name",
  "author_summary": "json(author.bio.summary)"
}

Consider authors with a posts field (O2M to posts):

Aggregate through O2M relation:

GET /items/authors?aggregate={"sum":["json(posts.metadata.views)"],"count":["posts"]}&groupBy=name

Consider products with a categories field (M2M through junction table):

Select through M2M:

GET /items/products?fields=name,categories.category_id.name

Filter by M2M related JSON:

GET /items/products?filter={
  "categories": {
    "category_id": {
      "metadata": {
        "_json": {
          "featured": { "_eq": true }
        }
      }
    }
  }
}

GET /items/articles?fields=id,title&alias={
  "writer_name": "author.name",
  "writer_email": "author.email",
  "writer_bio": "json(author.bio.summary)",
  "twitter": "json(author.bio.social.twitter)"
}

The extension supports Directus dynamic variables in JSON filters:

The offset format is: ±number unit where unit can be:

• second / seconds
• minute / minutes
• hour / hours
• day / days
• week / weeks
• month / months
• year / years

Examples:

• $NOW(-7 days) - 7 days ago
• $NOW(+1 hour) - 1 hour from now
• $NOW(-30 minutes) - 30 minutes ago
• $NOW(-1 month) - 1 month ago
• $NOW(+1 year) - 1 year from now

Filter by current user:

GET /items/documents?filter={
  "data": {
    "_json": {
      "owner_id": { "_eq": "$CURRENT_USER" }
    }
  }
}

Filter by date range:

GET /items/analytics?filter={
  "_and": [
    { "data": { "_json": { "recorded_at": { "_gte": "$NOW(-7 days)" } } } },
    { "data": { "_json": { "recorded_at": { "_lte": "$NOW" } } } }
  ]
}

Combine with regular filters:

GET /items/tasks?filter={
  "_and": [
    { "status": { "_eq": "active" } },
    { "data": {
        "_json": {
          "assigned_to": { "_eq": "$CURRENT_USER" },
          "due_date": { "_lte": "$NOW(+7 days)" }
        }
      }
    }
  ]
}

const response = await fetch('/items/products?' + new URLSearchParams({
  fields: 'id,name',
  alias: JSON.stringify({
    price: 'json(data.pricing.base)',
    discount: 'json(data.pricing.discount)',
    final_price: 'json(data.pricing.final)'
  }),
  filter: JSON.stringify({
    _and: [
      { status: { _eq: 'active' } },
      { data: {
          _json: {
            'pricing.discount': { _gte: 15 },
            'pricing.base': { _between: [50, 200] }
          }
        }
      }
    ]
  }),
  sort: '-json(data.pricing.discount)',
  limit: '20'
}));

const response = await fetch('/items/analytics?' + new URLSearchParams({
  aggregate: JSON.stringify({
    sum: ['json(data.metrics.pageviews)', 'json(data.metrics.clicks)'],
    avg: ['json(data.metrics.bounce_rate)', 'json(data.metrics.session_duration)'],
    max: ['json(data.metrics.concurrent_users)']
  }),
  groupBy: 'json(data.location.country),json(data.device.type)',
  filter: JSON.stringify({
    created_at: { _gte: '$NOW(-7 days)' }
  })
}));

const response = await fetch('/items/products?' + new URLSearchParams({
  fields: 'id,name',
  alias: JSON.stringify({
    sku: 'json(data.variants[0].sku)',
    stock: 'json(data.variants[0].stock)',
    reorder_level: 'json(data.inventory.reorder_level)'
  }),
  filter: JSON.stringify({
    _and: [
      { status: { _eq: 'active' } },
      {
        _or: [
          { data: { _json: { 'variants[0].stock': { _lte: 10 } } } },
          { data: { _json: { 'variants[1].stock': { _lte: 10 } } } },
          { data: { _json: { 'variants[2].stock': { _lte: 10 } } } }
        ]
      }
    ]
  }),
  sort: 'json(data.variants[0].stock)'
}));

const response = await fetch('/items/articles?' + new URLSearchParams({
  fields: 'id,title,author.name',
  alias: JSON.stringify({
    views: 'json(metadata.views)',
    likes: 'json(metadata.likes)',
    read_time: 'json(metadata.read_time_minutes)'
  }),
  filter: JSON.stringify({
    _and: [
      { status: { _eq: 'published' } },
      { author: { bio: { _json: { 'expertise': { _contains: 'technology' } } } } },
      { metadata: { _json: { 'views': { _gte: 1000 } } } }
    ]
  }),
  sort: '-json(metadata.views)',
  limit: '10'
}));

const response = await fetch('/items/content?' + new URLSearchParams({
  fields: 'id,title,json(data.category)',
  filter: JSON.stringify({
    _and: [
      { status: { _eq: 'published' } },
      { data: {
          _json: {
            'target_audience': { _eq: '$CURRENT_ROLE' },
            'published_at': { _lte: '$NOW' },
            'expires_at': { _gte: '$NOW' }
          }
        }
      }
    ]
  }),
  sort: '-json(data.priority)',
  limit: '5'
}));

const response = await fetch('/items/orders?' + new URLSearchParams({
  aggregate: JSON.stringify({
    sum: ['json(data.total.amount)'],
    count: ['*'],
    avg: ['json(data.total.amount)']
  }),
  groupBy: 'json(data.total.currency),json(data.payment.method)',
  filter: JSON.stringify({
    _and: [
      { status: { _eq: 'completed' } },
      { data: { _json: { 'created_at': { _gte: '$NOW(-30 days)' } } } }
    ]
  })
}));

const response = await fetch('/items/sensor_readings?' + new URLSearchParams({
  aggregate: JSON.stringify({
    avg: ['json(data.temperature)', 'json(data.humidity)'],
    min: ['json(data.temperature)'],
    max: ['json(data.temperature)']
  }),
  groupBy: 'json(data.location.room),json(data.location.floor)',
  filter: JSON.stringify({
    _and: [
      { data: { _json: { 'timestamp': { _gte: '$NOW(-24 hours)' } } } },
      { data: { _json: { 'sensor_type': { _eq: 'environmental' } } } }
    ]
  })
}));

GET /items/products?
  fields=id,name,json(data.pricing.base)&
  filter={"data":{"_json":{"pricing.currency":{"_eq":"USD"}}}}&
  search=wireless&
  sort=-json(data.pricing.base)&
  limit=10&
  page=2

PostgreSQL: Uses native JSONB operators (->, ->>, @>) for optimal performance. Supports indexing on JSON paths.

MySQL: Uses JSON_EXTRACT() and related functions. Supports generated columns for indexing.

SQLite: Requires the JSON1 extension. Uses json_extract() function.

MS SQL Server: Uses JSON_VALUE() for scalar extraction. Limited to scalar values.

Oracle: Uses JSON_VALUE() with path expressions.

Create indexes for frequently queried JSON paths:

PostgreSQL:

-- Index on entire JSON object
CREATE INDEX idx_data_pricing ON products USING GIN ((data->'pricing'));

-- Index on specific path (for equality checks)
CREATE INDEX idx_data_price ON products ((data->'pricing'->>'base'));

-- Expression index for numeric comparisons
CREATE INDEX idx_data_price_numeric ON products ((CAST(data->'pricing'->>'base' AS NUMERIC)));

MySQL 8.0+:

-- Functional index on JSON path
ALTER TABLE products ADD INDEX idx_data_pricing ((CAST(data->>'$.pricing.base' AS DECIMAL(10,2))));

-- Generated column with index (alternative)
ALTER TABLE products
  ADD COLUMN data_price DECIMAL(10,2) AS (CAST(data->>'$.pricing.base' AS DECIMAL(10,2))) STORED,
  ADD INDEX idx_data_price (data_price);

SQLite:

-- Expression index
CREATE INDEX idx_data_pricing ON products (json_extract(data, '$.pricing.base'));

1. Be Specific: Use exact paths instead of searching entire JSON structures
2. Index Common Paths: Create database indexes for frequently accessed JSON paths
3. Limit Results: Always use pagination for large datasets
4. Cache Results: Cache aggregation results that don't change frequently
5. Use Appropriate Types: Ensure JSON values are stored with correct types for comparison operations
6. Avoid Deep Nesting: Deeply nested queries may impact performance
7. Filter Early: Apply filters before aggregation to reduce data processing

The extension returns standard Directus error responses:

{
  "errors": [{
    "message": "Invalid JSON path in filter",
    "extensions": {
      "code": "INVALID_QUERY",
      "path": "data.invalid..path"
    }
  }]
}

Before:

SELECT id, data->>'$.pricing.base' as price
FROM products
WHERE CAST(data->>'$.pricing.base' AS DECIMAL) > 50

After:

GET /items/products?
  fields=id&
  alias={"price":"json(data.pricing.base)"}&
  filter={"data":{"_json":{"pricing.base":{"_gt":50}}}}

Before:

GET /items/products?query=data$.pricing.base&filter={"json_result":{"_gt":50}}

After:

GET /items/products?
  fields=json(data.pricing.base)&
  filter={"data":{"_json":{"pricing.base":{"_gt":50}}}}

The extension includes a comprehensive blackbox test suite that tests JSON queries against multiple database backends.

# Run tests against all databases (PostgreSQL, MySQL, MariaDB, SQLite, MSSQL)
npm run test:blackbox

# Run tests for a specific database
npm run test:blackbox:postgres
npm run test:blackbox:mysql
npm run test:blackbox:mariadb
npm run test:blackbox:sqlite
npm run test:blackbox:mssql

• Docker and Docker Compose
• Node.js 18+

The test suite covers:

• JSON Function in Fields: String values, numeric values, nested objects, array items, multiple paths
• JSON Operator in Filter: String/numeric comparisons, nested paths, array indexing, combinations
• All Comparison Operators: _eq, _neq, _lt, _lte, _gt, _gte, _in, _nin, _contains, _ncontains, _starts_with, _nstarts_with, _ends_with, _nends_with, _between, _nbetween, _null, _nnull, _empty, _nempty
• Logical Operators: _and, _or with JSON conditions
• JSON Function in Sort: String sorting, numeric sorting, nested objects, array items, ascending/descending
• Aggregation: sum, avg, min, max, count, countDistinct with JSON fields, groupBy with JSON paths
• Relational Queries: M2O field selection, M2O JSON selection, M2O filtering, M2O sorting, O2M aggregation, M2M queries
• Aliases: Custom field naming for JSON extractions
• Dynamic Variables: $CURRENT_USER, $NOW, $NOW(offset) in filters
• Endpoint Modes: Hook middleware (/items/:collection) and dedicated endpoint (/json/:collection)
• Edge Cases: Missing paths, special characters, decimal values, boolean handling, null relations

tests/blackbox/
├── docker-compose.yml          # Database containers and Directus instances
├── scripts/
│   └── run-tests.js            # Test runner (handles container lifecycle)
├── src/
│   ├── fields.test.ts          # JSON field selection tests
│   ├── filters.test.ts         # All filter operator tests
│   ├── sort.test.ts            # JSON sorting tests
│   ├── alias.test.ts           # Custom alias tests
│   ├── combinations.test.ts    # Combined functionality tests
│   ├── relations-m2o.test.ts   # Many-to-One relation tests
│   ├── relations-o2m.test.ts   # One-to-Many relation tests
│   ├── relations-m2m.test.ts   # Many-to-Many relation tests
│   └── utils/
│       ├── directus-client.ts  # API client utilities
│       └── test-fixtures.ts    # Test data and configuration
├── package.json
└── vitest.config.ts

Pull requests are welcome! Please ensure:

• Tests pass (npm run test:blackbox)
• Documentation is updated
• Code follows Directus conventions

MIT

Inspired by Directus PR #15889 and community feedback on JSON field querying needs.