Как построить запрос SQLite с предложением WHERE внутри столбца JSON-массива-объектов?
В связи с этим вопросом я пытаюсь построить высокопроизводительный* запрос SQLite на столбце JSON.
Рассматривая БД с этой схемой (псевдокод):
** Table **
id content json
1 abc [{start: some-unix-ts, end: some-unix-ts},{start: some-unix-ts, end: some-unix-ts}, between 1 to 1K+ items]
2 xyz ...
Можно ли написать запрос, извлекающий все события, где содержимое = xyz И где есть хотя бы одно событие в будущем или между двумя временными метками.
В псевдокоде:
SELECT * FROM events WHERE content = xyz AND start > some-unix-ts
*) Под высокой производительностью я подразумеваю (не имея опыта работы с SQL): если на теоретически огромной БД это вообще неэффективно, позвольте мне сейчас!
Кроме того, базовый SQLite не поддерживает столбцы JSON. У него нет полной системы типов — он хранит все строки (включая числа). Доступно расширение JSON, но поскольку оно не является основным, оно никогда не будет супер быстрым.
Если у вас а) есть одна строка для каждого элемента этих json-массивов, б) вы используете формат даты , понятный SQLite , например, метку времени unix или строку iso-8601, которая естественным образом сортируется, у вас будет лучшее время.
@TimRoberts SQLite поддерживает типы помимо строк и сохраняет числовые типы как числа, а не строки (в зависимости от определения таблицы, см. sqlite.org/datatype3.html#type_affinity). И в наши дни функции json включены по умолчанию (и всегда были основной частью SQLite). Не то чтобы OP должен хранить здесь свои данные в массивах json.
Ваши объекты JSON искажены для SQLite, потому что и пути, и значения не заключены в кавычки. Кроме того, значения имеют несопоставимый формат MM-YYYY. Вместо этого вы должны использовать YYYY-MM, если вы действительно заботитесь о производительности, поскольку использование строковых функций для преобразования текущего формата в сопоставимый в предложении WHERE приведет к значительному снижению производительности. Но даже если вы внесете эти изменения, я не верю, что высокопроизводительный запрос выполним для большой базы данных.
@forpas: я обновил свой вопрос (даты действительно указаны в формате метки времени + это, конечно, хорошо отформатированный Json)
Если у вас есть несколько значений start для данного id, вы хотите, чтобы они все или только одно было > some-unix-ts?
И является ли ваш ожидаемый результат только значениями start, которые являются > some-unix-ts, или всей записью для этого id?
@ Ник, я добавил некоторые детали. Запрос должен возвращать events, где, например, есть хотя бы одно вхождение в будущем.
Ответы 1
Вы можете добиться желаемого результата, используя json_each, чтобы разделить значения временных интервалов в столбце json, а затем сравнить их с интересующим вас временем. Например (используя расширенный dbfiddle из ответа на ваш предыдущий вопрос):
SELECT ev.id, ev.content, json_each.value
FROM ev, json_each(ev.json)
WHERE ev.content = 'xyz'
AND json_each.value->>'$.start' > '2022-07'
Выход:
id content value
2 xyz {"start":"2022-11","end":"2022-12"}
3 xyz {"start":"2022-09","end":"2022-10"}
Однако это не будет особенно эффективным. Было бы намного лучше вернуться к исходным таблицам и вместо этого запросить их:
SELECT e.*, t.start, t.end
FROM events e
JOIN timeslots t ON t.id = e.id
WHERE e.content = 'xyz' AND t.start > '2022-07'
Выход:
id content start end
2 xyz 2022-11 2022-12
3 xyz 2022-09 2022-10
Вам нужно будет заменить сравнения start тем, что подходит для ваших фактических данных временного интервала.
@charnould ответил на твой вопрос? Если нет, можете ли вы предоставить дополнительную информацию, чтобы помочь ответить на него?
Спасибо. Я денормализую свои данные: (i) сохраняю timeslotsстолбец в events как JSON, чтобы упростить API запросов для некоторых людей, и (ii) сохраняю timeslots таблицу, чтобы получить производительный запрос.
@charnould звучит как идеальный баланс размера и производительности.
SQLite был разработан для удобства, а не для производительности, и он отстой для огромных баз данных. Если вам нужно высокопроизводительное решение для больших наборов данных, вам нужно использовать настоящий сервер базы данных, такой как MySQL или Postgres. Их не намного сложнее настроить, чем SQLite.