MySQL находит в базе данных, где значение в массиве в JSON находится МЕЖДУ чем-то
у меня есть база данных
И я хочу выбрать всех пользователей, которые отправили сообщения между отметками времени 1662000000 и 1663000000 (любое количество сообщений, а не все)
У меня нет внешней таблицы сообщений, поэтому я не могу выбрать оттуда
Каждое сообщение имеет только две метки времени?
Каждое сообщение имеет только одну метку времени, например, пользователь отправил два сообщения с метками времени 1663000000 и 1662000000.
Содержит ли каждый массив «сообщения» не более двух элементов с отдельными временными метками сообщений? Или, может быть, существует какой-то верхний предел, отличный от 2?
Вы говорите, что у вас нет таблицы сообщений, но контролируете ли вы ее? Потому что это действительно должно быть заменено таблицей сообщений, если вы собираетесь использовать реляционную базу данных.
Сообщений может быть сколько угодно
@HeyThereAmI, из любопытства, почему вы решили использовать JSON для массива сообщений? Если бы вы хранили сообщения в отдельной таблице, по одному сообщению в строке, запросы к ним были бы намного проще.
Ответы 1
Если вы используете MySQL v8.0.x, вы можете использовать JSON_TABLE для создания форматированной таблицы JSON в подзапросе. Затем выберите своих пользователей DISTINCT, используя метку времени в предложении WHERE следующим образом:
SELECT DISTINCT b.`user` FROM (
SELECT `user`, a.*
FROM `sample_table`,
JSON_TABLE(`info`,'$'
COLUMNS (
NESTED PATH '$.messages[*]'
COLUMNS (
`user_to` int(11) PATH '$.user_to',
`timestamp` int(40) PATH '$.timestamp')
)
) a
) b
WHERE b.`timestamp` BETWEEN 1662000000 AND 1663000000
ORDER BY b.`user` ASC
Вход:
Вывод:
db<>рабочий пример здесь
Разберите с помощью JSON_TABLE(), затем проверьте.