Primer sqlite i json

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