如何在Redshift中从字符型JSON数组字段提取amount值？

错误原因 #

1. Redshift不支持json类型，不能用::json做类型转换，这是第一个报错type "json" does not exist的直接原因。
2. priceitems存储的是JSON数组，而json_extract_path_text只能直接处理JSON对象，直接传入数组会触发"invalid json object"解析错误。

可用解决方案 #

方法1：组合使用原生JSON函数 #

先提取数组的第一个元素（示例数组仅含一个元素），再提取amount值：

SELECT
  JSON_EXTRACT_PATH_TEXT(
    JSON_EXTRACT_ARRAY_ELEMENT_TEXT(priceitems, 0),
    'amount'
  ) AS amount
FROM
  expenses

方法2：用JSON_VALUE简化写法（Redshift 1.0.1250及以上版本支持） #

通过JSON路径表达式直接定位提取，写法更简洁：

SELECT
  JSON_VALUE(priceitems, '$[0].amount') AS amount
FROM
  expenses

方法3：转换为Super类型处理（支持Super的集群） #

将字段转换为Super类型后，可直接通过下标和键名访问对应值：

SELECT
  JSON_PARSE(priceitems)[0].amount AS amount
FROM
  expenses

提取数组所有元素的amount（若数组含多个对象） #

如果数组包含多个JSON对象，用UNNEST展开后批量提取：

SELECT
  item.amount
FROM
  expenses,
  UNNEST(JSON_PARSE(priceitems)) AS item

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