使用BuildRowSetFromJSON()解析嵌套JSON遇阻，无法获取目标content字段

问题场景 #

使用Salesforce Marketing Cloud的BuildRowSetFromJSON()函数解析OpenAI返回的JSON数据时，目标是获取choices数组内message对象的content字段，但当前代码执行后输出的是finish_reason字段的"stop"值。

原因分析 #

当前代码中Field(Row(@rows,1),3)直接提取了choices数组元素的第三个字段（对应finish_reason），而message是嵌套对象，无法通过直接索引字段位置的方式获取其内部的content，需要通过JSONPath精准定位或分步解析嵌套对象。

修正后的代码 #

方法1：直接定位目标字段（推荐） #

%%[
SET @json = '{"id":"chatcmpl-7bfXu0KfUg1NaPV4CVR3BRi0eFcPT","object":"chat.completion","created":1689212194,"model":"gpt-3.5-turbo-0613","choices":[{"index":0,"message":{"role":"assistant","content":"<!DOCTYPE html>\n<html>\n<head>\n <title>Thank You Email</title>\n</head>\n<body>\n <div style=\"padding: 20px;\">\n <h1><img src=\"https://example.com/logo.png\" alt=\"Logo\" style=\"height: 50px;\"> Thank You for Your Support!</h1>\n <p>Dear [Recipient\'s Name],</p>\n <p>We would like to express our heartfelt gratitude for your support and cooperation. Your contribution has made a significant impact on our organization and our cause.</p>\n <p>Thanks to your generosity, we have been able to [describe the achievement or project made possible with their support]. Without your help, this would not have been possible.</p>\n <p>We value your ongoing commitment and would like to extend our sincere thanks once again for believing in our mission and choosing to support our work. Your contribution is truly appreciated.</p>\n <p>If you have any further questions or would like to stay updated on our progress, please feel free to reach out to us. We are always here to answer any queries you may have.</p>\n <p>Once again, thank you for your kindness and support.</p>\n <p>Warm regards,<br>[Your Name]<br>[Your Organization]</p>\n </div>\n</body>\n</html>"}},"finish_reason":"stop"}],"usage":{"prompt_tokens":13,"completion_tokens":281,"total_tokens":294}}'

-- 用JSONPath直接定位到choices数组中每个元素的message.content
SET @contentRows = BuildRowSetFromJson(@json, '$.choices[*].message.content', 0)
SET @content = Field(Row(@contentRows, 1), 1)

]%%

Out: %%=v(@content)=%%

方法2：分步解析嵌套对象 #

%%[
SET @json = '{"id":"chatcmpl-7bfXu0KfUg1NaPV4CVR3BRi0eFcPT","object":"chat.completion","created":1689212194,"model":"gpt-3.5-turbo-0613","choices":[{"index":0,"message":{"role":"assistant","content":"<!DOCTYPE html>\n<html>\n<head>\n <title>Thank You Email</title>\n</head>\n<body>\n <div style=\"padding: 20px;\">\n <h1><img src=\"https://example.com/logo.png\" alt=\"Logo\" style=\"height: 50px;\"> Thank You for Your Support!</h1>\n <p>Dear [Recipient\'s Name],</p>\n <p>We would like to express our heartfelt gratitude for your support and cooperation. Your contribution has made a significant impact on our organization and our cause.</p>\n <p>Thanks to your generosity, we have been able to [describe the achievement or project made possible with their support]. Without your help, this would not have been possible.</p>\n <p>We value your ongoing commitment and would like to extend our sincere thanks once again for believing in our mission and choosing to support our work. Your contribution is truly appreciated.</p>\n <p>If you have any further questions or would like to stay updated on our progress, please feel free to reach out to us. We are always here to answer any queries you may have.</p>\n <p>Once again, thank you for your kindness and support.</p>\n <p>Warm regards,<br>[Your Name]<br>[Your Organization]</p>\n </div>\n</body>\n</html>"}},"finish_reason":"stop"}],"usage":{"prompt_tokens":13,"completion_tokens":281,"total_tokens":294}}'

-- 先提取choices数组中的message对象
SET @messageRows = BuildRowSetFromJson(@json, '$.choices[*].message', 0)
SET @messageObj = Field(Row(@messageRows, 1), 1)

-- 从message对象中解析content字段
SET @contentRows = BuildRowSetFromJson(@messageObj, '$.content', 0)
SET @content = Field(Row(@contentRows, 1), 1)

]%%

Out: %%=v(@content)=%%

说明 #

• 方法1利用JSONPath的嵌套定位能力，直接获取目标字段，代码更简洁高效，适合仅需获取content的场景。
• 方法2分步解析嵌套对象，适合需要同时获取message内其他字段（如role）的场景。

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