liteJQ: jq support in SQLite

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

Others

• I've also created pgJQ which brings jq to Postgres.
• There is another jq-sqlite similar, but it's written in Go and relies on a non-vanilla implementation of JQ.