Sqlite ima ekstenziju za rad sa json podacima.
Preduslovi:
Da bi se koristio iterator json_each potrebno je napraviti tabelu:
CREATE TABLE json_tree(
key ANY, -- key for current element relative to its parent
value ANY, -- value for the current element
type TEXT, -- 'object','array','string','integer', etc.
atom ANY, -- value for primitive types, null for array & object
id INTEGER -- integer ID for this element
parent INTEGER, -- integer ID for the parent of this element
fullkey TEXT, -- full path describing the current element
path TEXT, -- path to the container of the current row
json JSON HIDDEN, -- 1st input parameter: the raw JSON
root TEXT HIDDEN -- 2nd input parameter: the PATH at which to start
);
Za .NET podršku (>ver 1.0.99.0) posle uspostavljene konekcije:
connection.EnableExtensions(True);
connection.LoadExtension("full_path\SQLite.Interop.dll","sqlite3_json_init");
Napomena samome sebi za lazarus/typhon sa ZeosLib na Debianu ne treba load ekstenzije ali je dobro postaviti (može i direktno u samom IDE):
TZConnection.Properties['Undefined_Varchar_AsString_Length'] := 255; // ili više do 8K prema očekivani veličini
Kako bi se izbegao rad sa memo poljima.
Primer:
test tabela:
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
property_name TEXT NOT NULL UNIQUE,
property_value TEXT NOT NULL
);
json podatak obavezno okružiti sa json(jsonString) kao u primeru:
insert into test (property_name, property_value) values ('test',json('{
"values": [{
"name": "nv01",
"label": "d",
"unit": "cm",
"value": "1",
"title": "Thickness"
}, {
"name": "nv02",
"label": "ρ",
"unit": "kg/m3",
"value": "1",
"title": "Density"
}, {
"name": "nv03",
"label": "c",
"unit": "J/kgK",
"value": "1",
"title": " Thermal capacity"
}, {
"name": "nv04",
"label": "λ",
"unit": "W/mK",
"value": "1",
"title": "Thermal conductivity"
}, {
"name": "nv05",
"label": "μ",
"unit": "-",
"value": "1",
"title": "Relative vapor diffusion coefficient"
}]
}'));
sql:
select
t.id,
key,
json_extract(value,'$.name') as name,
json_extract(value,'$.label') as label,
json_extract(value,'$.value') as value,
json_extract(value,'$.unit') as unit,
json_extract(value,'$.title') as title
from
test t,
json_each(json_extract(t.property_value,'$.values'))
where
t.property_name='test';
rezultat:
| 1 |
0 |
nv01 |
d |
1 |
cm |
Thickness |
| 1 |
1 |
nv02 |
ρ |
1 |
kg/m3 |
Density |
| 1 |
2 |
nv03 |
c |
1 |
J/kgK |
Thermal capacity |
| 1 |
3 |
nv04 |
λ |
1 |
W/mK |
Thermal conductivity |
| 1 |
4 |
nv05 |
μ |
1 |
- |
Relative vapor diffusion coefficient |
