Read JSON
The VTTP virtual table was designed to work with JSONs by default.
From JSON to SQLite, the type conversions are:
| JSON | SQLITE |
|---|---|
string | TEXT |
object | TEXT |
array | TEXT |
boolean | TEXT or INT |
number | REAL or INT |
null | NULL |
From object to Rows
Incoming data is the payload you receive from an HTTP GET request, which vttp
maps to a SELECT query against a Fetch virtual table.
SELECT "userId", id, title, completed FROM todos;
Here's what a todo from the typicode api looks like:
{
"userId": 1,
"id": 1,
"title": "delectus aut autem",
"completed": false
}
To read a todo JSON as a SQL row, we have to tell the vttp virtual table
factory what SQL types we need converted from the JSON types.
Easy Column Maps
The keys userId and id are both numbers, and are represented as integers, so
we can map them to INT:
"userId" INT,
id INT
And title is a string, which we can just represent as TEXT:
"userId" INT,
id INT,
title TEXT
The VTTP virtual table works best with numerical / text types because they have a logical 1-to-1 mapping between JSON and SQL.
Booleans
While the keys so far have been easy to map, we have to make a decision with the
completed field because SQLite doesn't support BOOLEAN column types.
Yarts lets you map JSON booleans to TEXT or INT.
If you choose to save completed columns as TEXT:
DROP TABLE IF EXISTS todos;
CREATE VIRTUAL TABLE todos USING vttp (
url TEXT DEFAULT 'https://jsonplaceholder.typicode.com/todos',
id INT,
"userId" INT,
title TEXT,
completed TEXT
);
SELECT * FROM todos LIMIT 5;
Then VTTP will map booleans to their text representation true and false:
┌────┬────────┬──────────────────────────────────────────────────────────────┬───────────┐
│ id │ userId │ title │ completed │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 1 │ 1 │ delectus aut autem │ false │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 2 │ 1 │ quis ut nam facilis et officia qui │ false │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 3 │ 1 │ fugiat veniam minus │ false │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 4 │ 1 │ et porro tempora │ true │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 5 │ 1 │ laboriosam mollitia et enim quasi adipisci quia provident il │ false │
│ │ │ lum │ │
└────┴────────┴──────────────────────────────────────────────────────────────┴───────────┘
Boolean maps to INTs follow the standard C style bools where...
DROP TABLE IF EXISTS todos;
CREATE VIRTUAL TABLE todos USING vttp (
url TEXT DEFAULT 'https://jsonplaceholder.typicode.com/todos',
id INT,
"userId" INT,
title TEXT,
completed INT
);
SELECT * FROM todos LIMIT 5;
... true turns into 1 and false turns into 0:
┌────┬────────┬──────────────────────────────────────────────────────────────┬───────────┐
│ id │ userId │ title │ completed │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 1 │ 1 │ delectus aut autem │ 0 │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 2 │ 1 │ quis ut nam facilis et officia qui │ 0 │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 3 │ 1 │ fugiat veniam minus │ 0 │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 4 │ 1 │ et porro tempora │ 1 │
├────┼────────┼──────────────────────────────────────────────────────────────┼───────────┤
│ 5 │ 1 │ laboriosam mollitia et enim quasi adipisci quia provident il │ 0 │
│ │ │ lum │ │
└────┴────────┴──────────────────────────────────────────────────────────────┴───────────┘
Objects and Arrays
The last JSON types VTTP has to handle are object and array types,
which it does by simply stringifying the values into TEXT columns.
For example, the address and company fields from the typicode API is a JSON object,
so we declare their types as TEXT:
CREATE VIRTUAL TABLE users USING vttp (
url TEXT DEFAULT 'https://jsonplaceholder.typicode.com/users',
id INT,
name TEXT,
address TEXT,
company TEXT
);
SELECT * FROM users LIMIT 2;
SQLite will just represent it as plaintext:
┌────┬───────────────┬──────────────────────────────┬────────────────────────────────────────────────────────────┐
│ id │ name │ address │ company │
├────┼───────────────┼──────────────────────────────┼────────────────────────────────────────────────────────────┤
│ 1 │ Leanne Graham │ { │ { │
│ │ │ "street": "Kulas Light", │ "name": "Romaguera-Crona", │
│ │ │ "suite": "Apt. 556", │ "catchPhrase": "Multi-layered client-server neural-net", │
│ │ │ "city": "Gwenborough", │ "bs": "harness real-time e-markets" │
│ │ │ "zipcode": "92998-3874", │ } │
│ │ │ "geo": { │ │
│ │ │ "lat": "-37.3159", │ │
│ │ │ "lng": "81.1496" │ │
│ │ │ } │ │
│ │ │ } │ │
├────┼───────────────┼──────────────────────────────┼────────────────────────────────────────────────────────────┤
│ 2 │ Ervin Howell │ { │ { │
│ │ │ "street": "Victor Plains", │ "name": "Deckow-Crist", │
│ │ │ "suite": "Suite 879", │ "catchPhrase": "Proactive didactic contingency", │
│ │ │ "city": "Wisokyburgh", │ "bs": "synergize scalable supply-chains" │
│ │ │ "zipcode": "90566-7771", │ } │
│ │ │ "geo": { │ │
│ │ │ "lat": "-43.9509", │ │
│ │ │ "lng": "-34.4618" │ │
│ │ │ } │ │
│ │ │ } │ │
└────┴───────────────┴──────────────────────────────┴────────────────────────────────────────────────────────────┘
If you want to extract nested object values, use a GENERATED ALWAYS AS clause
in conjunction with the -> JSON path traversal (Postgres style):
DROP TABLE IF EXISTS users;
CREATE VIRTUAL TABLE users USING vttp (
url TEXT DEFAULT 'https://jsonplaceholder.typicode.com/users',
id INT,
name TEXT,
address_street TEXT GENERATED ALWAYS AS (address->'street'),
address_zipcode TEXT GENERATED ALWAYS AS (address->'zipcode'),
address_geo_lat TEXT GENERATED ALWAYS AS (address->'geo'->'lat'),
address_geo_lng TEXT GENERATED ALWAYS AS (address->'geo'->'lng')
);
SELECT * FROM users LIMIT 2;
┌────┬───────────────┬────────────────┬─────────────────┬─────────────────┬─────────────────┐
│ id │ name │ address_street │ address_zipcode │ address_geo_lat │ address_geo_lng │
├────┼───────────────┼────────────────┼─────────────────┼─────────────────┼─────────────────┤
│ 1 │ Leanne Graham │ Kulas Light │ 92998-3874 │ -37.3159 │ 81.1496 │
│ 2 │ Ervin Howell │ Victor Plains │ 90566-7771 │ -43.9509 │ -34.4618 │
└────┴───────────────┴────────────────┴─────────────────┴─────────────────┴─────────────────┘
If you have deeply nested values that are only separated by object parents,
then the GENERATED ALWAYS AS extraction is the best way to read that directly.
Response Body
The Fetch virtual table assumes the response body from the server is encoded as a JSON array of objects. If it returns a single object, then that object will be treated as the only row of the table.
For example, the FHIR API specs always return a single Bundle object for
its resources. So attempting to create this patients table...
CREATE VIRTUAL TABLE patients USING vttp (
url TEXT DEFAULT 'https://r4.smarthealthit.org/Patient',
"resourceType" TEXT,
id TEXT
);
SELECT * FROM patients;
...will result in this result set:
┌──────────────┬──────────────────────────────────────┐
│ resourceType │ id │
├──────────────┼──────────────────────────────────────┤
│ Bundle │ 326c2224-482a-4aff-aa09-9fdabfcc601c │
└──────────────┴──────────────────────────────────────┘
We'd like to be able to view the Bundle.entry.resource array field as the rows.
We can do so by setting the body hidden column against patients to specify
the JSON path we want to follow from the body using JSONPath:
SELECT * FROM patients
WHERE body = '$.entry[*].resource';
TODO: PASTEME
So you have some flexibility when it comes to the exact shape of your API resources.