MySQL find in database where value in array in JSON is BETWEEN something

HeyThereAmI 0
Viewed 54

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

mysql-json mysql sql
1 Answers

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

GRIV0
Related