PostgreSQL如何查询JSON数组列中指定status对应的时间值

解决方法 #

首先要根据你log列的实际类型（json/jsonb）选择对应的函数，以下是常用的两种查询场景：

场景1：仅返回存在delivered状态的行，对应拿到时间

如果log是jsonb类型，语句如下：

SELECT
  (log_element ->> 'date')::timestamptz AS delivered_date
  -- 可补充其他需要返回的原表字段
FROM
  你的表名,
  jsonb_array_elements(log) AS log_element
WHERE
  log_element ->> 'status' = 'delivered';

如果log是json类型，把jsonb_array_elements替换为json_array_elements即可。

场景2：返回所有原表行，无delivered状态的行时间字段返回NULL

用左连接横向展开实现：

SELECT
  t.*,
  (log_element ->> 'date')::timestamptz AS delivered_date
FROM
  你的表名 t
LEFT JOIN LATERAL
  jsonb_array_elements(t.log) AS log_element
  ON log_element ->> 'status' = 'delivered';

注意事项 #

• 你给出的示例数据中状态值拼写为deivered（缺失字母l），如果实际数据中确实保留了这个拼写错误，需要把WHERE条件里的delivered对应修改为实际存储的值。
• 如果你只需要字符串格式的时间，不需要转成时间戳类型，去掉::timestamptz的强制转换即可。
• 如果单条数据的log数组中存在多个delivered状态的元素，上述查询会为每个匹配的元素返回一行，如果你只需要取第一个匹配的时间，可以加DISTINCT ON (t.主键字段)或者聚合函数做过滤。

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