JSON and virtual columns in SQLite
source link: https://antonz.org/json-virtual-columns/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
JSON and virtual columns in SQLite
Generated columns have another great use case.
Let’s say you decide to keep a log of events that occur in the system. There are different types of events, each with its own set of fields. For example, sign-in:
{
"timestamp": "2022-05-15T09:31:00Z",
"object": "user",
"object_id": 11,
"action": "login",
"details": {
"ip": "192.168.0.1"
}
}
Or account deposit:
{
"timestamp": "2022-05-15T09:32:00Z",
"object": "account",
"object_id": 12,
"action": "deposit",
"details": {
"amount": "1000",
"currency": "USD"
}
}
You decide to store the raw JSON, as normalization is non-trivial. You create an events
table with a single value
field:
select value from events;
{"timestamp":"2022-05-15T09:31:00Z","object":"user","object_id":11,"action":"login","details":{"ip":"192.168.0.1"}}
{"timestamp":"2022-05-15T09:32:00Z","object":"account","object_id":12,"action":"deposit","details":{"amount":"1000","currency":"USD"}}
{"timestamp":"2022-05-15T09:33:00Z","object":"company","object_id":13,"action":"edit","details":{"fields":["address","phone"]}}
And select events for a specific object:
select
json_extract(value, '$.object') as object,
json_extract(value, '$.action') as action
from events
where json_extract(value, '$.object_id') = 11;
┌────────┬────────┐
│ object │ action │
├────────┼────────┤
│ user │ login │
└────────┴────────┘
So far, so good. But json_extract()
parses the text on each call, so for hundreds of thousands of records the query is slow. What should you do?
Define virtual columns:
alter table events
add column object_id integer
as (json_extract(value, '$.object_id'));
alter table events
add column object text
as (json_extract(value, '$.object'));
alter table events
add column action text
as (json_extract(value, '$.action'));
Build an index:
create index events_object_id on events(object_id);
Now the query works instantly:
select object, action
from events
where object_id = 11;
Thanks to virtual columns, we almost have a NoSQL database ツ
Follow @ohmypy on Twitter and subscribe by email to keep up with new posts 🚀
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK