What if the "views" your API queries didn't have to be tables? You could just keep "tables" as a backend implementation detail, and 'fake' a different model on top of them that's closer to what the client needs.
I understand postgres provides something like this, but it would be interesting as a library rather than a separate application. I'd love to be able to query hashmaps or arrays of data using SQL.
Maybe this already exists somewhere? My web-search-fu is failing me but I recall reading somewhere that facebook had something like this for querying systems, so maybe such a library exists extracted from that usecase?
Edit: Here's one. https://github.com/mozilla/moz-sql-parser
Edit: and that Facebook project is called OsQuery [1]. It was built on top of SQLite virtual tables and is listed in their implementations list.
Myself included too. As a side project, I'm currently writing a game (roguelike), where all the game state that would ordinarily be put into an ECS or an object tree is instead stored in in-memory SQLite instance; the game's state management consists of (currently hand-written) SQL queries.
The reason for that is because, in trying to implement yet another version of an ECS-like architecture, I realize I'm essentially reinventing database queries and database indices, so I might as well use the real thing.