Extracting Fields from JSON

Hi Team,

I need to extract below data from json.

response_1678962644070 (1).json (22.2 KB)
Auth no
Admit date
Servicing Facility Name
Auth Start Date
Auth End date
Status

Thank you in advance

Hi @yashnegi108

Read the JSON file & store it in a string. Convert the string to Json Object using Deserialize JSON activity, save the object in jsonObject variable.

Then you can easily obtain the data ike this:

authNo = jsonObject(“Auth no”).ToString
admitDate = jsonObject(“Admit date”).ToString
servicingFacilityName = jsonObject(“Servicing Facility Name”).ToString
authStartDate = jsonObject(“Auth Start Date”).ToString
authEndDate = jsonObject(“Auth End date”).ToString
status = jsonObject(“Status”).ToString

Hope this helps,
Best Regards.

Hii @yashnegi108 ,
Please find the attached workflow
JsonResponse.zip (1.6 KB)

Note : Please install package “UiPath.WebAPI.Activites”

According to your input your array is : “ipAuthorizations”
I have taken only the 0th index values that is :
JsonObject(“ipAuthorizations”)(0)(“authorizationNum”).ToString

if u want to retrive other values you can try by changing the Index to 1 etc
JsonObject(“ipAuthorizations”)(1)(“authorizationNum”).ToString

Hope this helps: :slightly_smiling_face:

4 Likes

Hi @yashnegi108,

Check this attached flow this will helps you to extract all the information from the JSON
ExtractJson.xaml (8.2 KB)

Thank you Jithesh for you answer. It works :slight_smile:
Could you please guide me if we have more dynamic data json string change according inputs like right now we have
*JsonObject(“ipAuthorizations”)(0) or JsonObject(“ipAuthorizations”)(1)(“authorizationNum”).ToString will change according to inputs.

How will we handle this.

Thank you for your supports

@yashnegi108
welcome to the forum

with deserialize JSON Activity we get back a JObject - myJObject

The returned JObject has under ipAuthorizations an array with 3 class objects
grafik

Retrieval Loop Approach ( via Index, or feeding to a for each)
Sample:
grafik

Retrieval to DataTable (longer one liner)

JArray.FromObject(myJObject("ipAuthorizations").Values(Of JObject).Select(Function (x) new JObject(x.Properties.Where(Function (p) arrExtract.Contains(p.Name))))).toObject(Of DataTable)

with:

 arrExtract
 string[6] { "authorizationNum", "admitDate", "servicingFacilityName", "authEndDate", "authStartDate", "status" }

We would recommend to combine both techniques and focus also on datatype specific value retrieval like for dates:
grafik

1 Like

Hii @yashnegi108
Please find the modified workflow

in this flow i have taken the array “ipAuthorizations” and iterated through all the indexes.
JsonResponse.zip (2.0 KB)

Hope this helps :slightly_smiling_face:

Hi Jithesh,
Thank you for response. I will not able to open xaml file getting below error. I tried change the package verison web UiPath.
Could not find member ‘File’ in type ‘http://schemas.uipath.com/workflow/activities:ReadTextFile’. Row: 100, Column: 24

[Debug] The restore inputs for ‘JsonResponse’ have not changed. No further actions are required to complete the restore.
03/18/2023 15:41:45 => [Info] Assets file has not changed. Skipping assets file writing. Path: C:\Users\ynegi\Desktop\JsonResponse.local\AllDependencies.json
03/18/2023 15:41:45 => [Debug] No-Op restore. The cache will not be updated. Path: C:\Users\ynegi\Desktop\JsonResponse.local\nuget.cache
03/18/2023 15:41:57 => [Error] File ‘JsonResponse.xaml’ - variable ‘NewArray’: Type ‘s:String’ (‘clr-namespace:System;assembly=System.Private.CoreLib’) could not be resolved and was automatically replaced with ‘UnknownType’
03/18/2023 15:41:57 => [Error] Could not find member ‘Settings’ in type ‘Error’. Row: 1, Column: 61
03/18/2023 15:41:57 => [Error] Could not find type ‘System.Collections.Generic.List(String)’ in assembly ‘System.Private.CoreLib’. Row: 3, Column: 6
03/18/2023 15:41:57 => [Error] Could not find type ‘System.Collections.Generic.List(AssemblyReference)’ in assembly ‘System.Private.CoreLib’. Row: 39, Column: 6
03/18/2023 15:41:57 => [Error] Could not find type ‘System.Collections.Generic.Dictionary(String,Object)’ in assembly ‘System.Private.CoreLib’. Row: 77, Column: 8
03/18/2023 15:41:57 => [Error] Could not find member ‘File’ in type ‘http://schemas.uipath.com/workflow/activities:ReadTextFile’. Row: 100, Column: 24
03/18/2023 15:41:57 => [Error] Could not find member ‘Settings’ in type ‘Error’. Row: 1, Column: 61
03/18/2023 15:41:57 => [Error] Could not find type ‘System.Collections.Generic.List(String)’ in assembly ‘System.Private.CoreLib’. Row: 3, Column: 6
03/18/2023 15:41:57 => [Error] Could not find type ‘System.Collections.Generic.List(AssemblyReference)’ in assembly ‘System.Private.CoreLib’. Row: 39, Column: 6
03/18/2023 15:41:57 => [Error] Could not find type ‘System.Collections.Generic.Dictionary(String,Object)’ in assembly ‘System.Private.CoreLib’. Row: 77, Column: 8
03/18/2023 15:41:57 => [Error] Could not resolve type ‘Error’ because could not find one or more of its type arguments. Row: 91, Column: 10
03/18/2023 15:41:57 => [Error] Could not find type ‘System.String’ in assembly ‘System.Private.CoreLib’. Row: 91, Column: 10
03/18/2023 15:41:57 => [Error] Could not find member ‘File’ in type ‘http://schemas.uipath.com/workflow/activities:ReadTextFile’. Row: 100, Column: 24

Hi Jithesh,
Thank you for reply and I am not able to open .xmal file. getting Errors. Could you resent the .xaml. It will be great and support

Hii @yashnegi108 ,
Can you open it now?
JsonResponse.zip (2.0 KB)

Regards,

3 Likes

Hi @ppr

Thank you for your detailed explanation, I am curious to understand the select method,
Is it not possible to use Select like below and convert that to datatable in single LINQ query?

JArray.FromObject(myJObject(“ipAuthorizations”).Values(Of JObject).Select(Function (x) new JObject(x(“authorizationNum”), x(“admitDate”)…?

if it is not possible, Can you share some reference link to understand the limitations in Select method, preferably in VB.net?

Thanks
VJ

As it is an older topic we suggest that you will open a new topic for your case and get individual help on this. Please share also your JSON data with us. Thanks

Hi @ppr

I could fetch specific fields data based on your linq query by changing the array with required field name.

My query was related to Select method in LINQ to understand it’s usage and limitations.

I am good for now, Thanks

Regards
VJ