1

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
HeyThereAmI
  • 13
  • 4
  • Parse using [JSON_TABLE()](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html) then check. – Akina Sep 13 '22 at 09:06
  • Is each message only have two timestamp? – flyingfox Sep 13 '22 at 09:38
  • Each message have only one timestamp, in example user sent two messages with timestamps 1663000000 and 1662000000 – HeyThereAmI Sep 13 '22 at 10:03
  • Does each "messages" array contains not more than two elements with separate message timestamps? Or maybe there exists some upper limit other than 2? – Akina Sep 13 '22 at 10:06
  • 1
    You say you don't have a messages table, but do you control that? Because this should really be replaced by a messages table if you are going to use a relational database. – Jerry Sep 13 '22 at 10:12
  • There may be any amount of messages – HeyThereAmI Sep 13 '22 at 10:12
  • @HeyThereAmI, Out of curiosity, why did you decide to use JSON for the array of messages? If you stored messages in a separate table with one message per row, querying it would be much simpler. – Bill Karwin Sep 13 '22 at 16:09

1 Answers1

1

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

griv
  • 2,098
  • 2
  • 12
  • 15