PostgreSQL 13：如何将JSON数组元素拆分为单独列查询

你需要通过JSON数组索引提取来将数组中的每个元素映射到单独列，原查询仅取出了完整数组，未做拆分。以下是针对你的数据的实现方案：

完整查询语句 #

SELECT
  -- 提取dimensions数组的每个元素，索引从0开始
  json_data::json->'dimensions'->>0 AS dim_date,
  json_data::json->'dimensions'->>1 AS dim_not_set_1,
  json_data::json->'dimensions'->>2 AS dim_user_type,
  json_data::json->'dimensions'->>3 AS dim_traffic_source,
  json_data::json->'dimensions'->>4 AS dim_not_set_2,
  json_data::json->'dimensions'->>5 AS dim_not_set_3,
  -- 提取metrics数组中第一个对象的values数组元素
  json_data::json->'metrics'->0->'values'->>0 AS metric_val1,
  json_data::json->'metrics'->0->'values'->>1 AS metric_val2,
  json_data::json->'metrics'->0->'values'->>2 AS metric_val3,
  json_data::json->'metrics'->0->'values'->>3 AS metric_val4
FROM test;

关键说明 #

• PostgreSQL的JSON数组索引从0开始，对应数组中元素的位置；
• ->用于获取JSON对象/数组的子元素（返回JSON类型），->>用于将结果转为文本类型；
• 对于metrics字段，它本身是一个包含单个对象的数组，所以需要先用->0定位到数组内的第一个对象，再提取其values数组的元素。

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