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

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

CodePudding user response：

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 from there:

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