如何在PostgreSQL中提取JSON数组内的重复字段值

你的语句返回null的核心原因是videoMetadata是JSON数组，不是单个JSON对象，直接访问数组的toTimestamp属性自然无法获取值，必须先将数组拆分成独立的行元素。

1. 展开JSON数组并提取时间字段 #

使用json_array_elements（若列类型是jsonb则用jsonb_array_elements）将数组拆分成多行，再提取每个元素的时间戳：

SELECT
  t.id, -- 替换为你的表主键字段
  video_item->>'cameraId' AS camera_id,
  (video_item->>'toTimestamp')::timestamp AS to_timestamp,
  (video_item->>'fromTimestamp')::timestamp AS from_timestamp
FROM your_table t,
     json_array_elements(t.metadata->'videoMetadata') AS video_item;

2. 筛选并统计时长超过指定秒数的实例 #

通过计算时间差并转换为秒数，筛选符合条件的记录，比如统计时长超过5秒的实例数量：

SELECT
  COUNT(*) AS long_video_instance_count
FROM your_table t,
     json_array_elements(t.metadata->'videoMetadata') AS video_item
WHERE 
  EXTRACT(EPOCH FROM (
    (video_item->>'toTimestamp')::timestamp - (video_item->>'fromTimestamp')::timestamp
  )) > 5; -- 这里替换为你需要的秒数阈值

关键说明 #

• json_array_elements：将JSON数组拆分为多行，每个数组元素对应一条独立记录
• 时间戳转换：必须将JSON字符串类型的时间戳转为PostgreSQL原生timestamp类型，才能进行时间差计算
• EXTRACT(EPOCH FROM ...)：将时间间隔转换为总秒数，用于时长比较

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