Validate JSON scalar index creation and querying

Parent Epic: #451

Dependencies

• Implement parallel CRUD with experimental endpoints #453 (blocks this issue)

Overview

Validate LanceDB's JSON scalar index feature (GitHub issue #4516, PRs #4566, #4577) with real NodeSpace data. This validation directly addresses critical questions about dynamic schema support: multi-level nested property filtering, index creation performance, and sparse property handling.

Self-Contained Scope

• Migrate metadata from DataType::Utf8 (JSON string) to native JSON type
• Create scalar indexes on nested properties at various depths
• Test json_extract() filtering with real queries
• Measure index creation time across dataset sizes
• Document sparse property behavior (nodes without indexed properties)
• Zero impact on Turso implementation

Implementation Approach

Phase 1: Schema Migration

• Update LanceDB schema to use native JSON type for metadata
• Migrate from Field::new("metadata", DataType::Utf8, true)
• To: Field::new("metadata", JsonType::new(), false)
• Test backward compatibility with existing PoC data

Phase 2: Index Creation Testing

• Create indexes on single-level properties: metadata.status
• Create indexes on multi-level properties: metadata.customer.address.street
• Create indexes on deeply nested properties (3+ levels)
• Measure index creation time for 100, 1k, 10k, 100k nodes

Phase 3: Query Validation

• Test json_extract() filtering on indexed properties
• Compare query performance: indexed vs non-indexed properties
• Validate correct results for nested property filters
• Test complex queries with multiple JSON filters

Phase 4: Sparse Property Analysis

• Create index on property that doesn't exist in all nodes
• Measure performance impact of sparse vs dense properties
• Document null/undefined handling in filters
• Test adding new property to existing dataset

Acceptance Criteria

• Schema migrated to native JSON type successfully
• Indexes created on properties at 1, 2, and 3+ nesting levels
• json_extract() filters return correct results
• Index creation time documented for various dataset sizes
• Query performance measured (indexed vs non-indexed)
• Sparse property behavior documented and tested
• Multi-level nested property filtering validated (e.g., address.street.zip)
• Comprehensive test suite for JSON scalar indexes

Technical Specifications

Schema Migration:

// OLD (current PoC):
Field::new("metadata", DataType::Utf8, true)  // JSON string

// NEW (native JSON):
use arrow::datatypes::DataType;
Field::new("metadata", DataType::Utf8, false)  // Note: LanceDB may use custom JSON type

Index Creation Patterns:

// Single-level property
table.create_scalar_index("metadata.status", Default::default()).await?;

// Multi-level property
table.create_scalar_index("metadata.customer.address.street", Default::default()).await?;
table.create_scalar_index("metadata.customer.address.zip", Default::default()).await?;

// Deep nesting validation
table.create_scalar_index("metadata.project.team.lead.contact.email", Default::default()).await?;

Query Testing:

// Test json_extract() filtering
let results = table.query()
    .only_if("json_extract(metadata, '$.status') = 'done'")
    .execute().await?;

// Test multi-level filtering
let results = table.query()
    .only_if("json_extract(metadata, '$.customer.address.street') = 'Main St'")
    .execute().await?;

// Test sparse properties
let results = table.query()
    .only_if("json_extract(metadata, '$.estimatedHours') IS NOT NULL")
    .execute().await?;

Performance Measurement:

pub struct IndexCreationMetrics {
    dataset_size: usize,
    property_path: String,
    nesting_level: u8,
    creation_time_ms: f64,
    sparse_ratio: f32,
}

async fn measure_index_creation(
    table: &Table,
    property_path: &str,
    dataset_size: usize,
) -> Result<IndexCreationMetrics> {
    let start = Instant::now();
    table.create_scalar_index(property_path, Default::default()).await?;
    let duration = start.elapsed();
    
    let sparse_ratio = calculate_property_coverage(table, property_path).await?;
    
    Ok(IndexCreationMetrics {
        dataset_size,
        property_path: property_path.to_string(),
        nesting_level: property_path.split('.').count() as u8,
        creation_time_ms: duration.as_secs_f64() * 1000.0,
        sparse_ratio,
    })
}

Test Scenarios:

1. Multi-level deep properties:

#[test]
async fn test_multilevel_nested_indexing() {
    let node = Node {
        metadata: json!({
            "customer": {
                "name": "Acme Corp",
                "address": {
                    "street": "123 Main St",
                    "zip": "12345"
                }
            }
        }),
        ..default
    };
    
    table.create_scalar_index("metadata.customer.address.street", Default::default()).await?;
    table.create_scalar_index("metadata.customer.address.zip", Default::default()).await?;
    
    let results = table.query()
        .only_if("json_extract(metadata, '$.customer.address.zip') = '12345'")
        .execute().await?;
    
    assert_eq!(results.len(), 1);
}

2. Sparse property scan optimization:

#[test]
async fn test_sparse_property_indexing() {
    create_test_dataset(1000, 0.1).await?;
    
    let metrics = measure_index_creation(
        &table,
        "metadata.estimatedHours",
        1000
    ).await?;
    
    println!("Sparse property (10% coverage) index time: {}ms", metrics.creation_time_ms);
}

Resources & References

LanceDB JSON Scalar Index Feature:

• GitHub Issue: FTS: rows with equal _score come back in an order that changes between calls and processes; a limit inside a tie keeps arbitrary rows lancedb/lancedb#4516
• Implementation PRs: #4566, #4577

Architecture Context:

• /Users/malibio/Desktop/nodespace-lancedb-vs-tursodb.md

Existing PoC:

• nodespace-data-store/SCHEMA.md
• nodespace-data-store/src/lance_data_store_simple.rs

Related Issues:

• LanceDB Parallel Implementation for Database Evaluation #451 (parent)
• Implement parallel CRUD with experimental endpoints #453 (blocks this issue)

Definition of Done

• Native JSON type schema validated and working
• Multi-level nested property indexes confirmed functional
• Index creation time documented for all test scenarios
• Sparse property behavior documented
• Query performance measured and compared
• Comprehensive test suite covering all scenarios
• Documentation includes specific performance data
• Code passes cargo fmt and cargo clippy

🤖 Generated with Claude Code

Co-Authored-By: Claude noreply@anthropic.com