PostgreSQL中如何遍历完整JSON数组并提取目标字段值？

你之前调用json_each失败的核心原因是该函数默认适配JSON对象的键值对遍历，处理JSON数组只要调整调用方式，或使用对应数据库的数组展开函数即可，不同主流数据库的实现如下：

SQLite 实现 #

直接将JSON数组作为参数传入json_each即可自动展开数组元素：

SELECT 
  json_extract(value, '$.uniqid') AS uniqid,
  json_extract(value, '$.time') AS time
FROM json_each('[{"uniqid": "LL.NEW", "time": 1}, {"uniqid": "LL.MODIFY", "time": 3}]')

PostgreSQL 实现 #

使用jsonb_array_elements（JSON类型则用json_array_elements）展开数组：

SELECT 
  elem->>'uniqid' AS uniqid,
  (elem->>'time')::int AS time
FROM jsonb_array_elements('[{"uniqid": "LL.NEW", "time": 1}, {"uniqid": "LL.MODIFY", "time": 3}]'::jsonb) AS elem;

MySQL 实现 #

使用JSON_TABLE函数将数组解析为结构化行数据：

SELECT *
FROM JSON_TABLE(
  '[{"uniqid": "LL.NEW", "time": 1}, {"uniqid": "LL.MODIFY", "time": 3}]',
  '$[*]' COLUMNS (
    uniqid VARCHAR(20) PATH '$.uniqid',
    time INT PATH '$.time'
  )
) AS jt;

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