OrmLite now queries JSON properties like any other property
ServiceStack v10.4 lets typed OrmLite queries reach into complex properties such as x.Address.City or x.Lines.Any(...). The expressions are translated to the native JSON functions of SQLite, PostgreSQL, SQL Server and MySQL, so one query works across all four. This removes the need for Sql.Json() wrappers on complex properties.
Key points
- Complex and list properties stored as JSON can be queried with paths like x.Address.City, without Sql.Json() wrappers.
- Any(), All() and Count(predicate) check each list item against all conditions at once, avoiding mismatches from x.Lines[0] checks.
- Supported databases are SQLite, PostgreSQL, SQL Server and MySQL/MariaDB, each using its native JSON functions.
- On dialects that do not store JSON by default, enable UseJson, and convert existing JSV rows before querying them.
- For frequently queried paths, add a dedicated indexed column with [Index], or an expression index on SQLite and PostgreSQL.
OrmLite v10.4 lets you filter, sort, count and update by properties nested inside complex or list values that are stored as JSON. Expressions such as x.Address.Country.Code, x.Tags.Contains("vip") and x.Lines.Count > 1 now run directly against the model, and collection checks like Any, All and Count evaluate each list item against all of its conditions together. The same code runs on SQLite, PostgreSQL, SQL Server and MySQL, with values sent as parameters.
The feature depends on the property being saved as JSON, which is the default for dialects configured through AddOrmLite(). Other dialects need UseJson enabled, and existing rows saved in the older JSV format must be converted before they can be queried. Attempting such a query on an unsupported setup raises a NotSupportedException that explains the fix. The article also covers the explicit JSON path API and indexing strategies.
Real-world use
- A multi-tenant SaaS API that stores customer addresses and tags as JSON and filters by city or VIP tag.
- An e-commerce service that keeps order lines as JSON and needs to match a SKU and quantity on the same line.
- An integration service that stores event bodies in text columns and reads them through typed Data Models.
Why it matters
You can write portable, compile-checked queries over JSON data without database-specific syntax, and model refactors are caught by the compiler. For large tables, index frequently queried JSON fields to keep the queries fast.