Very rough hacked together test script of the excellent article http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/

sql.py

#
# Oisin Mulvihill
# 2015-11-18
#
# Very rough hacked together test of the excellent article:
#  * http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/
#
import json
import urllib2
from pysqlite2.dbapi2 import connect

# Quick extension test. This would blow up if the extension wasn't loaded:
conn = connect(':memory:')
conn.execute('select json(?)', (1337,)).fetchone()

# Now follow some of the steps from the original article to see quering the JSON:
fh = urllib2.urlopen('http://media.charlesleifer.com/downloads/misc/blogs.json')
data = json.loads(fh.read())

from peewee import *
from playhouse.sqlite_ext import *


class JQLiteDatabase(SqliteExtDatabase):
    def _connect(self, database, **kwargs):
        conn = connect(':memory:')
        conn.isolation_level = None
        self._add_conn_hooks(conn)
        return conn

db = JQLiteDatabase(':memory:')


class Entry(Model):
    data = TextField()

    class Meta:
        database = db

Entry.create_table()

with db.atomic():
    for entry_json in data:
        Entry.create(data=json.dumps(entry_json))

from peewee import Entity
tags_src = fn.json_each(Entry.data, '$.metadata.tags').alias('tags')
tags_ref = Entity('tags')

title = fn.json_extract(Entry.data, '$.title')

query = (Entry
         .select(title.alias('title'))
         .from_(Entry, tags_src)
         .where(tags_ref.value == 'sqlite')
         .order_by(title))

import pprint

pprint.pprint([row for row, in query.tuples()])

# Profit!