Interesting library, thanks!
> Any query is going to require deserializing every row in the table (JSON and Protobuf alike) which is basically a non-starter for any project with performance requirements.
Apparently you can create indexes on the json functions, so I assume on your proto ones too.
> I believe that Protobuf messages are decoded as a whole, rather than just the desired field, which is going to be slower.
With the official library, yes, but it's an implementation/API choice. The wire format [1] can be skimmed fairly efficiently. Most significantly, message/byte/string fields all are written as tag number, length, data. So if there are submessages (entire trees) you don't care about, you can just skip over them. I've seen custom proto decoding things do this.
I think the most efficient approach would be to parse the path once per SQL query into a tag number-based path. And then have the per-row function just follow those with custom decoding logic. Unfortunately from my quick skim, it looks like SQLite's extension API doesn't really support this. You'd want it to build some context object for a given path/proto, then call extract with it a bunch of times, then tear it down. Still, I suppose you could do a LRU cache of these or something.
btw, I see your code is compiling a regex [edit: originally wrote proto by mistake] in the per-row path. [2] I haven't profiled, but I'd bet that's slowing you down a fair bit. You could just make it a 'static const std::regex* kPathElementRegexp = new std::regex("...")' to avoid this. (static initialization is thread-safe in C++. The heap allocation is because it's good practice to ensure non-POD globals are never destructed. Alternatively, there's absl::NoDestructor for this.)
[1] https://developers.google.com/protocol-buffers/docs/encoding
[2] https://github.com/rgov/sqlite_protobuf/blob/0a148ac6a5a2c02...