I have database
| user | info |
|---|---|
| 0 | {"messages": [{"user_to": 1, "timestamp": 1663000000}, {"user_to": 2, "timestamp": 1662000000}]} |
| 1 | {"messages": [{"user_to": 0, "timestamp": 1661000000}, {"user_to": 2, "timestamp": 1660000000}]} |
| 2 | {"messages": []} |
And I want to select all users who sent messages between timestamp 1662000000 and 1663000000 (any amount of messages, not all of them)
I don't have external table of messages, so I can't select from there
If you're using MySQL v8.0.x, you can utilize JSON_TABLE to create a JSON formatted table in a subquery. Then, select your DISTINCT users using your timestamp in a WHERE clause like this:
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
Input:
| user | info |
|---|---|
| 0 | {"messages": [{"user_to": 1, "timestamp": 1663000000}, {"user_to": 2, "timestamp": 1662000000}]} |
| 1 | {"messages": [{"user_to": 0, "timestamp": 1661000000}, {"user_to": 2, "timestamp": 1660000000}]} |
| 2 | {"messages": []} |
| 3 | {"messages": [{"user_to": 0, "timestamp": 1662000000}, {"user_to": 2, "timestamp": 1661000000}, {"user_to": 2, "timestamp": 1660000000}, {"user_to": 2, "timestamp": 1663000000}]} |
Output:
| user |
|---|
| 0 |
| 3 |
db<>fiddle here
