Awesome
liteJQ: jq support in SQLite</strong>
<a href="https://github.com/Florents-Tselai/litejq/actions/workflows/test.yml?branch=mainline"><img src="https://github.com/Florents-Tselai/litejq/actions/workflows/test.yml/badge.svg"></a> <a href="https://github.com/Florents-Tselai/liteJQ/stargazers"><img alt="GitHub Repo stars" src="https://img.shields.io/github/stars/Florents-Tselai/liteJQ"></a> <a href="https://github.com/Florents-Tselai/litejq/releases"><img alt="Release" src="https://img.shields.io/github/v/release/Florents-Tselai/liteJQ"></a> <a href="https://www.linkedin.com/in/florentstselai/"><img src="https://img.shields.io/badge/LinkedIn-0077B5?logo=linkedin&logoColor=white"></a> <a href="https://github.com/sponsors/Florents-Tselai/"><img src="https://img.shields.io/static/v1?label=Sponsor&message=%E2%9D%A4&logo=GitHub&link=https://github.com/sponsors/Florents-Tselai/"></a> <a href="https://opensource.org/licenses/MIT License"><img src="https://img.shields.io/badge/MIT License-blue.svg"></a>
liteJQ is an SQLite extension, written in C, that brings jq
support to SQLite.
It uses vanilla libjq
.
Note: If you like the idea, but you're more into Postgres, check out: pgJQ: jq extension for Postgres
Motivation
SQLite has been supporting JSON for years. Complex queries, however, involving JSON can be more cumbersome to write and understand, especially when compared to more complex systems like PostgreSQL. liteJQ attempts to alleviate that by bringing the expressive power of jq into SQLite.
Installation
make
This produces a litejq
binary object, which should be loaded in SQLite at runtime.
Verify the installation.
sqlite3 :memory: <<EOF
.load ./litejq
select jq('{"key": "litejq is loaded"}', '.key')
EOF
Usage
SELECT jq(json, jqprog)
Examples
We'll use the movie dataset as a running example.
sqlite3 movies.db < ./data/movies.sql
We have a very simple schema:
CREATE TABLE [movies] (
"d" TEXT -- json
);
Column d
holds json values in plain text like this.
{
"title": "The Corn Is Green",
"year": 1945,
"cast": [
"Bette Davis",
"Joan Lorring",
"John Dall"
],
"genres": [
"Drama"
],
"href": "The_Corn_Is_Green_(1945_film)",
"extract": "The Corn Is Green is a 1945 American drama film starring Bette Davis as a schoolteacher determined to bring education to a Welsh coal mining town despite great opposition. It was adapted from the 1938 play of the same name by Emlyn Williams, which originally starred Ethel Barrymore.",
"thumbnail": "https://upload.wikimedia.org/wikipedia/en/thumb/b/bf/The-corn-is-green-poster.jpg/320px-The-corn-is-green-poster.jpg",
"thumbnail_width": 320,
"thumbnail_height": 248
}
In any session, you should load the extension first after building it, like this:
.load ./litejq
Then you can start doing jq
magic.
Let's see some example queries.
List all movie titles
select jq(d, '.title')
from movies;
To find movies released after a specific year, for example, 1980
select jq(d, '{title: .title, year: .year}')
from movies
where jq(d, '.year > 1980');
The above query is equivalent to this one
select jq(d, '{title: .title, year: .year}')
from movies
where jq(d, '.year') > 1980;
Extract Movies with Specific Keywords in Extract
select jq(d, '.extract')
from movies
where jq(d, '.extract | contains("silent")');
Filter movies by a specific genre (e.g., Drama)
select jq(d, '{title: .title, year: .year, genres: .genres}')
from movies
where jq(d, '.genres[] == "Drama"');
Filter movies where "Joan Lorring" and "John Dall" played together
select jq(d, '{title: .title, year: .year, cast: .cast}')
from movies
where jq(d, '.cast | contains(["Joan Lorring", "John Dall"])');
Group by movies by release year
select jq(d, '.year'), count(*)
from movies
group by jq(d, '.year')
Notes On Installation
For this to work, you'll need development files for both SQLite and jq.
MacOS
brew install jq sqlite3
make all
I've found that brew
installs header files auomatically for you,
so there's nothing else you have to do
Verify installation
sqlite3 :memory: <<EOF
.load ./litejq
select jq('{"key": "value"}', '.key')
EOF
Linux
sudo apt install sqlite3 libsqlite3-dev jq libjq-dev
On Linux, sometimes things can be trickier because
many distros don't have jq
configured with pkg-config
so your best guess would be installing jq
from source first.
If you already have jq
installed to a known prefix,
try using it explicitly:
JQ_PREFIX=/usr/local make
If this doesn't work, you can (and probably should) build it from source.
Build jq
from source
cd /tmp &&
wget "https://github.com/jqlang/jq/releases/download/jq-1.7.1/jq-1.7.1.tar.gz" &&
tar xzf jq-1.7.1.tar.gz &&
cd jq-1.7.1 &&
./configure --with-oniguruma=builtin --prefix=/usr/local &&
sudo make install
Then try again
make
sqlite3 :memory: <<EOF
.load ./litejq
select jq('{"key": "litejq is loaded"}', '.key')
EOF