JSON functions, scalar types, and JSONB

CREATE TABLE event(id INTEGER PRIMARY KEY,payload TEXT NOT NULL CHECK(json_valid(payload))) STRICT;
INSERT INTO event VALUES(1,'{"count":2,"active":true,"optional":null}');
SELECT json_extract(payload,'$.count') AS count,
 typeof(json_extract(payload,'$.count')) AS storage,
 json_extract(payload,'$.active') AS active,
 json_type(payload,'$.optional') AS present_null,
 json_type(payload,'$.absent') AS missing FROM event ORDER BY id;

count|storage|active|present_null|missing
2|integer|1|null|NULL

count is SQL integer2 and true becomes1. json_type returns the text null for the present path and SQL NULL for the missing one.

CREATE TABLE document(id INTEGER PRIMARY KEY,payload BLOB NOT NULL CHECK(json_valid(payload,8))) STRICT;
INSERT INTO document VALUES(1,jsonb('{"count":2,"tags":["x","y"]}'));
SELECT typeof(payload) AS storage,json_extract(payload,'$.count') AS count FROM document ORDER BY id;
SELECT d.id,j.key,j.value FROM document AS d, json_each(d.payload,'$.tags') AS j ORDER BY d.id,j.key;

storage|count
blob|2

id|key|value
1|0|x
1|1|y

Storage is BLOB while scalar extraction remains integer2. Array positions0 and1 yield x and y in explicit order.