How to Right Join two Json files in C#?

Question

I want to join two json files using a common key and get all the records from the right file and matching data from the left.

If it was 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"

I need to get all the json2 records and the json1.CategoryDescription for each of them. But at the moment it just lists all the records from json1 then all the records from json2.

Here is my current attempt:

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:\Code\Tests\ReadTechTypes\ReadTechTypes\Program.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);
        }
    }
}

The left 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
   ]
}

The right 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
   ]
}

Any ideas?

Answer 1

You can try this

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

after creating these classes

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

output will be like this

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

And by the way you have a bug in your SQL query

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

this is an ambiguous code, since there are GameVersion and CategoryID in both queries.

Answer 2

The problem is that you are merging the "Category" list with the "Items" list, and "Items" is not present on catObj.

[I suggest to you to convert the items in class (with visual studio you can do a "Special paste" as JSON class).]

You have to iterate over the items of the first list and merge with the corresponding element in the second list, member with member, not list with list.