Postgres 10.8中查询JSON字段内符合条件的最近earliest_exit_date方法

解决方案 #

PostgreSQL 10 原生支持json_array_elements函数，如果你环境有特殊限制无法使用该函数，可直接参考方案二的自定义函数实现。

方案一：使用原生json_array_elements实现（推荐） #

假设你存储JSON的text类型字段名为json_col，表名为your_table，查询语句如下：

SELECT
  (
    SELECT MIN((elem ->> 'date')::date)
    FROM json_array_elements((json_col::json) -> 'earliest_exit_date') elem
    WHERE (elem ->> 'date')::date > CURRENT_DATE
  ) AS nearest_exit_date
FROM your_table;

逻辑说明：

• 先将text类型的字段转为json类型，取出earliest_exit_date数组
• 展开数组后过滤出date值大于当前日期的元素
• 取符合条件的最小日期，即为距离当前日期最近的未来日期
• 空数组、无符合条件的日期时返回null

方案二：自定义PL/pgSQL函数实现（无法使用json_array_elements时用） #

先创建处理函数：

CREATE OR REPLACE FUNCTION get_nearest_future_exit(p_json_text text)
RETURNS date AS $$
DECLARE
  v_exit_arr json;
  v_arr_len int;
  v_current date := CURRENT_DATE;
  v_temp_date date;
  v_result date;
BEGIN
  -- 提取目标数组
  v_exit_arr := (p_json_text::json) -> 'earliest_exit_date';
  v_arr_len := json_array_length(v_exit_arr);
  -- 空数组直接返回
  IF v_arr_len = 0 THEN
    RETURN NULL;
  END IF;
  -- 遍历数组找符合条件的最小日期
  FOR i IN 0..v_arr_len - 1 LOOP
    v_temp_date := (v_exit_arr -> i ->> 'date')::date;
    IF v_temp_date > v_current THEN
      IF v_result IS NULL OR v_temp_date < v_result THEN
        v_result := v_temp_date;
      END IF;
    END IF;
  END LOOP;
  RETURN v_result;
END;
$$ LANGUAGE plpgsql STABLE;

调用函数查询：

SELECT get_nearest_future_exit(json_col) AS nearest_exit_date
FROM your_table;

注意事项 #

• 你示例中的预期输出2021-12-21，对应当前日期处于2021-11-01到2021-12-20之间的场景，上述逻辑输出符合预期
• 如果存储的JSON存在格式非法的情况，可在函数中增加异常捕获逻辑避免查询报错

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