Azure AI: Using Microsoft Azure AI to Query Large JSON files to Retrieve Data

-

Artificial Intelligence (AI) is very important feature that every software application needs to provide an interactive interaction to the end-users. Nowadays, most of the software applications rely on the AI capabilities to provide conversational features to end-users, where these end-users ask questions to the applications and application generates accurate results based on the questions asked. In such cases, software applications need to have data that is arranged in the form of Tables, Documents, JSON files, etc. If we have data on PDF documents form, then we can use Vector embeddings to query to documents. Similarly, in case of JSON files, the chunking is required to query the JSON documents. I have already published articles on RAG Applications on PDF documents and on SQL Database on following links:

Azure AI Building RAG Application Solution: Using Azure Open AI to generate embedding on PDF documents and executing vector quires for RAG Applicaiton

Azure AI Building RAG Application Solution: Using Azure AI Search Service and Creating Data Source, Index, and Indexer

Semantic Kernel: Creating Plugins to access the business data

Azure Open AI: Generating SQL Statement from the Prompt and executing it

In this article, we will create a simple Console Application that will use Azure AI GPT Deployment to query to JSON documents. The code provided at the end of this article has the JSON data files.

Why JSON Files?

JSON (JavaScript Object Notation) files are widely used because they strike a beautiful balance between simplicity, readability, and versatility. JSON files are popular because of the following features:

  • Human-Readable
  • Language-Agnostic
  • Structured-Data
  • Web-Friendly
  • Easy-to-Parse
  • Interoperability
Som if we have data in JSON files, instead of storing these files in SQL Server or any other data store, we can directly use Natural Language like query to retrieve data from JSON documents.  

To implement this application, we need to create Azure Open AI Service and GPT 4.1 deployment. I have already published videos on these topics. Linke for these vides are as follows:

 Azure AI Search With Azure Open AI and Import and Vectorize Data

The implementation of the JSON File query application with Azure Open AI is explained in Figure 1.


Step 1: Open Visual Studio 2022, create a new Console Application targeting to .NET 9. Name this project as GPT_TO_JSON. In this project add following NuGet packages.

  •  Azure.AI.OpenAI
  • Azure.Identity

Step 2: In this project, add a new folder named jsonfiles. In this folder add a JSON files. (At the bottom of this article, I have provided link for the project code repository. In this repository, you will find JSON files.)

Step 3: In the project, add a new folder named Services. In this folder, add a new class file named GptWithJson.cs. In this class file we will add the code for the GptWithJson class. The code for this class is shown in Listing 1.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Text.Json.Nodes;
using System.Threading.Tasks;

namespace CS_JSON_TO_HTML.Services
{
    internal class GptWithJson
    {

       
        string apiKey = "[THE-AZURE-OPEN-AI-SERVICE]"; // Replace with your actual key
        string jsonArray; // Load JSON from file
        int chunkSize = 100;
       

        public GptWithJson()
        {
            string curDir = Directory.GetCurrentDirectory();

            string projectDirectory = Directory.GetParent(curDir)?.Parent?.Parent?.ToString();


            string jsonFilePath = Path.Combine(projectDirectory, "jsonfiles", "complex_company_data.json");
              jsonArray = File.ReadAllText(jsonFilePath); // Load JSON from file
        }

        public async Task<string> Run(string prompt)
        {
            string result = string.Empty;
            var chunks = SplitJsonIntoChunks(jsonArray, chunkSize);
            double grandTotal = 0;

            foreach (var chunk in chunks)
            {
                //string systemPrompt = $"Given this JSON array of customer orders:\n{chunk}\nCalculate the total sum of 'amount' for customerId = {targetCustomerId}. Only return the number.";

                string systemPrompt = $"Given this JSON array as:\n{chunk}\n. Use this data to provide me result based on my question as: {prompt}.";

                  result = await QueryGptAsync(apiKey, systemPrompt);

              
            return result;
        }

       

        static List<string> SplitJsonIntoChunks(string json, int chunkSize)
        {
            var chunks = new List<string>();
            var root = JsonNode.Parse(json);

            if (root is JsonArray array)
            {
                // Chunk array elements
                for (int i = 0; i < array.Count; i += chunkSize)
                {
                    var chunk = new JsonArray();
                    for (int j = i; j < i + chunkSize && j < array.Count; j++)
                    {
                        chunk.Add(JsonNode.Parse(array[j].ToJsonString()));
                    }
                    chunks.Add(chunk.ToJsonString(new JsonSerializerOptions { WriteIndented = false }));
                }
            }
            else if (root is JsonObject obj)
            {
                // Chunk object properties
                var properties = obj.ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
                var keys = properties.Keys.ToList();

                for (int i = 0; i < keys.Count; i += chunkSize)
                {
                    var chunk = new JsonObject();
                    for (int j = i; j < i + chunkSize && j < keys.Count; j++)
                    {
                        var key = keys[j];
                        chunk[key] = JsonNode.Parse(properties[key].ToJsonString());
                    }
                    chunks.Add(chunk.ToJsonString(new JsonSerializerOptions { WriteIndented = false }));
                }
            }
            else
            {
                // Treat as single value (string, number, bool, etc.)
                chunks.Add(root.ToJsonString(new JsonSerializerOptions { WriteIndented = false }));
            }

            return chunks;
        }


        static async Task<string> QueryGptAsync(string apiKey, string prompt)
        {
            using var client = new HttpClient();
            client.DefaultRequestHeaders.Add("Authorization", $"Bearer {apiKey}");

            var requestBody = new
            {
                model = "gpt-4.1",
                messages = new[]
                {
                new { role = "user", content = prompt }
            },
                temperature = 0.2
            };

            var content = new StringContent(JsonSerializer.Serialize(requestBody), Encoding.UTF8, "application/json");
            var response = await client.PostAsync("[THE-AZURE-OPEN-AI-ENDPOINT]", content);
            var responseBody = await response.Content.ReadAsStringAsync();

            try
            {
                using var doc = JsonDocument.Parse(responseBody);
                return doc.RootElement
                          .GetProperty("choices")[0]
                          .GetProperty("message")
                          .GetProperty("content")
                          .GetString()
                          ?.Trim() ?? "0";
            }
            catch
            {
                return "0";
            }
        }
    }
}

Listing 1: The GptWithJosn class

The code in Listing 1 has the following specifications:

  • SplitJsonIntoChunks(): 
    • This method is used to chunk the JSON file for better retrieval. The chunk size used here is 100.
    • The JSON document may have all records in JSON form or a single JSON object. This method contains logic to chunk the JSON file irrespective of it.   
    • For the large files, it is important to implement the chunking of the contents form the files. This is important to break large documents into smaller, semantically meaningful segments. This is a critical optimization in Retrieval-Augmented Generation (RAG) systems.    
    • The goal is to retrieve the most relevant information from a knowledge base and feed it into a language model.
    • Chunking has the following advantages:
      • Respecting Context Window Limits: The Language models have strict token limits. Chunking ensures that each segment fits within those limits, this helps to prevent truncation or overload.
      • Improving Retrieval Precision: Smaller and well-structured chunks allow the system to retrieve the most relevant pieces of information from the source document instead of rather than entire documents or irrelevant sections.
      • Reducing Computational Overhead: The efficient chunking helps to minimizes the amount of data processed during the retrieval and generation, that leads to faster and more scalable systems.
      • Enhancing Semantic Relevance: When chunks are created based on semantic boundaries, the retrieved content is more likely to be contextually aligned with the query.
      • Boosting Answer Accuracy: With better and targeted retrieval, the language model receives cleaner and most relevant input, which leads to more accurate and meaningful responses.
  • QueryGptAsync();
    • This method accepts two string parameters, the first is Microsoft Azure Open API key and second is the end-user prompt (the question) received from the end-user.
    • This methos uses the HttpClient object to make a HTTP POST request to the Microsoft Azure Open AI REST Endpoint. 
    • The method creates a HTTP header that uses the Microsoft Azure Open AI Key to authenticate the client application to access the Microsoft Azure Open AI REST Endpoint.
    • The method further creates a HTTP POST request body that contains, gpt-4.1 model, message having the prompt, and temperature.
    • The HttpContent is create using the request body so that HTTP Post request is made.
    • Once the request is over the response is collected from which the response contents are read.
  • Run():
    • This method accepts the prompt entered by the end-user. This method accesses the SplitJsonIntoChunks() method to get chunk from the JSON document and then using this chunk the final system prompt is generated that is passed to the QueryGptAsync() methos to get the result.
  • The constructor of the class reads the JSON file and store its contents in jsonArray variable from which the chunks are created.
Step 4: Modify the code of the Program.cs file as shown in Listing 2.


var canContinue = "y";
do
 {
   GptWithJson gpt = new GptWithJson();
   Console.WriteLine("Enter the Prompt");
   string prompt = Console.ReadLine();
   string gptResult = await gpt.Run(prompt);
   Console.WriteLine($"GPT Result: {gptResult}");
   Console.WriteLine("Do you want to continue? (y/n)");
   canContinue = Console.ReadLine();

 } while (canContinue == "y");
 Console.ReadLine();
Listing 2: The Program.cs

The following is a JSON sample data:
 "company": {
    "name": "TechNova Solutions",
    "location": {
      "city": "Pune",
      "state": "Maharashtra",
      "country": "India"
    },
    "departments": [
      {
        "id": "D1000",
        "name": "Engineering",
        "manager": {
          "id": "E1000",
          "name": "Sushrut Verma",
          "email": "sushrut.verma@technova.com"
        },
        "teams": [
          {
            "teamId": "T1000",
            "teamName": "Backend Development",
            "members": [
              {
                "employeeId": "E2000",
                "name": "Sushrut Verma",
                "role": "Senior Technical Trainer",
                "skills": [
                  "Java",
                  "Kubernetes",
                  "Python",
                  "REST APIs"
                ],
................................................

Run the application and enter the prompt:

The Result is shown in Figure 2.



Figure 2: The Result

The code for this article can be downloaded from the this link.

Conclusion: The JSON data files are structured file for data storage. To query to this JSON files, we can use Microsoft Azure Open AI to query these files to use Natural English Language query to access the data from it. 
  
 
              


  

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more

ASP.NET Core 8: Creating Custom Authentication Handler for Authenticating Users for the Minimal APIs

-
Image
The ASP.NET Core is the superb technology for building modern web apps. One of the best features of ASP.ET Core is the ease of security implementation and its customization as per the application requirements. The Identity Object model of ASP.NET Core provides extensive customization to the developers so that they can implement the security logic as per the application's requirements where the Identity can be verified against the On-Premises and/or Cloud stores where the Users' credentials are kept.  In this article we will see an implementation of the custom Authentication Handler to handle the authorization of the request to the Minimal APIs. The Figure 1 provides an idea of the implementation Figure 1: The Authentication Handlers in ASP.NET Core Minimal APIs To implement custom authentication handler, we will be using the following classes: AuthenticationSchemeOptions:   This class is used to set the scheme for the authentication calls. The request to the API will be f...
Read more