Hey Arvind, this is a common scenario when building dynamic API payloads from structured Excel data, and using Newtonsoft.Json (Json.NET) makes this really straightforward. Let's walk through a practical implementation that handles nested arrays and objects exactly as you need.

Core Approach #

The key idea is to:

1. Split each Excel path string (like books[].Categories[].Name) into individual segments using . as the delimiter.
2. Traverse from the root of your JSON object, creating nested objects or arrays as needed based on whether a segment ends with [].
3. For array segments ([]), we'll either create a new array (if it doesn't exist) or use the last element in the existing array to maintain the correct nested structure for matching paths.
4. Finally, set the Excel value to the target property at the end of the path.

Implementation Code #

First, make sure you have the Newtonsoft.Json NuGet package installed (Install-Package Newtonsoft.Json). Then create a helper class to handle the path parsing and JSON building:

using Newtonsoft.Json.Linq;

public static class DynamicJsonBuilder
{
    public static void AddPathValue(JObject root, string path, object value)
    {
        var segments = path.Split('.');
        JToken current = root;

        // Iterate through all segments except the last one (the target property)
        for (int i = 0; i < segments.Length - 1; i++)
        {
            var segment = segments[i];
            var isArray = segment.EndsWith("[]");
            var propName = segment.Replace("[]", "");

            if (isArray)
            {
                // Check if the current node has the array property
                JArray array = current[propName] as JArray;
                if (array == null)
                {
                    array = new JArray();
                    current[propName] = array;
                }

                // Use the last element in the array if it exists, else create a new object
                JObject nextObj;
                if (array.Count == 0)
                {
                    nextObj = new JObject();
                    array.Add(nextObj);
                }
                else
                {
                    nextObj = array.Last as JObject;
                }

                current = nextObj;
            }
            else
            {
                // Check if the current node has the object property
                JObject nextObj = current[propName] as JObject;
                if (nextObj == null)
                {
                    nextObj = new JObject();
                    current[propName] = nextObj;
                }

                current = nextObj;
            }
        }

        // Handle the final segment (set the value)
        var finalSegment = segments.Last();
        var finalPropName = finalSegment.Replace("[]", "");
        current[finalPropName] = JToken.FromObject(value);
    }
}

How to Use It #

Here's how you'd integrate this with your Excel data reading logic. Let's simulate your example where you have two paths and their corresponding values:

// Initialize the root JSON object
JObject rootJson = new JObject();

// Simulate reading from Excel: key = path string, value = cell value
var excelData = new Dictionary<string, object>
{
    { "books[].Categories[].Name", "Solo" },
    { "books[].Categories[].Type", "Drama" }
};

// Add each Excel entry to the JSON structure
foreach (var entry in excelData)
{
    DynamicJsonBuilder.AddPathValue(rootJson, entry.Key, entry.Value);
}

// Convert to formatted JSON string
string finalJson = rootJson.ToString(Newtonsoft.Json.Formatting.Indented);
Console.WriteLine(finalJson);

Output #

Running this code will produce exactly the JSON you're expecting:

{
  "books": [
    {
      "Categories": [
        {
          "Name": "Solo",
          "Type": "Drama"
        }
      ]
    }
  ]
}

Key Notes #

• Handling Multiple Array Entries: If you had multiple values for the same array path (e.g., books[].Name with "Book1" and "Book2"), you'd need to adjust the logic to create new array elements instead of reusing the last one. For your current use case though, reusing the last element works perfectly for matching nested paths like books[].Categories[].Name and books[].Categories[].Type.
• Type Safety: The example uses object for values, but you can add type checking/conversion if you need to enforce specific data types (e.g., numbers, booleans) from Excel.
• Alternative to Newtonsoft.Json: If you're using .NET Core 3.0+, you could use System.Text.Json with JsonElement and JsonDocument, but Newtonsoft.Json is generally more flexible for dynamic structure building.

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