Comparing values of JSON tags to database values

Help Studio
1

Hi, I have a JSON after deserialization. Now I have to compare multiple tag values in JSON with values fetched from SQL database. How can we do with minimum number of activities. I have created array of values fetched from database but while iterating with for or if the code is becoming long and complex.
If there is any other way(maybe data table etc), please suggest that too.

Thanks in advance!!!

1 Like
2

Do you have 2 arrays and you are trying to find if the each value in 1 array exists in the other array?

1 Like
3

I have one JSON object array(JArray) and other a normal array

1 Like
4

Are you using the Array.Contains method?

1 Like
5

I am using multiple ifs comparing them with array elements like below iterating for each item in Json Array:

(item(“employeeID”).ToString<>emp_ID) then goto next if

6

With shared sample data we can faster help

7

I have a deserialized JSON array as below:
“empDetails”: [ {
“name”: “MARK”,
“empID”: “687014”,
“deptAssigned”: “Chemistry”
}]

And I have above details fetched from my OLTP database in form of DataTable variable. Now I need to compare all the values from above JSON(like MARK, 687014 etc) to values fetched from Database. What all values are matching and what are not. Need to capture what values are not matching and fail the scenario.

I have around 30 such values in JSON.

8

We do feel that we can approach within a compact way. But from description we cannot derive all needed details. (e.g. 1 Json, with 30 emps, 30 json with 1 emp, the datatable from OLTP, …)

Also

row-wise check, colum-check, how to mark the non matchers…

Have a look here for some starter helps:

grafik

the OLTP DataTable:
grafik

Checks with Set Operations:
grafik

And still, we do have more other options in place

9

Sorry for not mentioning this but data will be as below.
1 JSON will have 1 employee details with 30 different attributes for that 1 employee.
Same is the case with DB values datatable. Values fetched will have details of 1 employee with those 30 attributes values fetched from database.

Meanwhile I will try above approach suggested.

10

Perfect so comparing 1 Json with one Datatable row and detecting the non-matching values we can also do:

Build DataTable - 3 Cols Key, JSON, OLTP - dtReport
grafik

Parse the JSON within a JObject - myJObject
grafik

Assign Activity:
dtReport =

(From p In myJObject("empDetails").First().Value(Of JObject).Properties
Let jv = p.Value.Value(Of String).Trim
Let ov = dtOLTP.Rows(0)(p.Name).toString.Trim
Where Not jv.Equals(ov)
Let ra = New Object(){p.Name, jv,ov}
Select r = dtReport.Rows.Add(ra)).CopyToDataTable

grafik

Handle empty results:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

And also we can adapt and decompose the LINQ into essential activities

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.