txtai looks cool! It's not obvious from the title of the post, but Endatabas isn't really comparable to SQLite in this way. Endb is a cloud-native columnar immutable database with separated storage and compute. Only the SQL grammar is SQLite-inspired.

That said, the significant dialect difference is that Endb's JSON-like documents are strongly-typed. One of the places the Endb SQL dialect diverges from SQLite's is typing. In this respect, SQLite is "more schemaless"... at least at the level of weakly-typed scalars and nested JSON (since JSON supports a diminutive set of types).

Endb's data types, their exposed JSON-LD types, and internals: https://docs.endatabas.com/reference/data_types.html

Endb's SQL data types: https://docs.endatabas.com/sql/data_types.html

...all of these can be used at any nesting level, which makes nested data in Endb quite different from JSON.

Hope that helps explain the difference!

(I'm involved in the Endatabas project and happy to answer other questions or clarify this.)

Sounds very interesting, appreciate the clarifications. Definitely will keep an eye on the project, best of luck!

The problem with JSON in SQLite is that indexing arrays is not supported.

https://sqlite.org/forum/forumpost/dfd4739c57