如何从指定JSON数组提取CancellationDate字段？已试json_extract、jsonpath无效

你遇到的问题根源是：这个JSON数组里的元素不是直接的JSON对象，而是序列化后的JSON字符串（外层数组包裹了一个字符串格式的JSON），所以直接用json_extract或jsonpath提取会失效，得先把这个字符串转成JSON对象再操作。

下面分不同场景给你具体解决办法：

场景1：MySQL环境 #

先提取数组里的字符串元素，再转成可解析的JSON，最后提取目标字段：

SELECT 
  (your_json_column->>'$[0]')->>'$.CancellationDate' AS CancellationDate
FROM your_table;

或者用更清晰的分步写法：

SELECT 
  JSON_EXTRACT(
    JSON_UNQUOTE(JSON_EXTRACT(your_json_column, '$[0]')),
    '$.CancellationDate'
  ) AS CancellationDate
FROM your_table;

场景2：BigQuery环境 #

用JSON_PARSE解析字符串为JSON对象后提取字段：

SELECT 
  JSON_VALUE(
    JSON_PARSE(JSON_EXTRACT_SCALAR(your_json_column, '$[0]')),
    '$.CancellationDate'
  ) AS CancellationDate
FROM your_table;

场景3：Python代码处理 #

如果是用Python处理这个JSON，先解析外层数组，再把内层字符串转成JSON对象：

import json

# 原始JSON字符串
raw_json = '["{\"RequestedByUser\":false,\"RequestedBySystem\":null,\"RequestedBySellerNotification\":null,\"RequestedByPaymentNotification\":true,\"Reason\":null,\"CancellationDate\":\"2024-01-16T00:40:59.0928615+00:00\"}"]'

# 解析外层数组
outer_array = json.loads(raw_json)
# 解析内层字符串为JSON对象
inner_json = json.loads(outer_array[0])
# 提取目标字段
cancellation_date = inner_json['CancellationDate']
print(cancellation_date)

内容的提问来源于stack exchange，提问作者Brenda Caroline Prazeres da SI