JSON и виртуальные столбцы Допустим, вы решили вести журнал с | SQLite на практике

JSON и виртуальные столбцы

Допустим, вы решили вести журнал событий, которые происходят в системе. События бывают разных типов, у каждого свой набор полей. Например, вход в систему:

{
"timestamp": 1652614531,
"object": "user",
"object_id": 11,
"action": "login",
"details": {
"ip": "192.168.0.1"
}
}

Или пополнение счета:

{
"timestamp": 1652614584,
"object": "account",
"object_id": 12,
"action": "deposit",
"details": {
"amount": "1000",
"currency": "USD"
}
}

Вы решаете не заниматься нормализацией по таблицам, а хранить прямо в JSON. Заводите таблицу events с единственным полем value:

select value from events;

{"timestamp":1652614531,...
{"timestamp":1652614584,...
{"timestamp":1652614644,...

И выбираете события по конкретному объекту:

select
json_extract(value, '$.object'),
json_extract(value, '$.action')
from events
where json_extract(value, '$.object_id') = 11;

┌────────┬────────┐
│ object │ action │
├────────┼────────┤
│ user │ login │
└────────┴────────┘

Все здорово, но json_extract() при вызове каждый раз парсит текст, так что на сотне тысяч записей запрос будет работать медленно. Что делать?

Создать виртуальные столбцы:

alter table events
add column object_id integer
as (json_extract(value, '$.object_id'));

Построить индекс:

create index events_object_id on events(object_id);

Теперь запрос работает моментально:

select object, action
from events
where object_id = 11;

Благодаря виртуальным столбцам получилась практически NoSQL база данных ツ

песочница