Read JSON

VTTP works with JSON objects. If the endpoint returns an object, you get one row. If it returns an array of objects, you get a row for each object. Simple enough.

An empty array gives you zero rows when you've declared the columns yourself. A scalar response, or an array that mixes objects with scalars, is an error.

Easy Column Maps

By default, a column reads the JSON key with the same name. For nested fields, add a PATH after the type:

CREATE VIRTUAL TABLE users USING vttp (
    URL ('https://jsonplaceholder.typicode.com/users'),
    id INTEGER,
    name TEXT,
    city TEXT PATH '$.address.city',
    latitude TEXT PATH '$.address.geo.lat',
    company TEXT PATH '$.company',
    first_role TEXT PATH '$.roles[0]',
    dotted_key TEXT PATH '$."a.b"',
    raw TEXT PATH '$'
);

Here, city reads address.city, and raw gives you the whole row as JSON text. The $ starts at the current row object, so you don't need to account for the outer response array.

You can chain .member, ."quoted member", and [index] steps. Array indexes start at zero. Quote keys containing dots or spaces, as in $."a.b"; quoted keys use JSON string escaping.

Paths don't support wildcards, recursive descent, filters, or negative indexes. An invalid path fails when you create the table and names the column to fix. A valid path that can't find its value just returns NULL. Objects and arrays come back as compact JSON text.

Types and Booleans

For the most part, just worry about the SQL types you want. VTTP handles these conversions for you:

JSON value	SQLite value
String	TEXT
Object or array	JSON text
Boolean	1 / 0, or true / false for a TEXT column
Integer	INTEGER, REAL for a REAL column, or JSON text for a TEXT column
Fractional number	REAL, or JSON text for a TEXT column
Null or missing field	NULL

For example, you can read the same boolean both ways:

CREATE VIRTUAL TABLE todos USING vttp (
    URL ('https://jsonplaceholder.typicode.com/todos'),
    id INT,
    completed INT,
    completed_text TEXT PATH '$.completed'
);

SELECT id, completed, completed_text FROM todos LIMIT 5;

completed gives you C style bools: true becomes 1, and false becomes 0. completed_text gives you the strings true and false. A REAL column gives you 1.0 and 0.0 instead.

Supported type names (case-insensitive) are INTEGER, INT, BIGINT, SMALLINT, TINYINT, INT2, INT8, BOOL, BOOLEAN, REAL, FLOAT, DOUBLE, TEXT, VARCHAR, CHAR, CLOB, NUMERIC, DECIMAL, and BLOB. Type size arguments, column constraints, and generated expressions are not supported.

Signed 64-bit integers keep their precision. Unsigned integers above SQLite's signed 64-bit range come back as decimal text so we don't lose digits. Objects and arrays stay as JSON text regardless of the declared type.

Declaring a type doesn't cast everything into it. JSON strings stay as text, even in a numeric column. Use CAST in your query if you need to convert numeric strings.

Inference

Leave out all the columns and VTTP will make an initial request to work them out. It collects the top-level keys from all returned rows, in the order it first sees them. Nested objects stay nested, and it doesn't fetch additional pages.

Observed JSON values	Inferred column type
Integers or booleans	INTEGER
Fractional numbers, or integers mixed with fractions	REAL
Strings, objects, arrays	TEXT
Incompatible mixed types, or only null values	TEXT

Missing and null values don't override a known type. Large unsigned integers use TEXT. If your API returns both id and ID, you'll need to declare distinct column aliases with PATH yourself; SQLite treats those names as the same identifier. Empty names and names containing NUL also fail inference. Other names are quoted for you, including SQL keywords and names containing quotes or dots.

VTTP saves the columns in a shadow table named <table>_vttp_columns in the same database. Leave that one to VTTP; dropping the virtual table cleans it up too. Reopening the database restores the saved schema without another discovery request.

Later responses can omit fields (you'll get NULL) or add fields (they'll be ignored). Drop and recreate the table to infer the schema again. You'll also need to do this to rename an inferred table; ALTER TABLE rename is rejected so it doesn't leave the saved schema behind.

If the API changes a value's type, its SQLite storage class can still change. Declare the columns yourself when you want to control which fields and types VTTP exposes.

Nested row collections

Sometimes the rows you want are inside an object. A Patient Bundle, for example, has an entry array containing the resources. Keep that array as JSON text, then let SQLite's json_each pull out the entries:

CREATE VIRTUAL TABLE bundles USING vttp (
    URL ('https://r4.smarthealthit.org/Patient'),
    entries TEXT PATH '$.entry'
);

SELECT entry.value ->> '$.resource.id' AS id,
       entry.value -> '$.resource.name' AS name
FROM bundles, json_each(bundles.entries) AS entry;