如何在C#中正确连接两个Json文件

Question

我想使用一个公共键连接两个json文件，并从右边的文件中获取所有记录，从左边获取匹配的数据。

如果是SQL。

SELECT json1.CategoryDescription, json2.CategoryID, json2.TechName, json2.SpawnID
FROM json1
RIGHT JOIN json2
ON json1.CategoryID = json2.CategoryID
WHERE GameVersion = "A" OR GameVersoion = "2" AND CategoryID = "metals"

我需要获得所有的json2记录和每个记录的json1.CategoryDescription。但目前它只列出了json1的所有记录，然后列出了json2的所有记录。

这是我目前的尝试：

using System;
using System.IO;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
namespace ConsoleApp1
{
public class Program
{
public static void Main()
{
// Filter variables
var gameVer = "2";
var cat = "metals";
// Load the categories.json
JObject catObj = JObject.Load(new JsonTextReader(File.OpenText("D:/Code/Tests/categories.json")));
// Load the techtype.json
JObject ttObj = JObject.Load(new JsonTextReader(File.OpenText("D:/Code/Tests/techtypes.json")));
// Read techtype.json into an array
var mergeSettings = new JsonMergeSettings
{
MergeArrayHandling = MergeArrayHandling.Union
};
catObj.Merge(ttObj, mergeSettings);
// Does not work,
/*
Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
at ConsoleApp1.Program.Main() in D:CodeTestsReadTechTypesReadTechTypesProgram.cs:line 30
*/
// (catObj.SelectToken("Categoris") as JArray).Merge(ttObj.SelectToken("TechType"), mergeSettings);
// Does not work, same error
//var mergedArray = catObj.SelectToken("Categoris") as JArray;
//string json = mergedArray.ToString();
Console.WriteLine(catObj);
}
}
}

左侧json

{
"Categories":[
{
"CategoryID":"baseupgrades",
"CategoryDescription":"Base Upgrades",
"IncludeCategory":true,
"GameVersion":"A"
},
{
"CategoryID":"batteries",
"CategoryDescription":"Batteries",
"IncludeCategory":true,
"GameVersion":"A"
},
{
"CategoryID":"blueprint",
"CategoryDescription":"Blueprint",
"IncludeCategory":false,
"GameVersion":"A"
}
// Other category values omitted
]
}

正确的json

{
"Items":[
{
"CategoryID":"crystalline",
"TechName":"Quartz",
"SpawnID":"quartz",
"TechID":1,
"GameVersion":"A"
},
{
"CategoryID":"metals",
"TechName":"Metal Salvage",
"SpawnID":"scrapmetal",
"TechID":2,
"GameVersion":"A"
},
{
"CategoryID":"outcrop",
"TechName":"Limestone Outcrop",
"SpawnID":"limestonechunk",
"TechID":4,
"GameVersion":"A"
}
// Other items omitted
]
}

有什么想法吗？

Answer 1

你可以试试这个

categoriesRoot = JsonConvert.DeserializeObject<CategoriesRoot>(categoriesJson);
itemsRoot = JsonConvert.DeserializeObject<ItemsRoot>(itemsJson);

var items = from cr in categoriesRoot.Categories
join ir in itemsRoot.Items on cr.CategoryID equals ir.CategoryID into irj
from ir in irj.DefaultIfEmpty()
where ( (cr.GameVersion == "A") || (cr.GameVersion == "2" && cr.CategoryID == "metals"))
select new { 
cr.CategoryDescription,
ir.CategoryID,
ir.TechName,
ir.SpawnID
};
var newItemsJson=JsonConvert.SerializeObject(items);

创建这些类之后

public class Item
{
public string CategoryID { get; set; }
public string TechName { get; set; }
public string SpawnID { get; set; }
public int TechID { get; set; }
public string GameVersion { get; set; }
}
public class ItemsRoot
{
public List<Item> Items { get; set; }
}

public class Category
{
public string CategoryID { get; set; }
public string CategoryDescription { get; set; }
public bool IncludeCategory { get; set; }
public string GameVersion { get; set; }
}
public class CategoriesRoot
{
public List<Category> Categories { get; set; }
}

输出将像这个

[
{"CategoryDescription":"Base Upgrades","CategoryID":"crystalline","TechName":"Quartz","SpawnID":"quartz"},
{"CategoryDescription":"Batteries","CategoryID":"metals","TechName":"Metal Salvage","SpawnID":"scrapmetal"}
]

顺便说一下，您的SQL查询中有一个错误

WHERE GameVersion = "A" OR GameVersoion = "2" AND CategoryID = "metals"

这是一个不明确的代码，因为两个查询中都有GameVersion和CategoryID。

Answer 2

以下内容应该有效：

// Filter variables
var gameVersions = new HashSet<string> { "A", "2" };
var categoryIDs = new HashSet<string> { "metals" };
// Left outer join on ttObj.  Select all Items[*] array items
var query = from i in ttObj.SelectTokens("Items[*]").OfType<JObject>()
// Filter on the game version and category ID
let categoryId = (string)i["CategoryID"]           
let gameVersion = (string)i["GameVersion"]
where categoryIDs.Contains(categoryId) && gameVersions.Contains(gameVersion)
// Join with "Categories[*]" on category ID
join c in catObj.SelectTokens("Categories[*]") on categoryId equals (string)c["CategoryID"] into joined
// DefaultIfEmpty makes this a left join
from cat in joined.DefaultIfEmpty()               
// Select all records of i and add the CategoryDescription from cat.
select new JObject(i.Properties()) { new JProperty("CategoryDescription", cat?["CategoryDescription"]) };
var results = query.ToList(); // Materialize the query into a list of results.

结果是：

[
{
"CategoryID": "metals",
"TechName": "Metal Salvage",
"SpawnID": "scrapmetal",
"TechID": 2,
"GameVersion": "A",
"CategoryDescription": null
}
]

注：

• 我将查询从右联接更改为左联接，因为它使过滤看起来更自然。如果您希望使用正确的联接语法，请参阅LINQ Left Join And Right Join。

• 最后的select语句使用JObject i项目对象中的所有记录创建一个新的JObject，然后添加cat类别对象中的CategoryDescription。它不修改现有对象i。

• JContainer.Merge()在这里对您没有帮助，因为它没有任何基于某个主键进行合并的能力。

• ttObj.SelectTokens("Items[*]")使用JSONPath通配符运算符[*]来选择"Items"数组中的所有项。

• 由于"CategoryID":"metals"没有类别，因此cat在最终的select语句中为null。

在这里演示小提琴。

Answer 3

问题是您正在合并"类别"；用"；项目"；列表和"；项目"；在catObj上不存在。

[我建议您转换类中的项(使用visualstudio，您可以将"特殊粘贴"作为JSON类(。]

您必须迭代第一个列表中的项，并与第二个列表中相应的元素合并，即成员与成员，而不是列表与列表。