I have JSON file in Azure Storage which I am reading using C#. In that JSON file there is anode called SQLViewDifinition and that node I have SQL which I need to fetch.

I have read the file into a string and converted that string in JObject. I have the JSON now but is finding it difficult to read that particular node. Tried with JToken and Jproperty. But could not crack it.

JSON file looks like this:

{
  "jsonSchemaSemanticVersion": "1.4.0",
  "imports": [
    {
      "corpusPath": "cdm:/foundations.cdm.json"
    },
    {
      "corpusPath": "localCdm:/foundations.cdm.json"
    }
  ],
  "definitions": [
    {
      "entityName": "METCredManCollectionGroupEntity",
      "exhibitsTraits": [
        {
          "traitReference": "is.CDM.entityVersion",
          "arguments": [
            {
              "name": "versionNumber",
              "value": "1.0.0"
            }
          ]
        },
        {
          "traitReference": "has.sqlViewDefinition",
          "arguments": [
            {
              "name": "sqlViewDefinition",
              "value": "CREATE VIEW [DBO].[METCREDMANCOLLECTIONGROUPENTITY] AS SELECT T1.COLLECTIONGROUPID AS COLLECTIONGROUPID, T1.DESCRIPTION AS DESCRIPTION, T1.RECID AS CREDMANCOLLECTIONGROUPTABLERECID, T1.DATAAREAID AS CREDMANCOLLECTIONGROUPTABLEDATAAREAID, T1.RECVERSION AS RECVERSION, T1.PARTITION AS PARTITION, T1.RECID AS RECID FROM CREDMANCOLLECTIONGROUPTABLE T1"
            }
          ]
        },
        {
          "traitReference": "has.backingElements",
          "arguments": [
            {
              "name": "backingElements",
              "value": "CredManCollectionGroupTable"
            }
          ]
        }
      ],
      "hasAttributes": [
        {
          "name": "CollectionGroupId",
          "dataType": "CredManCollectionGroupId",
          "isNullable": true,
          "displayName": "Collection group",
          "maximumLength": 10
        },
        {
          "name": "Description",
          "dataType": "Description",
          "isNullable": true,
          "displayName": "Description",
          "maximumLength": 60
        },
        {
          "name": "CredmanCollectionGroupTableRecId",
          "dataType": "other",
          "isNullable": true,
          "displayName": "Record-ID"
        },
        {
          "name": "CredmanCollectionGroupTableDataAreaId",
          "dataType": "other",
          "isNullable": true,
          "displayName": "Company"
        }
      ],
      "displayName": "MET Collection groups (Shared)"
    },
    {
      "explanation": "Collection group",
      "dataTypeName": "CredManCollectionGroupId",
      "extendsDataType": "SysGroup"
    },
    {
      "explanation": "Group",
      "dataTypeName": "SysGroup",
      "extendsDataType": "string"
    },
    {
      "explanation": "Description",
      "dataTypeName": "Description",
      "extendsDataType": "string"
    }
  ]
}

I need to find sqlViewDefinition from this file.

So far I can read the JSON in a JSON object. But could not find a way to get the view definition.

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Nancy.Json;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

public class FindFiles
{


    // Main Method with int return type
    static int Main(String[] args)
    {

        Console.WriteLine("Buid SQL");

        // for successful execution of code
        return X("FILE_NAME");
    }

    public static int X(string fileName)
    {
        //connection string
        string storageAccount_connectionString = "CONNECTION_STRING";

        // Retrieve storage account from connection string.
        CloudStorageAccount storageAccount = CloudStorageAccount.Parse(storageAccount_connectionString);

        // Create the blob client.
        CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

        // Retrieve reference to a previously created container.
        CloudBlobContainer container = blobClient.GetContainerReference("CONTAINER");
        //The specified container does not exist

        try
        {
            //root directory
            CloudBlobDirectory dira = container.GetDirectoryReference(string.Empty);
            //true for all sub directories else false 
            var rootDirFolders = dira.ListBlobsSegmentedAsync(true, BlobListingDetails.Metadata, null, null, null, null).Result;

            foreach (var blob in rootDirFolders.Results)
            {
                if (blob.Uri.OriginalString.Contains(fileName, StringComparison.OrdinalIgnoreCase) && blob.Uri.OriginalString.Contains(".cdm.json", StringComparison.OrdinalIgnoreCase))
                {
                    Console.WriteLine("Blob: " + blob.Uri.OriginalString);
                    
                    if (blob.GetType() == typeof(CloudBlockBlob))
                    {
                        CloudBlockBlob b = (CloudBlockBlob)blob;
                        string jsonText = b.DownloadTextAsync().Result;
                        Dictionary<string, object> json_Dictionary = (new JavaScriptSerializer()).Deserialize<Dictionary<string, object>>(jsonText.ToString());
                        JObject json = JObject.Parse(jsonText);
                    }
                }
            }

        }
        catch (Exception e)
        {
            //  Block of code to handle errors
            Console.WriteLine("Error", e);

        }

        return 1;
    }
}

>Solution :

As you are using .NET 6 and because the structure is always the same, the easiest way to deserialize is to mimic the structure of the JSON in C# classes. You can then easily deserialize the JSON into objects and access the properties of the objects instead of "brachiating" through dynamic data.

In order to get the classes, you can use Visual Studio’s Paste Special function (Edit -> Paste special -> Paste JSON as classes). This generates the classes for you (you can adjust the classes if you don’t need parts of them or change the casing of the property names.

Afterwards, it is easy to parse the JSON into an object, e.g. (I’ve put your sample JSON into the jsonContent variable):

var obj = System.Text.Json.JsonSerializer.Deserialize<Rootobject>(jsonContent);

Because it still is a complex structure, getting to the SQL needs a bit of code:

Console.WriteLine(obj
  .definitions[0]
  .exhibitsTraits
  .Where(x => x.traitReference == "has.sqlViewDefinition")
  .First().arguments.Where(x => x.name == "sqlViewDefinition")
  .First().value);

Finally, the above code writes the following output:

CREATE VIEW [DBO].[METCREDMANCOLLECTIONGROUPENTITY] AS SELECT T1.COLLECTIONGROUPID AS COLLECTIONGROUPID, T1.DESCRIPTION AS DESCRIPTION, T1.RECID AS CREDMANCOLLECTIONGROUPTABLERECID, T1.DATAAREAID AS CREDMANCOLLECTIONGROUPTABLEDATAAREAID, T1.RECVERSION AS RECVERSION, T1.PARTITION AS PARTITION, T1.RECID AS RECID FROM CREDMANCOLLECTIONGROUPTABLE T1