PostgreSQL中如何解析嵌套JSON数组转换为结构化数据表

首先注意：你提供的示例JSON存在语法错误，Info数组内的多个位置对象缺少闭合大括号，正式处理前需要先确保列中存储的是合法可解析的JSON结构。提取逻辑核心是展开Info数组，分别取每个元素下的时间戳、纬度、经度字段，映射为Timestamp/Lat/Lon三列即可，你可以根据自己的数据存储环境选择对应方案：

方案1：直接在数据库内通过SQL生成结构化结果 #

适合数据已经存在数据库中、需要直接在数仓/业务库出结果的场景，不同数据库写法如下：

• PostgreSQL（支持JSON/JSONB类型）
  假设原表名为raw_data，存储JSON内容的列名为json_col，通过LATERAL配合数组展开函数实现：

SELECT
  elem ->> 'timestampPos' AS Timestamp,
  (elem -> 'coordinate' ->> 'latitude')::numeric AS Lat,
  (elem -> 'coordinate' ->> 'longitude')::numeric AS Lon
FROM raw_data,
LATERAL jsonb_array_elements(json_col::jsonb -> 'Info') AS elem;

• MySQL 8.0及以上版本
  使用JSON_TABLE函数直接完成数组展开和字段映射：

SELECT
  jt.timestampPos AS Timestamp,
  jt.latitude AS Lat,
  jt.longitude AS Lon
FROM raw_data,
JSON_TABLE(
  json_col,
  '$.Info[*]' COLUMNS(
    timestampPos VARCHAR(32) PATH '$.timestampPos',
    latitude DECIMAL(10,6) PATH '$.coordinate.latitude',
    longitude DECIMAL(10,6) PATH '$.coordinate.longitude'
  )
) AS jt;

• Spark SQL/大数据数仓场景
  通过explode函数炸开嵌套数组后取字段：

SELECT
  pos.timestampPos AS Timestamp,
  pos.coordinate.latitude AS Lat,
  pos.coordinate.longitude AS Lon
FROM raw_data
LATERAL VIEW explode(from_json(json_col, 'struct<Info:array<struct<timestampPos:string,coordinate:struct<latitude:double,longitude:double>>>>')) AS json_data
LATERAL VIEW explode(json_data.Info) AS pos

方案2：通过Python批量处理生成结构化表 #

适合把数据导出后离线处理、做二次分析的场景，基于pandas实现的示例代码如下：

import pandas as pd
import json

# raw_list为从数据库读取的所有JSON字符串组成的列表
result = []
for json_str in raw_list:
    data = json.loads(json_str)
    # 遍历Info数组下的每一条位置记录
    for item in data.get("Info", []):
        coord = item.get("coordinate", {})
        result.append({
            "Timestamp": item.get("timestampPos"),
            "Lat": coord.get("latitude"),
            "Lon": coord.get("longitude")
        })
# 转换为结构化DataFrame，可直接导出为CSV或写回数据库
df = pd.DataFrame(result)

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