SQL-отчёты в Платформе

Содержание

Почему json_extract — и что это такое
Структура базы данных
Макросы {field} — быстрый способ обратиться к полю
Прямые запросы через json_extract
JOIN — объединение таблиц
Работа с табличными частями (record_rows)
Аудит-лог и мета-таблицы
Пагинация и LIMIT
Зашифрованные поля в SQL
Ограничения безопасности
Полные примеры по типовым задачам

1. Почему json_extract

Как хранятся данные записей

Каждая запись в платформе — это строка в таблице records. Все поля документа или справочника хранятся не в отдельных колонках, а внутри одного JSON-поля data.

records
├── id          INTEGER        — идентификатор записи
├── entity_id   INTEGER        — к какой сущности относится
├── status      TEXT           — draft / active / posted / cancelled / deleted
├── data        TEXT (JSON)    — ВСЕ поля записи хранятся здесь
├── created_at  DATETIME
├── updated_at  DATETIME
├── posted_at   DATETIME
├── created_by  INTEGER        — id пользователя
├── is_folder   INTEGER        — 1 если это папка (иерархический справочник)
└── parent_id   INTEGER        — родительская папка

Пример того, что реально лежит в поле data:

{
  "fio": "Иванов Иван Иванович",
  "phone": "9001234567",
  "amount": 15000,
  "date_doc": "2024-03-15",
  "client_id": 42,
  "status_color": "confirmed"
}

Чтобы вытащить конкретное поле из этого JSON, SQLite использует функцию json_extract:

json_extract(r.data, '$.fio')      -- вернёт: "Иванов Иван Иванович"
json_extract(r.data, '$.amount')   -- вернёт: 15000
json_extract(r.data, '$.client_id')-- вернёт: 42

Синтаксис пути всегда: '$.' + имя поля (то, что задано в настройках сущности, вкладка «Поля», колонка «Имя»).

2. Структура базы данных

Все таблицы доступны в SQL-отчётах (только на чтение).

Основные таблицы

Таблица	Что содержит
`records`	Все записи всех сущностей
`record_rows`	Строки табличных частей (вложенные таблицы в документах)
`entities`	Список сущностей (справочники, документы и т.д.)
`fields`	Поля сущностей с их настройками
`audit_log`	Журнал всех изменений
`users`	Пользователи
`attachments`	Прикреплённые файлы

Таблица `records` — колонки

-- Служебные колонки, доступны напрямую:
r.id          -- числовой ID записи
r.entity_id   -- к какой сущности относится (FK → entities.id)
r.status      -- статус: draft / active / posted / cancelled / deleted
r.created_at  -- дата создания (UTC, формат: '2024-03-15 10:23:45')
r.updated_at  -- дата последнего изменения
r.posted_at   -- дата проведения (только для документов)
r.created_by  -- id пользователя, создавшего запись
r.is_folder   -- 1 = папка (иерархический справочник), 0 = обычная запись
r.parent_id   -- id родительской папки (NULL = корневой уровень)
-- Все пользовательские поля — внутри r.data (JSON):
json_extract(r.data, '$.имя_поля')

Как узнать имя поля

Имена полей смотреть в: Настройки → Сущность → Поля → колонка «Имя».

Либо через SQL:

SELECT name, label, field_type
FROM fields
WHERE entity_id = 5   -- id нужной сущности
ORDER BY sort_order

Либо узнать entity_id:

SELECT id, name, label, type
FROM entities
ORDER BY sort_order

3. Макросы {field}

Макрос — это сокращённая запись, которую платформа автоматически разворачивает перед выполнением запроса. Работает только для основного алиаса r (таблица records).

Синтаксис

{имя_поля}       → json_extract(r.data, '$.имя_поля')
{имя_поля:date}  → strftime('%d.%m.%Y', datetime(json_extract(r.data, '$.имя_поля'), '+3 hours'))

Пример

-- Написано в редакторе отчёта:
SELECT r.id, r.status, {fio}, {amount}, {date_doc:date}
FROM records r
WHERE r.entity_id = 5
-- После разворачивания макросов выполняется:
SELECT r.id, r.status,
       json_extract(r.data, '$.fio'),
       json_extract(r.data, '$.amount'),
       strftime('%d.%m.%Y', datetime(json_extract(r.data, '$.date_doc'), '+3 hours'))
FROM records r
WHERE r.entity_id = 5

Модификаторы макросов

Модификатор	Результат	Пример
(без модификатора)	Сырое значение из JSON	`{amount}` → `15000`
`:date`	Дата в формате ДД.ММ.ГГГГ с учётом часового пояса сервера	`{date_doc:date}` → `15.03.2024`

Важно: Модификатор :date работает для полей типа date и datetime. Для datetime покажет только дату без времени.

Когда макросы не работают

Макросы раскрываются только для r.data. Если вы делаете JOIN с другой таблицей под алиасом r2, doc, client и т.д. — нужно писать json_extract вручную:

-- Для r2 макрос {name} НЕ сработает — пишите вручную:
SELECT {fio}, json_extract(r2.data, '$.name') AS контрагент
FROM records r
LEFT JOIN records r2 ON r2.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)

4. Прямые запросы через json_extract

Базовый запрос

SELECT
    r.id,
    r.status,
    r.created_at,
    json_extract(r.data, '$.fio')     AS фио,
    json_extract(r.data, '$.phone')   AS телефон,
    json_extract(r.data, '$.amount')  AS сумма
FROM records r
WHERE r.entity_id = 3
  AND r.status != 'deleted'
ORDER BY r.created_at DESC

Фильтрация по значению поля

-- Текстовое поле
WHERE json_extract(r.data, '$.city') = 'Москва'
-- Числовое поле
WHERE CAST(json_extract(r.data, '$.amount') AS REAL) > 10000
-- Статус (select / colored_status) — хранится как строка-ключ
WHERE json_extract(r.data, '$.order_status') = 'confirmed'
-- Булево поле — хранится как 0 или 1
WHERE json_extract(r.data, '$.is_vip') = 1
-- Дата: сравнение строк работает для формата YYYY-MM-DD
WHERE json_extract(r.data, '$.date_doc') >= '2024-01-01'
  AND json_extract(r.data, '$.date_doc') <= '2024-12-31'
-- Reference-поле — хранит числовой id связанной записи
WHERE CAST(json_extract(r.data, '$.client_id') AS INTEGER) = 42

Форматирование дат

-- Дата в формате ДД.ММ.ГГГГ (с учётом timezone +3 часа)
strftime('%d.%m.%Y', datetime(json_extract(r.data, '$.date_doc'), '+3 hours'))
-- Дата и время
strftime('%d.%m.%Y %H:%M', datetime(json_extract(r.data, '$.created_datetime'), '+3 hours'))
-- created_at — системная колонка, тоже UTC:
strftime('%d.%m.%Y', datetime(r.created_at, '+3 hours'))

Замените +3 hours на ваш часовой пояс.

Числа и агрегация

SELECT
    COUNT(*)                                                   AS кол_во,
    SUM(CAST(json_extract(r.data, '$.amount') AS REAL))       AS итого,
    AVG(CAST(json_extract(r.data, '$.amount') AS REAL))       AS среднее,
    MAX(CAST(json_extract(r.data, '$.amount') AS REAL))       AS максимум
FROM records r
WHERE r.entity_id = 7
  AND r.status = 'posted'

5. JOIN

Принцип

Так как все записи всех сущностей живут в одной таблице records, JOIN на связанные записи — это самосоединение (records JOIN records). Ключ связи — это значение reference-поля, в котором хранится id связанной записи.

Шаблон LEFT JOIN

SELECT
    r.id,
    {fio}                                          AS клиент,
    json_extract(client.data, '$.company')         AS компания,
    json_extract(client.data, '$.inn')             AS инн,
    {amount}                                       AS сумма
FROM records r
-- Соединяем по reference-полю 'client_id' → id записи в другой сущности
LEFT JOIN records client
    ON  client.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)
    AND client.status != 'deleted'
WHERE r.entity_id = 7        -- сущность «Договоры»
  AND r.status = 'posted'

CAST(...AS INTEGER) — обязателен для reference-полей, потому что JSON хранит числа, но SQLite может интерпретировать их как TEXT при сравнении с INTEGER.

Несколько JOIN

SELECT
    r.id,
    r.status,
    {doc_number}                                        AS номер,
    {date_doc:date}                                     AS дата,
    json_extract(client.data,  '$.fio')                AS клиент,
    json_extract(manager.data, '$.display_name')       AS менеджер,
    json_extract(product.data, '$.title')              AS товар,
    {amount}                                           AS сумма
FROM records r
LEFT JOIN records client
    ON client.id = CAST(json_extract(r.data, '$.client_id')  AS INTEGER)
LEFT JOIN records manager
    ON manager.id = CAST(json_extract(r.data, '$.manager_id') AS INTEGER)
LEFT JOIN records product
    ON product.id = CAST(json_extract(r.data, '$.product_id') AS INTEGER)
WHERE r.entity_id = 12
  AND r.status != 'deleted'
ORDER BY r.created_at DESC

Фильтрация через JOIN

-- Только договоры клиентов из Москвы:
WHERE json_extract(client.data, '$.city') = 'Москва'
-- Только если менеджер нашёлся (INNER JOIN логика через WHERE):
WHERE manager.id IS NOT NULL

JOIN с users (кто создал запись)

SELECT
    r.id,
    {fio}              AS клиент,
    u.display_name     AS создал,
    r.created_at
FROM records r
LEFT JOIN users u ON u.id = r.created_by
WHERE r.entity_id = 5

6. Табличные части

Строки табличных частей (вложенные таблицы в документах) хранятся в отдельной таблице record_rows.

record_rows
├── id         INTEGER  — id строки
├── record_id  INTEGER  — FK → records.id (к какому документу)
├── section    TEXT     — имя секции (вкладки) табличной части
├── data       TEXT     — JSON с полями строки (аналогично records.data)
└── sort_order INTEGER  — порядок строки

Запрос с табличной частью

SELECT
    r.id                                                AS id_документа,
    {doc_number}                                        AS номер,
    {date_doc:date}                                     AS дата,
    rr.sort_order                                       AS строка_номер,
    json_extract(rr.data, '$.product_name')            AS товар,
    json_extract(rr.data, '$.qty')                     AS кол_во,
    json_extract(rr.data, '$.price')                   AS цена,
    CAST(json_extract(rr.data, '$.qty')   AS REAL)
        * CAST(json_extract(rr.data, '$.price') AS REAL) AS сумма_строки
FROM records r
JOIN record_rows rr ON rr.record_id = r.id AND rr.section = 'items'
WHERE r.entity_id = 8
  AND r.status = 'posted'
ORDER BY r.id, rr.sort_order

section — это имя вкладки табличной части из настроек сущности.

Агрегация по строкам таблицы

SELECT
    r.id,
    {doc_number}                                                    AS номер,
    COUNT(rr.id)                                                    AS строк,
    SUM(CAST(json_extract(rr.data, '$.qty')   AS REAL)
      * CAST(json_extract(rr.data, '$.price') AS REAL))            AS итого
FROM records r
LEFT JOIN record_rows rr ON rr.record_id = r.id AND rr.section = 'items'
WHERE r.entity_id = 8
  AND r.status = 'posted'
GROUP BY r.id
ORDER BY r.id DESC

7. Аудит-лог и мета-таблицы

audit_log — история изменений

audit_log
├── id            — id записи лога
├── entity_id     — сущность
├── entity_label  — название сущности (текст)
├── record_id     — id изменённой записи
├── record_title  — заголовок записи (текст, на момент изменения)
├── action        — created / updated / deleted / status_changed / merged
├── user_label    — имя пользователя (текст)
├── diff          — JSON с изменениями (старые/новые значения)
├── record_status — статус документа на момент изменения
└── changed_at    — дата и время изменения (UTC)

-- Все изменения конкретной записи:
SELECT
    strftime('%d.%m.%Y %H:%M', datetime(al.changed_at, '+3 hours')) AS дата,
    al.action       AS действие,
    al.user_label   AS пользователь,
    al.record_status AS статус
FROM audit_log al
WHERE al.record_id = 123
ORDER BY al.changed_at DESC

-- Активность пользователей за месяц:
SELECT
    al.user_label              AS пользователь,
    COUNT(*)                   AS всего_действий,
    SUM(al.action = 'created') AS создано,
    SUM(al.action = 'updated') AS изменено,
    SUM(al.action = 'deleted') AS удалено
FROM audit_log al
WHERE al.changed_at >= '2024-03-01'
  AND al.changed_at <  '2024-04-01'
GROUP BY al.user_label
ORDER BY всего_действий DESC

entities и fields — метаданные

-- Список всех сущностей и количество записей:
SELECT
    e.label                            AS сущность,
    e.type                             AS тип,
    COUNT(r.id)                        AS записей,
    SUM(r.status = 'deleted')          AS удалено
FROM entities e
LEFT JOIN records r ON r.entity_id = e.id
GROUP BY e.id
ORDER BY e.sort_order

-- Все поля конкретной сущности:
SELECT name, label, field_type, is_required, is_encrypted, search_mode
FROM fields
WHERE entity_id = 5
ORDER BY sort_order

8. Пагинация и LIMIT

Система по-разному обрабатывает запросы с LIMIT и без него.

Без LIMIT (рекомендуется)

Платформа сама добавляет пагинацию и считает общее количество строк (total). В отчёте работают кнопки «следующая/предыдущая страница».

SELECT r.id, {fio}, {amount}
FROM records r
WHERE r.entity_id = 5
ORDER BY r.created_at DESC
-- LIMIT не указан → платформа добавит LIMIT/OFFSET сама

С LIMIT (ручное управление)

Если LIMIT есть в запросе — пагинация отключается. total = количество возвращённых строк. Используйте только когда нужен топ-N или жёсткое ограничение.

SELECT r.id, {fio}, {amount}
FROM records r
WHERE r.entity_id = 5
ORDER BY {amount} DESC
LIMIT 10   -- топ-10, пагинации нет

Подзапросы с LIMIT

Внутри подзапросов LIMIT можно использовать свободно — платформа проверяет только верхний уровень запроса:

SELECT *
FROM (
    SELECT r.id, {amount}
    FROM records r
    WHERE r.entity_id = 5
    ORDER BY {amount} DESC
    LIMIT 100    -- внутри подзапроса — ок
) sub
WHERE CAST(sub.amount AS REAL) > 1000

9. Зашифрованные поля

Как хранится зашифрованное поле

Поля с включённым шифрованием хранятся в data с префиксом enc::

{
  "fio":      "enc:gAAAAA...base64...",
  "passport": "enc:gAAAAA...base64...",
  "fio_hash": "a3f8c1d2e4..."
}

Рядом с зашифрованным значением платформа автоматически сохраняет HMAC-хэш в поле {name}_hash — но только если у поля включён search_mode = exact или like в настройках сущности. Хэш используется для точного поиска без расшифровки.

SELECT — отображение зашифрованного поля

Платформа автоматически расшифровывает все значения enc:... в результатах SQL. Просто используйте обычный макрос — расшифрованное значение придёт в таблицу:

SELECT {fio}, {passport}, {phone}
FROM records r
WHERE r.entity_id = 5

WHERE — фильтрация по зашифрованному полю: `{enc:field}`

Прямое сравнение WHERE {fio} = 'Иванов' не работает — в базе лежит enc:gAAAAA..., а не имя. Для точного сравнения используйте макрос {enc:field}:

WHERE {enc:fio} = 'Иванов Иван Иванович'

Макрос разворачивается в сравнение по HMAC-хэшу:

-- Что выполнится в базе:
WHERE json_extract(r.data, '$.fio_hash') = 'a3f8c1d2e4...'

Хэш вычисляется регистронезависимо — 'Иванов', 'иванов', 'ИВАНОВ' дадут одинаковый результат.

Требование: поле должно иметь search_mode = exact или like. Если search_mode = none — хэш не сохраняется и макрос вернёт пустой результат.

GROUP BY — группировка по зашифрованному полю: `{hash:field}`

Группировать по {fio} нельзя — расшифрованное значение недоступно на уровне SQL. Группировка по хэшу даёт корректный результат: один хэш = один уникальный клиент:

SELECT {fio} AS клиент, COUNT(*) AS сделок, SUM(CAST({amount} AS REAL)) AS сумма
FROM records r
WHERE r.entity_id = 7
GROUP BY {hash:fio}

Макрос {hash:field} разворачивается в:

json_extract(r.data, '$.fio_hash')

Сводная таблица макросов для зашифрованных полей

Макрос	Где использовать	Разворачивается в	Требование
`{field}`	SELECT	`json_extract(r.data, '$.field')` + авторасшифровка	—
`{enc:field} = 'значение'`	WHERE	`json_extract(r.data, '$.field_hash') = '<hmac>'`	`search_mode != none`
`{hash:field}`	GROUP BY	`json_extract(r.data, '$.field_hash')`	`search_mode != none`

Что не поддерживается

LIKE по зашифрованному полю — частичный поиск (WHERE {fio} LIKE '%иванов%') невозможен на уровне SQL. Используйте стандартный поиск платформы на странице записей.
ORDER BY по зашифрованному полю — сортировка по хэшу бессмысленна. Отсортировать по расшифрованному ФИО в SQL нельзя.
{enc:field} без правой части — макрос обязательно должен использоваться с = 'значение'. Использование в SELECT или GROUP BY не имеет смысла.

10. Ограничения безопасности

Правило	Детали
Только `SELECT`	Запросы должны начинаться с `SELECT`
Запрещённые операторы	`INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `TRUNCATE`, `CREATE`, `REPLACE`, `ATTACH`, `DETACH`, `PRAGMA`, `VACUUM`, `REINDEX`
Без комментариев	`--`, `/`, `/` запрещены
Один запрос	Точка с запятой в середине запрещена
Read-only соединение	Физически, на уровне SQLite-соединения

11. Типовые задачи

Реестр документов с расшифровкой связей

SELECT
    r.id,
    r.status,
    {doc_number}                                         AS номер,
    {date_doc:date}                                      AS дата,
    json_extract(client.data, '$.fio')                   AS клиент,
    json_extract(client.data, '$.phone')                 AS телефон,
    {amount}                                             AS сумма,
    {comment}                                            AS примечание
FROM records r
LEFT JOIN records client
    ON client.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)
WHERE r.entity_id = 7
  AND r.status IN ('posted', 'active')
ORDER BY r.created_at DESC

Продажи по менеджерам за период

SELECT
    json_extract(manager.data, '$.fio')                  AS менеджер,
    COUNT(r.id)                                          AS сделок,
    SUM(CAST(json_extract(r.data, '$.amount') AS REAL))  AS сумма_итого,
    AVG(CAST(json_extract(r.data, '$.amount') AS REAL))  AS средний_чек,
    MAX(CAST(json_extract(r.data, '$.amount') AS REAL))  AS макс_сделка
FROM records r
LEFT JOIN records manager
    ON manager.id = CAST(json_extract(r.data, '$.manager_id') AS INTEGER)
WHERE r.entity_id = 12
  AND r.status = 'posted'
  AND json_extract(r.data, '$.date_doc') >= '2024-01-01'
  AND json_extract(r.data, '$.date_doc') <= '2024-12-31'
GROUP BY json_extract(r.data, '$.manager_id')
ORDER BY сумма_итого DESC

Детализация по строкам табличной части с JOIN

SELECT
    r.id                                                          AS id_заказа,
    {doc_number}                                                  AS номер,
    {date_doc:date}                                               AS дата,
    json_extract(client.data, '$.fio')                            AS клиент,
    json_extract(rr.data, '$.product_name')                       AS товар,
    CAST(json_extract(rr.data, '$.qty')   AS REAL)                AS кол_во,
    CAST(json_extract(rr.data, '$.price') AS REAL)                AS цена,
    CAST(json_extract(rr.data, '$.qty')   AS REAL)
      * CAST(json_extract(rr.data, '$.price') AS REAL)            AS сумма_строки
FROM records r
JOIN  record_rows rr    ON rr.record_id = r.id AND rr.section = 'items'
LEFT JOIN records client ON client.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)
WHERE r.entity_id = 8
  AND r.status = 'posted'
ORDER BY r.id DESC, rr.sort_order

Остатки / задолженности (без оплаты)

SELECT
    json_extract(client.data, '$.fio')                           AS клиент,
    COUNT(r.id)                                                  AS документов,
    SUM(CAST(json_extract(r.data, '$.amount') AS REAL))          AS начислено,
    SUM(CAST(json_extract(r.data, '$.paid')   AS REAL))          AS оплачено,
    SUM(CAST(json_extract(r.data, '$.amount') AS REAL))
      - SUM(CAST(json_extract(r.data, '$.paid') AS REAL))        AS задолженность
FROM records r
LEFT JOIN records client
    ON client.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)
WHERE r.entity_id = 9
  AND r.status = 'posted'
GROUP BY json_extract(r.data, '$.client_id')
HAVING задолженность > 0
ORDER BY задолженность DESC

История изменений конкретного документа

SELECT
    strftime('%d.%m.%Y %H:%M', datetime(al.changed_at, '+3 hours')) AS дата,
    al.action                                                         AS действие,
    al.user_label                                                     AS пользователь,
    al.record_status                                                  AS статус
FROM audit_log al
WHERE al.entity_id = 7
  AND al.record_id = 123
ORDER BY al.changed_at

Записи без связанного объекта (проверка целостности)

SELECT
    r.id,
    {fio}                                                         AS клиент,
    {date_doc:date}                                               AS дата
FROM records r
LEFT JOIN records client
    ON client.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)
WHERE r.entity_id = 7
  AND r.status != 'deleted'
  AND client.id IS NULL   -- клиент не найден или удалён
ORDER BY r.created_at DESC

Топ-10 клиентов по сумме

SELECT
    json_extract(client.data, '$.fio')                           AS клиент,
    COUNT(r.id)                                                  AS сделок,
    SUM(CAST(json_extract(r.data, '$.amount') AS REAL))          AS сумма
FROM records r
LEFT JOIN records client
    ON client.id = CAST(json_extract(r.data, '$.client_id') AS INTEGER)
WHERE r.entity_id = 7
  AND r.status = 'posted'
GROUP BY json_extract(r.data, '$.client_id')
ORDER BY сумма DESC
LIMIT 10

Поиск по зашифрованному ФИО

SELECT
    r.id,
    {fio}          AS фио,
    {phone}        AS телефон,
    {date_doc:date} AS дата
FROM records r
WHERE r.entity_id = 5
  AND {enc:fio} = 'Иванов Иван Иванович'
  AND r.status != 'deleted'

Несколько зашифрованных полей в фильтре

SELECT r.id, {fio}, {email}, {amount}
FROM records r
WHERE r.entity_id = 7
  AND {enc:fio}   = 'Петров Сергей'
  AND {enc:email} = 'petrov@example.com'
  AND r.status = 'posted'

Сводка по клиентам с зашифрованным ФИО

SELECT
    {fio}                              AS клиент,
    COUNT(*)                           AS сделок,
    SUM(CAST({amount} AS REAL))        AS сумма_итого,
    MAX({date_doc:date})               AS последняя_сделка
FROM records r
WHERE r.entity_id = 9
  AND r.status = 'posted'
GROUP BY {hash:fio}
ORDER BY сумма_итого DESC

Зашифрованное поле + JOIN + фильтр

SELECT
    r.id,
    {fio}                                          AS клиент,
    {passport}                                     AS паспорт,
    json_extract(manager.data, '$.display_name')   AS менеджер,
    {amount}                                       AS сумма,
    {date_doc:date}                                AS дата
FROM records r
LEFT JOIN records manager
    ON manager.id = CAST(json_extract(r.data, '$.manager_id') AS INTEGER)
WHERE r.entity_id = 12
  AND {enc:fio} = 'Сидоров Алексей'
  AND r.status = 'posted'
ORDER BY r.created_at DESC

Документация актуальна для Platform v0.9.9.6 / SQLite диалект