This script demonstrates querying json data in sqlite.

sqlite_json_demo.py

#!/usr/bin/env python3
# by: Cody Kochmann
# created: 2021-05-10
# license: MIT


import sqlite3, json, random, string, sys, functools


''' This script demonstrates basic querying of json
    data in sqlite.
'''


# cleaner printing to stderr
eprint = functools.partial(print, file=sys.stderr)

# open a sqlite db in memory
db = sqlite3.connect(':memory:')
cur = db.cursor()

# convenience function for testing sql like this
def run(*sql):
    eprint('running:', *sql)
    for i, row in enumerate(cur.execute(*sql)):
        if i == 0:
            eprint('result:')
        eprint(i, end='\t')
        print(*row)

# random test data generator
random_json = lambda: {
    random.choice(string.ascii_lowercase): random.randint(32, 64)
    for i in range(
        1,
        random.randint(2,8)
    )
}

# initialize table
eprint('setting up the table...')
run(
    '''
    CREATE TABLE records(
        id integer primary key autoincrement,
        data json not null
    )
    '''
)

# fill the db
eprint('injecting objects...')
test_objects = [random_json() for i in range(64)]
for o in test_objects:
    run(
        '''
        insert into records(data) values (?)
        ''',
        [json.dumps(o)]
    )

# run some selects against the db to see it work
eprint('running queries...')
# standard dump to see the full table
run(
    '''
        select data from records
    '''
)
# select using json_extract to see field select
run(
    '''
        select json_extract(data, '$.c') from records
    '''
)
# select using json_extract but with filter
run(
    '''
        select json_extract(data, '$.c') from records where json_extract(data, '$.c') is not null
    '''
)
# select filtered json_extract but comparing values
run(
    '''
        select json_extract(data, '$.c') from records where json_extract(data, '$.c') > 50
    '''
)


# close the db
cur.close()
db.close()