c# - SSIS Http Get请求Json反序列化

Question

我正在尝试使用 SSIS 从 JotForm 表单中检索数据，它仅在获得响应时有效，但数据非常困惑，需要反序列化。下面是一个响应示例(我在脚本中放置了一个断点，并将值复制到记事本中)；

"{\"responseCode\":200,\"message\":\"success\",\"content\":[{\"id\":\"555555555555555555\",\"form_id\":\"2222222222222222\",\"ip\":\"195.195.176.147\",\"created_at\":\"2022-02-09 07:04:52\",\"status\":\"ACTIVE\",\"new\":\"1\",\"flag\":\"0\",\"notes\":\"\",\"updated_at\":null,\"answers\":{\"1\":{\"name\":\"standardForm\",\"order\":\"1\",\"text\":\"Standard Form Template UNIT-e\",\"type\":\"control_head\"},\"2\":{\"name\":\"submit2\",\"order\":\"20\",\"text\":\"Submit\",\"type\":\"control_button\"},\"3\":{\"name\":\"familyName\",\"order\":\"2\",\"text\":\"Family Name\",\"type\":\"control_textbox\",\"answer\":\"Michael\"},\"4\":{\"name\":\"givenName\",\"order\":\"3\",\"text\":\"Given Name\",\"type\":\"control_textbox\",\"answer\":\"Myers\"},\"6\":

现在，如您所见，它非常困惑(无论如何对我来说)，我要检索的是 givenName 和 familyName。我创建了一个脚本组件，下面是 main.cs 中的代码；

  public override void CreateNewOutputRows()
    {

        System.Net.ServicePointManager.SecurityProtocol =
            SecurityProtocolType.Tls12 |
            SecurityProtocolType.Tls11 |
            SecurityProtocolType.Tls;

        HttpClient client = new HttpClient();
        client.BaseAddress = new Uri("https://mycompany.jotform.com/API/form/222222222222222222/submissions&apiKey=25555555xxxxxxxxxxx5555555");

        client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));

        string APIUrl = string.Format("https://mycompany.jotform.com/API/form/222222222222222222/submissions&apiKey=25555555xxxxxxxxxxx5555555");

        var response = client.GetAsync(APIUrl).Result;
        if (response.IsSuccessStatusCode)
        {
            var result = response.Content.ReadAsStringAsync().Result;
            var serializer = new JavaScriptSerializer();
            var data = serializer.Deserialize<ResultAPI>(result);

            Output0Buffer.AddRow();
            Output0Buffer.FirstName = data.givenName;
            Output0Buffer.Surname = data.familyName;
           

            Console.WriteLine(result);
            System.Diagnostics.Debug.WriteLine(result);

            var json = JsonSerializer.Serialize(response);

            Console.WriteLine(json);
            System.Diagnostics.Debug.WriteLine(json);


        }
    }

我像这样创建了两个名为 Generic Response 和 ResultAPI 的类 (GenericResponse.cs)；

namespace SC_98d911fb90f6449b86ed276a074f46c6
{
    public class GenericResponse
    {

        public ResultAPI[] ListData { get; set; }
    }


    public class ResultGen
    {
        public GenericResponse Result { get; set; }

    }

}

ResultAPI 类如下所示；

namespace SC_98d911fb90f6449b86ed276a074f46c6
{
    public class ResultAPI
    {
        public string givenName { get; set; }
        public string familyName { get; set; }

    }
}

响应应该发送到我映射的平面文件目标 (.txt)，但实际上没有发送任何内容。

如果您能帮我弄清楚这件事，我将不胜感激。

编辑:根据要求，JSON 响应如下所示(我只发布了第一部分，因为它超出了允许的字符限制)；

"{\"responseCode\":200,\"message\":\"success\",\"content\":[{\"id\":\"555555555555555555\",\"form_id\":\"2222222222222222\",\"ip\":\"195.xxx.xxx.xxx\",\"created_at\":\"2022-02-09 07:04:52\",\"status\":\"ACTIVE\",\"new\":\"1\",\"flag\":\"0\",\"notes\":\"\",\"updated_at\":null,\"answers\":{\"1\":{\"name\":\"standardForm\",\"order\":\"1\",\"text\":\"Standard Form Template UNIT-e\",\"type\":\"control_head\"},\"2\":{\"name\":\"submit2\",\"order\":\"20\",\"text\":\"Submit\",\"type\":\"control_button\"},\"3\":{\"name\":\"familyName\",\"order\":\"2\",\"text\":\"Family Name\",\"type\":\"control_textbox\",\"answer\":\"Michael\"},\"4\":{\"name\":\"givenName\",\"order\":\"3\",\"text\":\"Given Name\",\"type\":\"control_textbox\",\"answer\":\"Myers\"},\"6\":{\"name\":\"title\",\"order\":\"4\",\"text\":\"Title\",\"type\":\"control_dropdown\",\"answer\":\"Dr\"},\"7\":{\"name\":\"gender\",\"order\":\"5\",\"text\":\"Gender\",\"type\":\"control_dropdown\",\"answer\":\"Male\"},\"8\":{\"name\":\"dateOf\",\"order\":\"6\",\"sublabels\":\"{\\\"day\\\":\\\"Day\\\",\\\"month\\\":\\\"Month\\\",\\\"year\\\":\\\"Year\\\",\\\"last\\\":\\\"Last Name\\\",\\\"hour\\\":\\\"Hour\\\",\\\"minutes\\\":\\\"Minutes\\\",\\\"litemode\\\":\\\"Date\\\"}\",\"text\":\"Date of Birth\",\"type\":\"control_datetime\",\"answer\":{\"day\":\"13\",\"month\":\"08\",\"year\":\"1963\"},\"prettyFormat\":\"13-08-1963\"},\"10\":{\"name\":\"nationality\",\"order\":\"7\",\"text\":\"Nationality\",\"type\":\"control_dropdown\",\"answer\":\"AMERICAN SAMOA [796\\/2]\"},\"11\":{\"name\":\"address\",\"order\":\"8\",\"sublabels\":\"{\\\"cc_firstName\\\":\\\"First Name\\\",\\\"cc_lastName\\\":\\\"Last Name\\\",\\\"cc_number\\\":\\\"Credit Card Number\\\",\\\"cc_ccv\\\":\\\"Security Code\\\",\\\"cc_exp_month\\\":\\\"Expiration Month\\\",\\\"cc_exp_year\\\":\\\"Expiration Year\\\",\\\"addr_line1\\\":\\\"Street Address\\\",\\\"addr_line2\\\":\\\"Street Address Line 2\\\",\\\"city\\\":\\\"City\\\",\\\"state\\\":\\\"State \\\\\\/ Province\\\",\\\"postal\\\":\\\"Postal \\\\\\/ Zip Code\\\",\\\"country\\\":\\\"Country\\\"}\",\"text\":\"Address\",\"type\":\"control_address\",\"answer\":{\"addr_line1\":\"17 Clover Avenue\",\"addr_line2\":\"Bethesda\",\"city\":\"Kent\",\"country\":\"United Kingdom\"},\"prettyFormat\":\"Street Address: 17 Clover Avenue<br>Street Address Line 2: Bethesda<br>City: Kent<br>Country: United Kingdom<br>\"},\"12\":{\"name\":\"emailAddress\",\"order\":\"10\",\"text\":\"Email Address\",\"type\":\"control_email\",\"answer\":\"mmyers@gmail.com\"},\"13\":{\"name\":\"telephoneNumber\",\"order\":\"11\",\"sublabels\":\"{\\\"country\\\":\\\"Country Code\\\",\\\"area\\\":\\\"Area Code\\\",\\\"phone\\\":\\\"Phone Number\\\",\\\"full\\\":\\\"Phone Number\\\",\\\"masked\\\":\\\"Please enter a valid phone number.\\\"}\",\"text\":\"Telephone Number\",\"type\":\"control_phone\",\"answer\":{\"country\":\"+44\",\"area\":\"7888\",\"phone\":\"167989\"},\"prettyFormat\":\"(+44) (7888) 167989\"},\"14\":{\"name\":\"mobileTelephone\",\"order\":\"12\",\"sublabels\":\"{\\\"country\\\":\\\"Country Code\\\",\\\"area\\\":\\\"Area Code\\\",\\\"phone\\\":\\\"Phone Number\\\",\\\"full\\\":\\\"Phone Number\\\",\\\"masked\\\":\\\"Please enter a valid phone number.\\\"}\",\"text\":\"Mobile Telephone Number\",\"type\":\"control_phone\",\"answer\":{\"country\":\"+44\",\"area\":\"7499\",\"phone\":\"90377\"},\"prettyFormat\":\"(+44) (7499) 90377\"},\"15\":{\"name\":\"applicationSession\",\"order\":\"13\",\"text\":\"Application Session\",\"type\":\"control_textbox\",\"answer\":\"2020AC\"},\"16\":{\"name\":\"uniteAgent\",\"order\":\"14\",\"text\":\"UNIT-e Agent reference\",\"type\":\"control_textbox\",\"answer\":\"IFCELS\"},\"17\":{\"name\":\"uniteProgramme\",\"order\":\"15\",\"text\":\"UNIT-e Programme code\",\"type\":\"control_textbox\",\"answer\":\"Q3EP\"},\"18\":{\"name\":\"entryYear\",\"order\":\"16\",\"text\":\"Entry year\",\"type\":\"control_textbox\",\"answer\":\"2021\"},\"19\":{\"name\":\"entryMonth\",\"order\":\"17\",\"text\":\"Entry month\",\"type\":\"control_textbox\",\"answer\":\"04\"},\"20\":{\"name\":\"entryPoint\",\"order\":\"18\",\"text\":\"Entry point\",\"type\":\"control_textbox\",\"answer\":\"1\"},\"26\":{\"name\":\"postalzipCode\",\"order\":\"9\",\"text\":\"Postal\\/Zip Code\",\"type\":\"control_textbox\",\"answer\":\"KT1 3ER\"},\"27\":{\"name\":\"uniqueID\",\"order\":\"19\",\"selectedField\":\"528c8b464b1a424916000004\",\"text\":\"Unique ID\",\"type\":\"control_autoincrement\",\"answer\":\"8TYZ2Y\"}}}"

非常感谢，

Answer 1

在您的情况下，您从 API 获得的 JSON 包含 JSON 中的 \ 个字符，所以你需要反序列化，如下面的尝试所示。您的 Model 结构也将包含动态节点。

为此，您需要使用 Newtonsoft 程序包，它可以序列化和反序列化任何 .NET 对象。您可以通过 Visual Studio 中的 Nuget 管理器添加此包，或在您的项目中手动添加引用。

当您的 Answer 属性可以是 string 或 Answer 类类型时，我们还需要一个自定义转换器来处理您的情况。如果 Answer 是字符串，它将设置为 null 并进行相应处理。

一个工作示例:https://dotnetfiddle.net/yB1fIf

using System;
using System.Collections.Generic;
using Newtonsoft.Json;
                    
public class Program
{
    public static void Main()
    {
        var myString=@" "; //omitted for breveity 
        var myDeserializedClass = JsonConvert.DeserializeObject<dynamic>(myString);
        Root myDeserializedClass1 = JsonConvert.DeserializeObject<Root>(myDeserializedClass);
    
        foreach(var items in myDeserializedClass1.content)
        {
            foreach(var items1 in items.answers.Values)
            {
                Console.WriteLine(items1.name);
                //Console.WriteLine(items1.text);
            }
        } 
        
    }
}

public class AnswersData
{
    public string name { get; set; }
    public string order { get; set; }
    public string sublabels { get; set; }
    public string text { get; set; }
    public string type { get; set; }
    [JsonConverter(typeof(AnswerConverter))]
    public Answer answer { get; set; }
    public string prettyFormat { get; set; }
    public string selectedField { get; set; }
    public string full { get; set; }
}

public class Answer
{
    public string full { get; set; }
}


public class Content
{
    public string id { get; set; }
    public string form_id { get; set; }
    public string ip { get; set; }
    public string created_at { get; set; }
    public string status { get; set; }
    public string @new { get; set; }
    public string flag { get; set; }
    public string notes { get; set; }
    public object updated_at { get; set; }
    public Dictionary<string,AnswersData> answers { get; set; }
}

public class ResultSet
{
    public int offset { get; set; }
    public int limit { get; set; }
    public int count { get; set; }
}

public class Root
{
    public int responseCode { get; set; }
    public string message { get; set; }
    public List<Content> content { get; set; }
    public string duration { get; set; }
    public ResultSet resultSet { get; set; }
}

public class AnswerConverter : JsonConverter<Answer>
{
    public override Answer ReadJson(JsonReader reader, Type objectType, Answer existingValue, bool hasExistingValue, JsonSerializer serializer)
    {
        switch (reader.MoveToContentAndAssert().TokenType)
        {
            case JsonToken.Null:
            case JsonToken.String:
                return null;

            default:
                var alarm = hasExistingValue ? existingValue : (Answer)serializer.ContractResolver.ResolveContract(objectType).DefaultCreator();
                serializer.Populate(reader, alarm);
                return alarm;
        }
    }

    public override bool CanWrite => false;

    public override void WriteJson(JsonWriter writer, Answer value, JsonSerializer serializer) => throw new NotImplementedException();
}

public static partial class JsonExtensions
{
    public static JsonReader MoveToContentAndAssert(this JsonReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException();
        if (reader.TokenType == JsonToken.None)       // Skip past beginning of stream.
            reader.ReadAndAssert();
        while (reader.TokenType == JsonToken.Comment) // Skip past comments.
            reader.ReadAndAssert();
        return reader;
    }

    public static JsonReader ReadAndAssert(this JsonReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException();
        if (!reader.Read())
            throw new JsonReaderException("Unexpected end of JSON stream.");
        return reader;
    }
}

输出:

standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID