Hacker News new | ask | show | jobs
by nbbaier 638 days ago
> I hope SQLite3 can implement SQL/JSON soon too. I have a library of compatability functions to generate the appropriate JSON operations depending on if it's SQLite3 or PostgreSQL. And it'd be nice to reduce the number of incompatibilities over time.

Is this available anywhere? Super interested
1 comments
veggieroll 638 days ago
No, it's not at the moment. Sorry!

The most useful part is doing set intersection operations on JSON array's. Probably the second is extracting a value by path across both.

It's not crazy to implement, SQLite was the harder side. Just a bit of fiddling with `json_each`, EXISTS, and aggregate functions.

link
nbbaier 638 days ago
Might give it a whirl. Is the library pure sql or is it written in something else?
link
veggieroll 638 days ago
I've been using Go with Goqu [1] for SQL for a lot of things lately. But, the language shouldn't matter much. The functions are just building the appropriate SQL clauses.

For some of the operations, the method I was using required marshaling the inputs to JSON before sending them over the wire. And that's nicer in a non SQL programming language. But both db's ultimately do have json_build_array/json_build_object for PostgreSQL or json_array/json_object for SQLite3.

[1] https://github.com/doug-martin/goqu

link