How to query this nested JSON using C# ?

Hi,

var filter = Builders.Filter.ElemMatch("prod1", Builders.Filter.Eq("wre.name", "wrt"));

inside the prod1 - "wre" Key is not same every time. each time it will change.

"wre" is dynamic here How to form query ? 

To query the nested JSON using System.Text.Json, which is the built-in JSON parser in .NET, you can take advantage of its JsonDocument and JsonArray classes for efficient parsing and querying. Here's an approach using System.Text.Json.

Steps:

1. Install System.Text.Json: It's part of the .NET Core/Framework SDK, so no need for extra installations if you're using .NET 5 or later.

2. Use JsonDocument to parse and query the JSON.

Code Example Using System.Text.Json:

using System;
using System.Text.Json;

class Program
{
    static void Main(string[] args)
    {
        // Sample JSON string
        string json = @"[{
                          ""_id"": { ""$oid"": ""65af63b9b90f28b3176dbae8"" },
                          ""Company Name"": ""ABCD"",
                          ""Location"": ""Mysore"",
                          ""NoOfEmployee"": ""300"",
                          ""fixlevel"": 10,
                          ""create"": ""1"",  
                          ""prod1"": [
                            { ""wre"": { ""id"": ""wre"", ""name"": ""wrt"", ""address"": ""345 435435vr"", ""pincode"": ""535345"" }},
                            { ""1q"": { ""id"": ""1q"", ""name"": ""re"", ""address"": ""ewrwer"", ""pincode"": ""213414"" }}
                          ],
                          ""prod2"": [
                            { ""22"": { ""id"": ""22"", ""name"": ""423"", ""address"": ""erw"", ""pincode"": ""324234"", ""linkstring"": ""prod1.wre"", ""linkvalue"": ""wre"" }},
                            { ""223"": { ""id"": ""223"", ""name"": ""sdfsdf"", ""meterid"": ""erwer"", ""address"": ""fgsdgf"", ""pincode"": ""321114"", ""linkstring"": ""prod1.1q"", ""linkvalue"": ""1q"" }}
                          ]
                        }]";

        // Parse JSON string to JsonDocument
        using (JsonDocument doc = JsonDocument.Parse(json))
        {
            // Access the first element of the array (assuming one object in the array)
            JsonObject company = doc.RootElement[0].AsObject();

            // Access company details
            string companyName = company.GetProperty("Company Name").GetString();
            string location = company.GetProperty("Location").GetString();
            string noOfEmployee = company.GetProperty("NoOfEmployee").GetString();

            Console.WriteLine($"Company Name: {companyName}");
            Console.WriteLine($"Location: {location}");
            Console.WriteLine($"No of Employees: {noOfEmployee}");

            // Querying prod1
            JsonArray prod1 = company.GetProperty("prod1").EnumerateArray();
            foreach (var item in prod1)
            {
                if (item.TryGetProperty("wre", out JsonElement wre))
                {
                    Console.WriteLine($"Prod1 wre - ID: {wre.GetProperty("id").GetString()}, Name: {wre.GetProperty("name").GetString()}");
                }
                if (item.TryGetProperty("1q", out JsonElement q1))
                {
                    Console.WriteLine($"Prod1 1q - ID: {q1.GetProperty("id").GetString()}, Name: {q1.GetProperty("name").GetString()}");
                }
            }

            // Querying prod2
            JsonArray prod2 = company.GetProperty("prod2").EnumerateArray();
            foreach (var item in prod2)
            {
                if (item.TryGetProperty("22", out JsonElement prod22))
                {
                    Console.WriteLine($"Prod2 22 - ID: {prod22.GetProperty("id").GetString()}, Name: {prod22.GetProperty("name").GetString()}");
                }
                if (item.TryGetProperty("223", out JsonElement prod223))
                {
                    Console.WriteLine($"Prod2 223 - ID: {prod223.GetProperty("id").GetString()}, Name: {prod223.GetProperty("name").GetString()}");
                }
            }
        }
    }
}

Explanation:

1. JsonDocument.Parse: Parses the JSON string into a JsonDocument, which is used for querying and manipulating JSON in a read-only way.
2. EnumerateArray(): Converts a JsonArray element into an enumerator so you can loop through its elements.
3. GetProperty(): Extracts specific properties from JSON objects, e.g., company.GetProperty("Company Name").GetString().
4. TryGetProperty(): Used for checking and extracting properties from nested JSON objects like prod1 or prod2.

Output:

Company Name: ABCD
Location: Mysore
No of Employees: 300
Prod1 wre - ID: wre, Name: wrt
Prod1 1q - ID: 1q, Name: re
Prod2 22 - ID: 22, Name: 423
Prod2 223 - ID: 223, Name: sdfsdf

Benefits of Using System.Text.Json:

• Performance: System.Text.Json is faster and uses less memory than Newtonsoft.Json.
• Built-in: It’s part of the .NET Core/Framework SDK, so no additional libraries are needed.
• Read-only API: It’s optimized for read-only operations, and uses a JsonDocument for parsing JSON in a non-modifying way.

Limitations:

• System.Text.Json doesn't have the same extensive features as Newtonsoft.Json (e.g., serialization/deserialization with custom converters), but for querying and accessing JSON, it provides a high-performance, built-in solution.

For more advanced querying or if you need to manipulate the JSON data, Newtonsoft.Json remains a flexible option, but System.Text.Json is a great choice for performance-critical scenarios or when working with built-in .NET features.

Hi Sangeetha S,

To query a nested JSON structure in MongoDB using C#, you can utilize the MongoDB C# driver, which provides a straightforward way to interact with your database. Given the provided JSON structure, you can access nested fields using the dot notation. Below is an example of how to query the prod1 array for a specific name:

using MongoDB.Bson;
using MongoDB.Driver;
using System;
using System.Threading.Tasks;

public class Program
{
    public static async Task Main(string[] args)
    {
        var client = new MongoClient("your_connection_string");
        var database = client.GetDatabase("your_database_name");
        var collection = database.GetCollection("your_collection_name");

        var filter = Builders.Filter.ElemMatch("prod1", 
            Builders.Filter.Eq("wre.name", "wrt"));

        var result = await collection.Find(filter).ToListAsync();

        foreach (var document in result)
        {
            Console.WriteLine(document.ToString());
        }
    }
}

We create a filter to match documents where the name field in the prod1 array's wre object equals "wrt". This approach allows you to efficiently retrieve nested data from your MongoDB collection.