如何在PostgreSQL中从jsonb数组筛选含特定星期几的行？

要从存储datetime字符串的JSONB数组列中筛选出包含特定星期几的行，可以通过以下两种高效方法实现（以筛选星期二为例）：

方法1：使用EXISTS子查询（推荐） #

通过展开JSONB数组元素，将字符串转换为timestamp类型后提取星期几，判断该行是否存在符合条件的元素：

SELECT *
FROM my_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(jsonb_col) AS dt_str
    WHERE extract(dow FROM dt_str::timestamp) = 2 -- 注：PostgreSQL中`dow`取值为0=周日，1=周一，2=周二，…，6=周六
);

方法2：使用ANY结合数组转换 #

将JSONB数组转换为星期几的数值数组，再检查目标值是否存在于该数组中：

SELECT *
FROM my_table
WHERE 2 = ANY (
    ARRAY(
        SELECT extract(dow FROM dt_str::timestamp)
        FROM jsonb_array_elements_text(jsonb_col) AS dt_str
    )
);

关键说明 #

• jsonb_array_elements_text用于将JSONB数组展开为单个字符串元素；
• dt_str::timestamp利用PostgreSQL对ISO 8601格式的自动识别，将字符串转换为时间类型；
• extract(dow FROM ...)用于提取星期几的数值，可根据需求修改数值（如筛选周一则改为1）。

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