MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


create table `sample_table` (
  `user` int,
  `info` varchar(500)
)
insert into `sample_table` (user, info)
values
(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}]}')
select * from `sample_table`


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}]}


/*Using NESTED PATH*/
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



user


0

3


/*Using CONCAT*/
SELECT DISTINCT b.`user` FROM (
      SELECT `user`, a.* 
      FROM `sample_table`, 
      JSON_TABLE(CONCAT('[', info, ']'), '$[*].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


user


0

3

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}]}