v1.14.5

[cwt]

NeoSQLite v1.14.5 Release Notes

TL;DR

NeoSQLite v1.14.5 is a feature enhancement release that adds native FTS5 BM25 text relevance scoring to aggregation pipelines and enables $push/$addToSet with nested object expressions in $group stages.

Key Features: This release implements $meta: "textScore" for native text search relevance scoring using SQLite's BM25 algorithm, and adds full support for $push and $addToSet with expression objects to collect nested structures during grouping operations.

• Upgrade if you use: Text search with relevance scoring, or $group stages that collect nested objects.
• Result Correctness: Achieved 100% compatibility in API comparison tests with comprehensive test coverage for all new features.

---

Overview

This release focuses on aggregation pipeline capabilities and native text scoring:

• $meta: textScore Support — Native FTS5 BM25 relevance scoring available in $project and $addFields stages.
• $push with Expressions — $push and $addToSet now support expression objects to collect nested documents during $group.
• Kill Switch Support — Both features respect the force fallback mechanism for debugging and testing.
• Comprehensive Test Coverage — Added 8 unit tests covering all new scenarios.

---

High Severity Enhancements

Added $meta: textScore for Native FTS5 Relevance Scoring

Feature: Native FTS5 BM25 text relevance scoring is now available in aggregation pipelines via {$meta: "textScore"}.

How It Works: During $text search stages, NeoSQLite captures the FTS5 BM25 score and stores it in the document's JSON data. When $meta: "textScore" is used in $project or $addFields, the score is extracted and made available for sorting and filtering.

SQL Implementation:

1. Score Capture — During $text match, -bm25(fts_table) is computed and stored as _textScore in the document JSON
2. Score Extraction — $meta: "textScore" in $project/$addFields extracts the stored score via json_extract(data, '$._textScore')
3. Python Fallback — When force fallback is enabled, the Python evaluator returns the stored score (or 0.0 if no FTS was used)

Impact:

• Before: No native text scoring in aggregation — required Python post-processing
• After: Native BM25 scoring available directly in aggregation pipelines

Example:

# Create FTS index
db.posts.create_index('title', fts=True)

# Use native text scoring in aggregation
pipeline = [
    {'$match': {'$text': {'$search': 'Python'}}},
    {'$project': {
        'title': 1,
        'score': {'$meta': 'textScore'}
    }},
    {'$sort': {'score': -1}}
]

results = list(db.posts.aggregate(pipeline))
# Results sorted by relevance score!
for post in results:
    print(f"Title: {post['title']}, Score: {post['score']}")

With $addFields:

pipeline = [
    {'$match': {'$text': {'$search': 'Learning'}}},
    {'$addFields': {'relevance': {'$meta': 'textScore'}}},
]

results = list(db.articles.aggregate(pipeline))
# All documents include relevance score

Kill Switch Support:

from neosqlite.collection.query_helper import set_force_fallback

# Force Python fallback for debugging
set_force_fallback(True)
# $meta: textScore returns 0.0 (no FTS5 in Python path)
set_force_fallback(False)  # Reset to native SQL path

---

Medium Severity Enhancements

Added $push and $addToSet with Expression Objects

Feature: $push and $addToSet now support expression objects to collect nested documents during $group stages.

Problem: Previously, $push with expressions like {'$push': {'title': '$title', 'author': '$author'}} would raise NotImplementedError and fall back to Python evaluation.

Solution: Implemented native SQL support that builds json_object() expressions for nested document collection:

1. Expression Detection — Detects dict expressions in $push/$addToSet
2. JSON Object Construction — Builds json_object() with field references and literal values
3. Array Aggregation — Uses json_group_array() to collect objects into arrays

SQL Logic:

-- For {$push: {title: '$title', author: '$author'}}
json_group_array(json_object(
    'title', json_extract(data, '$.title'),
    'author', json_extract(data, '$.author')
)) AS items

Impact:

• Before: $push with expressions → Python fallback
• After: $push with expressions → Native SQL collection

Example:

# Collect nested objects during grouping
pipeline = [
    {'$group': {
        '_id': '$category',
        'items': {
            '$push': {
                'title': '$title',
                'author': '$author'
            }
        }
    }},
    {'$sort': {'_id': 1}}
]

results = list(db.posts.aggregate(pipeline))
# Returns: {'_id': 'tech', 'items': [
#   {'title': 'Post 1', 'author': 'Alice'},
#   {'title': 'Post 2', 'author': 'Bob'}
# ]}

With Literal Values:

pipeline = [
    {'$group': {
        '_id': '$category',
        'items': {
            '$push': {
                'name': '$name',
                'type': 'product',  # Literal string
                'quantity': 10       # Literal number
            }
        }
    }}
]

$addToSet with Deduplication:

pipeline = [
    {'$group': {
        '_id': '$category',
        'unique_items': {
            '$addToSet': {
                'title': '$title',
                'author': '$author'
            }
        }
    }}
]
# Automatically deduplicates identical objects

---

Low Severity Improvements

Python Fallback Support for $meta

Python Evaluator Enhancement — Added $meta operator support to the Python expression evaluator for consistency with native SQL path. Returns stored _textScore value or 0.0 as default.

Comprehensive Test Coverage

New Tests Added — 8 comprehensive unit tests covering all new features:

• 4 tests for $meta: textScore in various scenarios (basic, $addFields, no FTS index, multiple indexes)
• 4 tests for $push/$addToSet with expressions (basic, literals, $addToSet deduplication, complex pipelines)

---

Test Statistics

Unit Tests

Metric	v1.14.5	v1.14.4
Total Tests	2,772	2,764
Passed	2,772	2,764
XFailed	0	0
Failed	0	0
Code Coverage	81.6%+	81.5%+

API Comparison Tests

NeoSQLite vs MongoDB

Metric	v1.14.5	v1.14.4
Total Tests	386	386
Passed	368	368
Skipped	18	18
Failed	0	0
Compatibility	100.0%	100.0%

---

Installation

pip install neosqlite
# or
poetry install

---

Migration Guide

Upgrading from v1.14.4

This is a non-breaking feature release. All existing code continues to work without modification.

For Users Who Need Text Relevance Scoring

If you were using Python-based text relevance scoring:

# Before v1.14.5: Manual Python scoring
pipeline = [
    {'$match': {'$text': {'$search': 'Python'}}},
]

results = list(db.posts.aggregate(pipeline))
# Python post-processing required
scored_results = []
for post in results:
    score = calculate_relevance(post, 'Python')
    scored_results.append((post, score))
scored_results.sort(key=lambda x: x[1], reverse=True)

# After v1.14.5: Native SQL scoring
pipeline = [
    {'$match': {'$text': {'$search': 'Python'}}},
    {'$project': {
        'title': 1,
        'score': {'$meta': 'textScore'}
    }},
    {'$sort': {'score': -1}}
]

results = list(db.posts.aggregate(pipeline))
# Already scored and sorted by relevance!

For Users Who Need Nested Object Collection

If you were using Python post-processing to build nested structures:

# Before v1.14.5: Manual Python grouping
pipeline = [
    {'$match': {'status': 'published'}},
]

results = list(db.posts.aggregate(pipeline))
# Python post-processing to group by category
from collections import defaultdict
grouped = defaultdict(list)
for post in results:
    grouped[post['category']].append({
        'title': post['title'],
        'author': post['author']
    })

# After v1.14.5: Native SQL grouping
pipeline = [
    {'$match': {'status': 'published'}},
    {'$group': {
        '_id': '$category',
        'items': {
            '$push': {
                'title': '$title',
                'author': '$author'
            }
        }
    }}
]

results = list(db.posts.aggregate(pipeline))
# Already grouped with nested objects!

---

Credits

Feature enhancements developed as part of the NeoSQLite project, based on community requests for advanced aggregation pipeline capabilities.

Special thanks to the optimization teams who identified these feature gaps during database performance tuning and aggregation pipeline work.

---

NeoSQLite v1.14.5 — Native FTS5 BM25 Scoring and Nested Object Collection in Aggregation Pipelines.

---

This discussion was created from the release v1.14.5.