Restructure / Pivot a list of attribute - value pairs to dynamic column headers in a table

Question

We are using: Azure Logic Apps for the webhook,

Azure RazorPages Web App Net6, with EFCore 6 for the CRUD

Google Ads Lead Forms returns submissions via Webhook, with the Column Name/Question text as an attribute named column_id and submission value in an attribute named string

    "user_column_data": [
        {
            "string_value": "FirstName LastName",
            "column_id": "FULL_NAME"
        },
        {
            "string_value": "[email protected]",
            "column_id": "EMAIL"
        },
        {
            "string_value": "mazda",
            "column_id": "which_vehicle_make_are_you_interested_in?"
        },
        {
            "string_value": "bicycle",
            "column_id": "what_kind_of_vehicle_do_you_have?"
        }
    ]

We intend to push the responses into a table in SQL Server.

public class Lead
{
    public int LeadId { get; set; }
    public List<LeadQuestion> LeadQuestions { get; set; }
}

public class LeadQuestion
{
    public int LeadQuestionId { get; set; }
    public string Question_text { get; set; }
    public string Answer { get; set; }
}

CREATE TABLE Leads
(
    LeadId INT IDENTITY(1, 1) PRIMARY KEY,
    FULL_NAME NVARCHAR(100) NOT NULL
);

CREATE TABLE LeadQuestions
(
    LeadQuestionId INT IDENTITY(1, 1) PRIMARY KEY,
    LeadId INT /* Foreign Key */ NOT NULL,
    Question_Text VARCHAR(50) NOT NULL,
    Answer VARCHAR(100) NOT NULL
);

We want to display the data on a RazorPage with the question text as a table header and the answers as values in those columns.

Rather than this:

Question_Text	Answer
Full_Name	FirstName LastName
which_vehicle_make_are_you_interested_in	mazda
what_kind_of_vehicle_do_you_have	bicycle

We want to achieve this:

Full_Name	which_vehicle_make_are_you_interested_in	what_kind_of_vehicle_do_you_have
FirstName LastName	mazda	bicycle

How do we structure a Linq query to dynamically pivot the data?

Answer 1

I've found a few methods to convert the array of key/value pairs into the required object.

var json = @"
{
    ""user_column_data"": [
        {
            ""string_value"": ""FirstName LastName"",
            ""column_id"": ""FULL_NAME""
        },
        {
            ""string_value"": ""[email protected]"",
            ""column_id"": ""EMAIL""
        },
        {
            ""string_value"": ""mazda"",
            ""column_id"": ""which_vehicle_make_are_you_interested_in?""
        },
        {
            ""string_value"": ""bicycle"",
            ""column_id"": ""what_kind_of_vehicle_do_you_have?""
        }
    ]
}";

Using a Dictionary allows for the most flexibility, as the Object properties may change, increase, or decrease.

    var userData = new Dictionary<string, object>();
    var jsonDoc = JsonDocument.Parse(json);
    var userColumnDataList = jsonDoc.RootElement.GetProperty("user_column_data").EnumerateArray();
    foreach (var userColumnData in userColumnDataList) {
        userData.Add(userColumnData.GetProperty("column_id").GetString(), userColumnData.GetProperty("string_value").GetString());
    }

Creates the new object when serialized:

{
 "FULL_NAME ": "FirstName LastName",
 "EMAIL": "[email protected]",
 "which_vehicle_make_are_you_interested_in?": "mazda",
 "what_kind_of_vehicle_do_you_have?": "bicycle"
}