如何在Redshift中从JSON数组列提取指定key的取值

报错原因说明 #

你执行array_to_json相关语句报错，是因为该函数的作用是将PostgreSQL原生数组类型转换为JSON数组，而你的col_1是JSON类型/字符串格式JSON数组，并非PostgreSQL原生数组，参数类型不匹配因此触发报错。

解决方案 #

你已经验证col_1是合法JSON数组，可通过json_array_elements（如果是JSONB类型对应jsonb_array_elements）函数将JSON数组拆分为一行一个JSON元素的结构，再提取category字段即可：

1. 提取所有category，每个值单独占一行（需要保留原表其他字段可自行在SELECT后补充）

SELECT 
  array_element ->> 'category' AS category
FROM 
  mytable,
  json_array_elements(mytable.col_1) AS array_element

2. 如果你需要将同一行原数据的所有category合并为一个数组返回，可搭配聚合函数实现：

SELECT 
  array_agg(array_element ->> 'category') AS category_list
FROM 
  mytable,
  json_array_elements(mytable.col_1) AS array_element
GROUP BY 
  mytable.主键字段 -- 替换为你表的主键列名，比如id

3. 如果只需取数组指定位置的category（比如第一个元素，数组下标从0开始），无需拆数组，直接查询即可：

SELECT 
  col_1 -> 0 ->> 'category' AS first_category
FROM 
  mytable

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