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.

Answer 3

The following should work:

// 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.

Which results in:

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

Notes:

• I changed the query from a right join to a left join because it made the filtering look a little more natural. See LINQ Left Join And Right Join if you would prefer the right join syntax.

• The final select statements creates a new JObject with all the records from the JObject i item object then adds the CategoryDescription from the cat category object. It does not modify the existing object i.

• JContainer.Merge() isn't going to help you here because it doesn't have any ability to merge based on some primary key.

• ttObj.SelectTokens("Items[*]") uses the JSONPath wildcard operator [*] to select all items in the "Items" array.

• As there is no category with "CategoryID":"metals", cat is null in the ultimate select statement.

Demo fiddle here.