c# - 使用 C# 从存储在 Azure 存储中的 JSON 文件获取值

Question

我在 Azure 存储中有 JSON 文件，我正在使用 C# 读取该文件。在该 JSON 文件中，有一个名为 SQLViewDifinition 的阳极，并且该节点有我需要获取的 SQL。

我已将文件读入字符串并在 JObject 中转换该字符串。我现在有了 JSON，但发现很难读取该特定节点。尝试使用 JToken 和 Jproperty。但无法破解。

JSON 文件如下所示:

{
  "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"
    }
  ]
}

我需要从此文件中找到 sqlViewDefinition。

到目前为止，我可以读取 JSON 对象中的 JSON。但找不到获取 View 定义的方法。

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;
    }
}

Answer 1

由于您使用的是 .NET 6 并且结构始终相同，因此反序列化的最简单方法是模仿 C# 类中的 JSON 结构。然后，您可以轻松地将 JSON 反序列化为对象并访问对象的属性，而不是通过动态数据“ fork ”。

为了获取类，您可以使用 Visual Studio 的选择性粘贴功能(编辑 -> 选择性粘贴 -> 将 JSON 粘贴为类)。这会为您生成类(如果您不需要类的一部分，则可以调整类或更改属性名称的大小写；您也可以使用属性来自定义序列化)。

之后，很容易将 JSON 解析为对象，例如(我已将示例 JSON 放入 jsonContent 变量中):

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

因为它仍然是一个复杂的结构，所以获取 SQL 需要一些代码:

Console.WriteLine(obj
  .definitions[0]
  .exhibitsTraits
  .Where(x => x.traitReference == "has.sqlViewDefinition")
  .First().arguments.Where(x => x.name == "sqlViewDefinition")
  .First().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

您可以使用这个documentation熟悉 .NET 6 中的 JSON 处理。