MySQL查询：筛选JSON数组中timestamp在指定区间的用户

给定表结构如下：

需求：找出所有发送过时间戳在1662000000至1663000000之间消息的用户（只要有任意一条符合即可），且无独立消息表，只能从现有表查询。

不同数据库的实现方案 #

MySQL（5.7+ 支持JSON_TABLE）

SELECT DISTINCT t.user
FROM your_table t
JOIN JSON_TABLE(
    t.info->'$.messages',
    '$[*]' COLUMNS(
        timestamp BIGINT PATH '$.timestamp'
    )
) jt
WHERE jt.timestamp BETWEEN 1662000000 AND 1663000000;

通过JSON_TABLE将info字段中的messages数组拆分为行记录，筛选时间戳在目标区间的条目后，用DISTINCT去重得到符合条件的用户。

PostgreSQL

SELECT DISTINCT t.user
FROM your_table t
CROSS JOIN LATERAL json_array_elements(t.info::json->'messages') AS msg
WHERE (msg->>'timestamp')::BIGINT BETWEEN 1662000000 AND 1663000000;

使用json_array_elements展开JSON数组，提取timestamp字段并转换为数值类型，筛选后去重得到结果。如果你的字段是jsonb类型，把json->换成jsonb->即可。

SQL Server

SELECT DISTINCT t.user
FROM your_table t
CROSS APPLY OPENJSON(t.info, '$.messages')
WITH (
    timestamp BIGINT '$.timestamp'
) AS jt
WHERE jt.timestamp BETWEEN 1662000000 AND 1663000000;

借助OPENJSON解析messages数组并指定字段类型，筛选符合时间范围的记录后去重。

低版本数据库兼容方案（不推荐，可靠性差） #

如果你的数据库不支持JSON解析函数，可以尝试字符串匹配，但这种方法可能误匹配其他包含相似数字的字段：

SELECT user
FROM your_table
WHERE info LIKE '%"timestamp": 1662%' 
   OR info LIKE '%"timestamp": 1663000000%';

内容的提问来源于stack exchange，提问作者HeyThereAmI