What does this PR do?

Enables indexing nested properties within embedded documents or maps contained in lists using the BY ITEM modifier, with query optimizer integration for direct nested property queries. Syntax: CREATE INDEX ON Photo (\tags.id` BY ITEM) NOTUNIQUE`.

Motivation

Users needed to index properties of embedded documents within lists (e.g., indexing the id field of Tag documents within a Photo's tags list). The existing BY ITEM implementation only supported indexing primitive list values, not nested properties.

-- Previously unsupported, now works:
CREATE VERTEX TYPE Photo;
CREATE DOCUMENT TYPE Tag;
CREATE PROPERTY Tag.id INTEGER;
CREATE PROPERTY Photo.tags LIST OF Tag;
CREATE INDEX ON Photo (`tags.id` BY ITEM) NOTUNIQUE;

-- Insert and query (uses index!):
INSERT INTO Photo SET id = 1, tags = [{'@type':'Tag', 'id': 100}];
SELECT FROM Photo WHERE tags.id CONTAINS 100;  -- Uses index Photo[tags.idbyitem]

Changes

TypeIndexBuilder (schema/TypeIndexBuilder.java)

• Distinguish between properties with literal dots in names vs. nested paths
• For nested paths: validate root property exists and is LIST type, use STRING key type
• Split property names on dot only when exact match fails and root property is a LIST

DocumentIndexer (database/DocumentIndexer.java)

• addListItemsToIndex: Extract nested property values from each list item before indexing
• updateListItemsInIndex: Compare extracted values for incremental index updates
• deleteListItemsFromIndex: Extract nested values before removing index entries
• getPropertyValue: Check record.has() first to handle properties with dots in names, then fall back to nested traversal

ContainsCondition (query/sql/parser/ContainsCondition.java)

• Enhanced isIndexAware() to recognize nested property paths for query optimization
• Handles both simple identifiers ("tags") and nested paths ("tags.id")
• Uses pattern matching for nested properties to enable index usage

Tests

• EmbeddedListIndexByItemTest: End-to-end test with schema creation, data insertion, querying, and EXPLAIN plan verification
• ReviewCommentQueriesTest: Tests specific query patterns mentioned in review
• CompoundConditionIndexTest: Demonstrates alternative syntax for compound conditions with index usage
• EmbeddedListIndexDebugTest: Direct index lookup verification

Related issues

• Fixes Support indexing of properties contained in embedded documents (or maps) inside lists #2802

Additional Notes

Query Optimizer Integration: Direct nested property queries use indexes with O(log n) performance:

• SELECT FROM Photo WHERE tags.id CONTAINS 103 - ✅ Uses index Photo[tags.idbyitem]

Compound Conditions: For queries with multiple conditions, use the alternative syntax to achieve index usage:

• ✅ Recommended (uses index): SELECT FROM Photo WHERE tags.id CONTAINS 100 AND tags.name CONTAINS 'Apple Inc'
• ⚠️ Works but no index: SELECT FROM Photo WHERE tags CONTAINS (id=100 and name='Apple Inc') - performs table scan

The recommended syntax uses the index on tags.id for initial filtering, then applies additional conditions as filters.

Performance: Direct nested property queries achieve O(log n) index lookups instead of O(n) table scans. EXPLAIN plans show FETCH FROM INDEX for supported query patterns.

Backward Compatibility: All 98 existing index tests pass. Properties with dots in names (e.g., other.special:property) continue working correctly.

Checklist

• I have run the build using mvn clean package command
• My unit tests cover both failure and success scenarios

• Fixes Support indexing of properties contained in embedded documents (or maps) inside lists #2802

💡 You can make Copilot smarter by setting up custom instructions, customizing its development environment and configuring Model Context Protocol (MCP) servers. Learn more Copilot coding agent tips in the docs.