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
Imports Newtonsoft.Json
Imports System.Collections.Generic
Imports System.Dynamic
Imports System.Linq
Namespace ConsoleApplication1
Class Program
Private Shared Sub Main(args As String())
Dim empInformation = GetEmpInformation()
Dim deptInformation = GetDeptInformation()
'join between EmpInformation and DeptInformation
Dim recordSet = (From emp In empInformationJoin dept In deptInformation On DirectCast(DirectCast(emp, IDictionary(Of String, Object))("DeptId"), String) = DirectCast(DirectCast(dept, IDictionary(Of String, Object))("DeptId"), String)New With { _
Key .EmployeeName = DirectCast(DirectCast(emp, IDictionary(Of String, Object))("EmployeeName"), String), _
Key .DepartmentId = DirectCast(DirectCast(emp, IDictionary(Of String, Object))("DeptId"), String), _
Key .DepartmentName = DirectCast(DirectCast(dept, IDictionary(Of String, Object))("DepartmentName"), String) _
})
'print the result
recordSet.ToList().ForEach(Function(i) Console.WriteLine("Employee {0} belongs to Department {1}", i.EmployeeName, i.DepartmentName))
Console.ReadKey()
End Sub
Private Shared Function GetEmpInformation() As List(Of ExpandoObject)
Try
Dim empObj = New List(Of ExpandoObject)()
Dim source = "{'Employees': {'Employee': [{'DeptId': '111','EmployeeName': 'Niladri'},{'DeptId': '222','EmployeeName': 'Arina'},{'DeptId': '111','EmployeeName': 'Babai'},{'DeptId': '222','EmployeeName': 'RNA Team'}]}}"
Dim employeeInfo As dynamic = JsonConvert.DeserializeObject(source)
Dim countRecord = employeeInfo.Employees.Employee.Count
For i As var = 0 To countRecord - 1
Dim expandoObj As dynamic = New ExpandoObject()
expandoObj.DeptId = Convert.ToString(employeeInfo.Employees.Employee(i).DeptId)
expandoObj.EmployeeName = Convert.ToString(employeeInfo.Employees.Employee(i).EmployeeName)
empObj.Add(expandoObj)
Next
Return empObj
Catch ex As Exception
Throw ex
End Try
End Function
Private Shared Function GetDeptInformation() As List(Of ExpandoObject)
Try
Dim deptObj = New List(Of ExpandoObject)()
Dim source = "{'Departments': {'Department': [{'DepartmentName': 'HR','Deptid': '111'},{'DepartmentName': 'Finance','Deptid': '222' }]}}"
Dim departmentInfo As dynamic = JsonConvert.DeserializeObject(source)
Dim countRecord = departmentInfo.Departments.Department.Count
For i As var = 0 To countRecord - 1
Dim expandoObj As dynamic = New ExpandoObject()
expandoObj.DeptId = Convert.ToString(departmentInfo.Departments.Department(i).Deptid)
expandoObj.DepartmentName = Convert.ToString(departmentInfo.Departments.Department(i).DepartmentName)
deptObj.Add(expandoObj)
Next
Return deptObj
Catch ex As Exception
Throw ex
End Try
End Function
End Class
End Namespace
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.