How to query this nested JSON using C# ?

Question

Hi How to query this nested 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 } ] }]

Amit Mohanty · Answer

Try this once: var client = new MongoClient(connectionString); var database = client.GetDatabase(yourDatabaseName); var collection = database.GetCollection (yourCollectionName); var documents = collection.Find(FilterDefinition .Empty).ToList(); foreach (var document in documents) { Console.WriteLine($Company Name: {document[Company Name]}); Console.WriteLine($Location: {document[Location]}); var prod1Array = document[prod1].AsBsonArray; foreach (var item in prod1Array) { var prod1Obj = item.AsBsonDocument; foreach (var key in prod1Obj.Names) { var details = prod1Obj[key].AsBsonDocument; Console.WriteLine($Prod1 Key: {key} Name: {details[name]}); } } var prod2Obj = document[prod2].AsBsonDocument; foreach (var key in prod2Obj.Names) { var details = prod2Obj[key].AsBsonDocument; Console.WriteLine($Prod2 Key: {key} LinkString: {details[linkstring]}); } }

Sangeetha S · Answer

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 ?

Jaish Mathews · Answer

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: 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. 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: JsonDocument.Parse : Parses the JSON string into a JsonDocument  which is used for querying and manipulating JSON in a read-only way. EnumerateArray() : Converts a JsonArray element into an enumerator so you can loop through its elements. GetProperty() : Extracts specific properties from JSON objects e.g. company.GetProperty(Company Name).GetString() . 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.

Jayraj Chhaya · Answer

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.