How to Right Join two Json files in C#?

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.

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.

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.