Query Json Strings

How to store and query strings in json format in SQLite.

Setup

    CREATE TABLE testable (
        testid INTEGER PRIMARY KEY, 
        testcol TEXT 
    )

Insert json string

    INSERT INTO testable(testcol) 
    values('{"testfield": "Lorem Ipsum"}') 
    ; 

Selection

    SELECT json_extract(testcol, '$.testfield') as testfield
    FROM testable 

Teardown

    DROP TABLE IF EXISTS testable 

QED

© Adam Heinz 11 March 2025