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

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 база данных ツ

песочница