Feature: Index custom YAML arrays per-element for metadata filtering

[bm-clawd]

Feature Request: Index custom YAML arrays per-element for metadata filtering

Summary

Built-in arrays (tags, type) are indexed per-element and filterable via --tag and --type. Custom YAML arrays in frontmatter store correctly but aren't queryable via --meta or metadata_filters.

Extend the per-element indexing that tags already has to all array fields in frontmatter.

Use Case

User has 1000+ notes with Picoschema-defined structures. Many natural data models use arrays (participants, assignees, dependencies, status history, etc.). Currently these require workarounds (numbered fields, flattening) that lose semantic clarity.

Current Behavior

Example: Simple string array

---
title: Meeting Notes
participants:
  - Alice
  - Bob
  - Charlie
---

Expected: metadata_filters={"participants": "Bob"} returns this note.
Actual: Returns no results. The array is stored but not indexed per-element.

Example: Array of objects

---
title: Project Tracker
tasks:
  - name: Design review
    status: done
    priority: high
  - name: Write tests
    status: active
    priority: medium
---

Ideal (future): metadata_filters={"tasks.status": "active"} or element-level querying.
Understood: This is significantly more complex than simple contains.

Current Workaround

Numbered fields (task1_name: Design review, task1_status: done) and dot notation on nested objects. Works but loses natural array semantics and makes schemas verbose.

Proposed Solution

Phase 1: Simple arrays (parity with tags)

• Index string arrays per-element
• metadata_filters={"participants": "Bob"} matches if "Bob" is in the participants array
• Same behavior as --tag already provides

Phase 2 (optional): Array-of-objects

• Support dot-notation queries: {"tasks.status": "active"}
• More complex but enables rich structured data in frontmatter

Why This Matters

• Schemas (Picoschema) are making structured frontmatter more common
• Natural data models use arrays (meeting participants, task assignees, dependencies, contributors, etc.)
• Current workaround (numbered fields) is verbose and breaks schema expressiveness
• tags proves the indexing pattern already works — just needs extension to custom fields

Related

• Built-in array filtering already works: --tag <value>, --type <value> (repeatable)
• Custom arrays are stored correctly, just not indexed for filtering
• User is running production workload (1000+ notes) hitting this limitation

Priority

Medium-High — affects users building structured knowledge bases with schemas. Blocking more expressive data models in frontmatter.

[claude]

Claude finished @bm-clawd's task in 2m 30s —— View job

---

Issue Triage

• Analyze issue
• Explore codebase to understand current metadata indexing
• Check for duplicates
• Apply labels and provide triage summary

---

Triage Summary

Type: Enhancement / Feature Request
Priority: Medium-High
Component: Database / Search (metadata filtering)
Complexity: Medium
Labels applied: enhancement

---

Analysis

No duplicate issues found.

Current State:

• tags and type built-in fields are indexed per-element via computed columns (SQLite: tags_json, frontmatter_status, frontmatter_type) and GIN indexes (Postgres)
• Custom frontmatter arrays are stored correctly in entity.entity_metadata (JSON/JSONB) but the eq operator in metadata filters does exact-match, not array-contains
• The infrastructure for array filtering already exists — the contains operator uses json_each() (SQLite) and JSONB @> (Postgres)

What needs to change:

The fix is primarily in src/basic_memory/repository/sqlite_search_repository.py and src/basic_memory/repository/postgres_search_repository.py. When the eq operator is applied to a metadata field and the stored value is a JSON array, the query should check whether the filter value is an element of that array — rather than requiring an exact match.

Key files:

• src/basic_memory/repository/metadata_filters.py — filter parsing
• src/basic_memory/repository/sqlite_search_repository.py:710-742 — SQLite filter SQL (eq/in operators)
• src/basic_memory/repository/postgres_search_repository.py:708-755 — Postgres filter SQL
• src/basic_memory/services/search_service.py:165-172 — where metadata_filters are merged

Implementation approach for Phase 1:

For SQLite, the eq case needs to add a fallback: if the JSON path value is an array, use EXISTS (SELECT 1 FROM json_each(json_extract(e.entity_metadata, '$.field')) WHERE value = ?). For Postgres, use the JSONB ? operator or @> containment check.

Phase 2 (dot-notation for nested array objects like tasks.status) is significantly more complex and can be tracked separately.