An extension for SQLite that adds functions for querying JSON data using JQ syntax.
SQLite does have JSON functions and operators already but I have occasionally done a bit of data mangling in jq prior to loading it into SQLite, and sometimes I've wanted to be able to do all of that in one place. jq's language is a bit more flexible than the built-in JSON functions in SQLite for some purposes, particularly when iterating over large deeply-nested objects.
This will return the result of the specified JQ expression run against the supplied JSON.
select jq('{"a": "xyz"}', '.a');
-- returns "xyz"If there is a single scalar result, it will be returned as the corresponding type. Integers are returned as integers, floats as floats, booleans as ints with value 0 or 1, etc.
If the result is a JSON array or object, those will be returned encoded as JSON.
If there are multiple results they are returned as a JSON array.
This function returns a result set as a virtual table. Each row of the result will be encoded in the same way as above.
select * from jq_each('{"hello": "world"}', '.hello');
-- returns "world"
-- see test_table.sql for the input
select * from test, jq_each(test.raw, '.[].repo.name');
-- returns two rows, both 'mgdm/htmlq'On macOS, run make, then you can load the resulting extension into sqlite3 using .load sqlite_jq.so.
On Linux, run make to build, though you will then have to place the extension somewhere on LD_LIBRARY_PATH. Alternatively, for testing, you can set this directly:
export LD_LIBRARY_PATH=$PWD:LD_LIBRARY_PATHI would not advise doing this permanently. Then, you can load the resulting extension with .load sqlite_jq.
## Things to be aware of
This is, at present, an interesting hack with no tests. I intend to fix this. Notably, I haven't tested the table-valued function with constraints much.
This uses the gojq implementation of jq by itchyny, which has some differences from the canonical implementation but is easy to integrate with.
The sqlite bindings in use are by Riyaz Ali.