Join JSON Arrays in C#

Introduction

In this article we will demonstrate the Joining of two JSON Arrays in C#. We will return the JSON arrays as an anonymous collection from functions using Expando Objects and then will perform the operation.

Let's start the experiment

Let's first open a Console Application and add reference to Json.NET

Let's say we have two JSON viz.

Employee JSON
--------------
{
  "Employees": {
    "Employee": [
      {
        "DeptId": "111",
        "EmployeeName": "Niladri"
      },
      {
        "DeptId": "222",
        "EmployeeName": "Arina"
      },
      {
        "DeptId": "111",
        "EmployeeName": "Babai"
      },
      {
        "DeptId": "222",
        "EmployeeName": "RNA Team"
      }
    ]
  }
}

and

Department JSON
----------------
{
  "Departments": {
    "Department": [
      {
        "DepartmentName": "HR",
        "Deptid": "111"
      },
      {
        "DepartmentName": "Finance",
        "Deptid": "222"
      }
    ]
  }
}

The objective is to join these two JSON and to find out which Employees belongs to which Department

To achieve this, we will add the below program

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var empInformation = GetEmpInformation();
            var deptInformation = GetDeptInformation();

            //join between  EmpInformation and DeptInformation       
            var recordSet = (
                                from emp in empInformation
                                join dept in deptInformation on
                                (string)((IDictionary<string, object>)emp)["DeptId"] equals
                                (string)((IDictionary<string, object>)dept)["DeptId"]

                                select new
                                {
                                    EmployeeName = (string)((IDictionary<string, object>)emp)["EmployeeName"],
                                    DepartmentId = (string)((IDictionary<string, object>)emp)["DeptId"],
                                    DepartmentName = (string)((IDictionary<string, object>)dept)["DepartmentName"]

                                });

            //print the result
            recordSet
                .ToList()
                .ForEach(i => Console.WriteLine("Employee {0} belongs to Department {1}", i.EmployeeName, i.DepartmentName));

            Console.ReadKey();
        }

        private static List<ExpandoObject> GetEmpInformation()
        {
            try
            {
                var empObj = new List<ExpandoObject>();
                var source = "{'Employees': {'Employee': [{'DeptId': '111','EmployeeName': 'Niladri'},{'DeptId': '222','EmployeeName': 'Arina'},{'DeptId': '111','EmployeeName': 'Babai'},{'DeptId': '222','EmployeeName': 'RNA Team'}]}}";
                dynamic employeeInfo = JsonConvert.DeserializeObject(source);

                var countRecord = employeeInfo.Employees.Employee.Count;

                for (var i = 0; i < countRecord; i++)
                {
                    dynamic expandoObj = new ExpandoObject();
                    expandoObj.DeptId = Convert.ToString(employeeInfo.Employees.Employee[i].DeptId);
                    expandoObj.EmployeeName = Convert.ToString(employeeInfo.Employees.Employee[i].EmployeeName);

                    empObj.Add(expandoObj);
                }

                return empObj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private static List<ExpandoObject> GetDeptInformation()
        {
            try
            {
                var deptObj = new List<ExpandoObject>();
                var source = "{'Departments': {'Department': [{'DepartmentName': 'HR','Deptid': '111'},{'DepartmentName': 'Finance','Deptid': '222' }]}}";
                dynamic departmentInfo =  JsonConvert.DeserializeObject(source);

                var countRecord = departmentInfo.Departments.Department.Count;

                for(var i=0;i< countRecord;i++)
                {
                    dynamic expandoObj = new ExpandoObject();
                    expandoObj.DeptId = Convert.ToString(departmentInfo.Departments.Department[i].Deptid);
                    expandoObj.DepartmentName = Convert.ToString(departmentInfo.Departments.Department[i].DepartmentName);

                    deptObj.Add(expandoObj);
                }

                return deptObj;               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

In the GetEmpInformation() method, we are converting the Employee Json string into an object by using the JsonConvert.DeserializeObject method, then looping through the record count and preparing the List<ExpandoObject>. The same follows for GetDeptInformation().

So now we have both the Employee and Department information (collections) at our hand.The last step is to fire a JOIN using LINQ to find out the matching record.

//join between  EmpInformation and DeptInformation       
var recordSet = (
                    from emp in empInformation
                    join dept in deptInformation on
                    (string)((IDictionary<string, object>)emp)["DeptId"] equals
                    (string)((IDictionary<string, object>)dept)["DeptId"]

                    select new
                    {
                        EmployeeName = (string)((IDictionary<string, object>)emp)["EmployeeName"],
                        DepartmentId = (string)((IDictionary<string, object>)emp)["DeptId"],
                        DepartmentName = (string)((IDictionary<string, object>)dept)["DepartmentName"]

                    });

ExpandoObject implements IDictionary<string, object> interface for maintaining its list of members.The rest is pure join condition of Linq.

Finally, we are displaying the result

//print the result
recordSet
    .ToList()
    .ForEach(i => Console.WriteLine("Employee {0} belongs to Department {1}", i.EmployeeName, i.DepartmentName));

The final output

References

Conclusion

In this article, we have learnt joining JSON Arrays in C#. We have also seen the use of Expando Objects in this junction. Hope this will be helpful. Thanks for reading. Zipped file attached.