如何使用json_extract_path_text从JSON数组列提取对应itemCode的amount值？

假设你的JSON数组存储在名为json_column的列中，表名为your_table，以下是具体实现方案：

1. 拆分JSON数组为单行JSON对象 #

首先需要将数组拆分为单个JSON对象行，这是提取单个元素属性的前提。以Redshift/PostgreSQL为例，使用json_array_elements实现横向拆分：

SELECT elem AS single_json_obj
FROM your_table,
     json_array_elements(json_column) AS elem;

2. 提取itemCode和对应amount值 #

基于拆分后的单行JSON对象，使用json_extract_path_text提取目标字段，同时可将amount转换为数值类型方便后续计算：

SELECT
  json_extract_path_text(single_json_obj, 'itemCode') AS item_code,
  json_extract_path_text(single_json_obj, 'amount')::numeric AS amount
FROM (
  SELECT elem AS single_json_obj
  FROM your_table,
       json_array_elements(json_column) AS elem
) AS sub_query;

3. 筛选特定itemCode的amount #

如果只需要某个特定itemCode（比如ABC）的amount值，添加WHERE条件即可：

SELECT
  json_extract_path_text(single_json_obj, 'amount')::numeric AS target_amount
FROM (
  SELECT elem AS single_json_obj
  FROM your_table,
       json_array_elements(json_column) AS elem
) AS sub_query
WHERE json_extract_path_text(single_json_obj, 'itemCode') = 'ABC';

关键说明：

• json_extract_path_text接收两个参数：第一个是JSON对象/字符串，第二个是要提取的键名，返回对应值的字符串形式。
• 数组拆分函数因SQL方言略有差异：Redshift/PostgreSQL用json_array_elements，其他数据库可使用对应数组拆分函数（如UNNEST配合JSON转换工具）。

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