Extract specific values from nested JSOn output

[MAHESH1 (MAHESHKUMAR JV)]

@pramilag

Please post the Entire JSOn for better Understanding and to know the Pattern of nodes.

Regards,
Mahesh

[pramilag (pramila)]

Hi Mahesh,

Please find the below json file:
{
“contract”: {
“contractHeader”: {
“effectiveDate”: “2019-01-01”,
“contractCode”: “G224”,
“productLifecycleState”: “Certified”,
“approvalStatus”: “Unapproved”,
“marketSegment”: “Large”,
“businessUnit”: “Local”,
“businessEntity”: “Anthem”,
“state”: “CA”
},
“admin”: {
“grandfatheringStatus”: “No”,
“dependentAgeMaximum”: “No”,
“annualLimitRestriction”: “Yes”,
“lifetimeLimitRestriction”: “No”,
“patientProtection”: “No”,
“preExisting”: “Yes”,
“preventitveZeroCostshare”: “No”,
“womensPreventiveZeroCostshare”: “Yes”,
“womensPreventitiveContraceptiveSterilization”: “Yes”,
“performanceGuaranteeIndicator”: “No”,
“liabilityPriorityRule”: “BirthdayRule”,
“exchangeIndicator”: “AvailableOffExchangeOnly”,
“metalLevel”: “Gold”,
“hrcCompliantIndicator”: “Yes”,
“qhpIndicator”: “Yes”,
“qhpVariation”: “01”,
“productMarketingName”: “Test Gold CA Product 500”,
“productMarketingCode”: “TGCP500”,
“companyCode”: “360C”,
“coverageLimitedTo”: “Individual”,
“maximumEnrollmentAge”: 36,
“minimumSubscriberEnrollmentAge”: 17,
“hpccCode”: “HBCD”,
“renewalEndDate”: “9999-12-31”,
“salesEffectiveDate”: “2017-10-01”,
“endNewSales”: “9999-12-31”,
“priceMixerDataList”: [ {
“priceMixerData”: {
“pricingCoverageCode”: “DEN”,
“pricingCode”: “00001”,
“pricingNetwork”: “PPO”
}
}
]
},
“planAdminList”:[
{
“planAdmin”: {
“lob”: “Dental”,
“premiumEHB”: “Yes”,
“premiumStateMandatedBenefits”: “Yes”,
“premiumNonStateMandatedBenefits”: “No”
}
}
],
“plans”:[
{
“plan” :{
“lob”:“Dental”,
“proxyID”:“1122334455”
}

	}
],
"contacts":[
	{
	"contact":{
		"role":"Customer Service",
		"name":"Customer Service Phone",
		"phone":"18001231234"
	}
	}
]

}
}

Thank you.

Regards,
Pramila

[aksh1yadav (AKSHAY YADAV)]

Hey @pramilag

Just use single line to get your desired value -

String Lob=JObject.Parse(JsonData).SelectToken(“contract.planAdminList[0].planAdmin.lob”).ToString

and for

String pricingCoverageCode=JObject.Parse(JsonData).SelectToken(“contract.admin.priceMixerDataList[0].priceMixerData.pricingCoverageCode”).ToString

Import Namespace Newtonsoft.json.Linq if you will get error with JObject.

Regards…!!
Aksh

[pramilag (pramila)]

Hi Aksh,

Thanks for the inputs.
That works

Regards,
Pramila

[aksh1yadav (AKSHAY YADAV)]

@pramilag

Thanks for the inputs.
That works

Your Welcome

Regards…!!
Aksh

[pramilag (pramila)]

Hi,

I am trying to use the array values from JSON as input to the select multiple items activity for the below JSON:
{
“contract”: {
“contractHeader”: {
“effectiveDate”: “2019-01-01”,
“contractCode”: “2FVU”,
“productLifecycleState”: “Operations”,
“approvalStatus”: “Approved”,
“marketSegment”: “SMALL”,
“businessUnit”: “Local”,
“businessEntity”: “Anthem Blue Cross and Blue Shield”,
“state”: “CO”,
“lineOfBusiness”: [
“Medical”,
“Pharmacy”,
“Vision”,
“Dental”
]
},
“admin”: {
“grandfatheringStatus”: “No”,
“dependentAgeMaximum”: “Yes”,
“annualLimitRestriction”: “No”,
“lifetimeLimitRestriction”: “No”,
“patientProtection”: “Yes”,
“preExisting”: “No”,
“preventitveZeroCostshare”: “Yes”,
“womensPreventiveZeroCostshare”: “Yes”,
“womensPreventitiveContraceptiveSterilization”: “Yes”,
“performanceGuaranteeIndicator”: “No”,
“liabilityPriorityRule”: “Birthday Rule”,
“exchangeIndicator”: “Off”,
“metalLevel”: “Silver/Essential”,
“hrcCompliantIndicator”: “Yes”,
“qhpIndicator”: “No”,
“qhpVariation”: “00”,
“productMarketingName”: “Anthem Silver PPO 5300/25%/6450”,
“productMarketingCode”: “TGCP500”,
“productShortName”: “Sv PPO 5300/25% 3J34”,
“productLongName”: “Sv PPO 5300/25%/6450 3J34”,
“productBillName”: “Sv 3J34”,
“companyCode”: “525C”,
“coverageLimitedTo”: “Individual + Family”,
“maximumEnrollmentAge”: 26,
“minimumSubscriberEnrollmentAge”: 17,
“hpccCode”: “HM65”,
“renewalEndDate”: “12/31/9999”,
“salesEffectiveDate”: “2019-01-01”,
“endNewSales”: “9999-12-31”,
“smallEmployerGroupAssociation”: [
“CO02”,
“DBN1”,
“NN09”
],
“castlightCostTransparencyIndicator”: “Care and Cost Finder (9)”,
“priceMixerDataList”: [
{
“priceMixerData”: {
“pricingCoverageCode”: “MED”,
“pricingCode”: “0000”,
“pricingNetwork”: “PPO”
}
},
{
“priceMixerData”: {
“pricingCoverageCode”: “DRG”,
“pricingCode”: “0000”,
“pricingNetwork”: “PPO”
}
},
{
“priceMixerData”: {
“pricingCoverageCode”: “DEN”,
“pricingCode”: “0000”,
“pricingNetwork”: “PPO”
}
},
{
“priceMixerData”: {
“pricingCoverageCode”: “VIS”,
“pricingCode”: “0000”,
“pricingNetwork”: “PPO”
}
}
]
},
“planAdminList”: [
{
“planAdmin”: {
“lob”: “Medical”,
“memberproductCode”: “HXNS”,
“benefitPlanCode”: “Sv PPO 5300/25% 3J34”,
“standardPlanCode”: “null”,
“corporatePlanCode”: “N/A”,
“networkId”: “COPPO100”,
“networkName”: “Anthem PPO”,
“fitnessCapPlanCode”: “GFP1”,
“suppressBlueExchange”: “No”,
“suppressAutoAdjudication”: “No”,
“suppressITSAutoAdjudication”: “No”,
“suppressCOBAutoAdjudication”: “No”,
“suppressMedicareAutoAdjudication”: “No”,
“thirdPartyLiability”: “Yes”,
“premiumEHB”: “Yes”,
“premiumStateMandatedBenefits”: “No”,
“premiumNonStateMandatedBenefits”: “No”
}
},
{
“planAdmin”: {
“lob”: “Pharmacy”,
“premiumEHB”: “Yes”,
“premiumStateMandatedBenefits”: “No”,
“premiumNonStateMandatedBenefits”: “No”
}
},
{
“planAdmin”: {
“lob”: “Dental”,
“premiumEHB”: “Yes”,
“premiumStateMandatedBenefits”: “No”,
“premiumNonStateMandatedBenefits”: “No”
}
},
{
“planAdmin”: {
“lob”: “Vision”,
“premiumEHB”: “Yes”,
“premiumStateMandatedBenefits”: “No”,
“premiumNonStateMandatedBenefits”: “No”
}
}
],
“contactsList”: [
{
“contactData”: {
“role”: “”,
“name”: “Anthem Blue Cross and Blue Shield”,
“phoneNumber”: “(855) 837-8536”
}
},
{
“contactData”: {
“role”: “Pre Certification Vender”,
“name”: “”,
“phoneNumber”: “(800) 832-7850”
}
},
{
“contactData”: {
“role”: “Advanced Imaging Pre Cert Vendor”,
“name”: “American Imaging Management”,
“phoneNumber”: “(877) 291-0366”
}
},
{
“contactData”: {
“role”: “PT/OT Pre Certification Vendor Contact”,
“name”: “ORTHONET”,
“phoneNumber”: “(844) 282-6994”
}
}
],
“contractObjectId”: “1547744424712_3J34”
},
“plans”: [
{
“plan”: {
“lob”: “Medical”,
“proxyid”: “0428510667”
}
},
{
“plan”: {
“lob”: “Pharmacy”,
“placeHolderProxyId”: “8888888887”
}
},
{
“plan”: {
“lob”: “Vision”,
“placeHolderProxyId”: “8888888885”
}
},
{
“plan”: {
“lob”: “Dental”,
“placeHolderProxyId”: “8888888886”
}
}
]
}

The lineOfBusiness attribute under the contractHeader section has medical , pharmacy, vision, dental.
I am able to save the values in an array of JToken but not able to convert into string.
Same is for smallEmployerGroupAssociation under the admin section.

Please find my syntax for the fetching the array values:

(Obj_Json.parse(responseJson))(“contract”)(“contractHeader”)(“lineOfBusiness”).toArray()

But this gives me Jtoken array which am unable to pass as input in the select multiple Items activity.
Please help me converting the same to array of string.

Thanks for your massive help.

@aksh1yadav @MAHESH1 could you please provide me any inputs on the same

[Rishi_p (Hrishikesh)]

I need to extract a particular value from json output

[
{
“relatedNumberId”: null,
“relatedNumberType”: “MSA”,
“transactionId”: 550721026,
“name”: “MSA”,
“value”: “421318TWH 6th & 421293twh”,
“displayUrl”: “”,
“displayValue”: “421318TWH 6th & 421293twh”
},
{
“relatedNumberId”: null,
“relatedNumberType”: "PON ",
“transactionId”: 550721026,
“name”: "PON ",
“value”: “2774436912809302”,
“displayUrl”: “”,
“displayValue”: “2774436912809302”
}
]

How can i extract “2774436912809302” from above response . PLease help

[loginerror (Maciej Kuźmicz)]

Hi @Rishi_p

You can use the Deserialize JSON Array activity to parse your string like so:

And then you will be able to access your variable with the following syntax:

jsonOutputArray(1)("displayValue").ToString

To use this activity Deserialize JSON Array you will need to install the UiPath.Web.Activities package:

[Rishi_p (Hrishikesh)]

@loginerror

jsonOutputArray(1)(“displayValue”).ToString

Thanks a lot @loginerror Appreciate all your Help

[aksh1yadav (AKSHAY YADAV)]

@pramilag

help me converting the same to array of string

Hey @pramilag

Feeling Sorry for My Delayed Response…!! Was on personal vacations

JObject.Parse(JsonData).SelectToken(“contract.contractHeader.lineOfBusiness”).Select(function(s) Convert.ToString(s)).ToArray()

You will get array of String

Regards…!!
Aksh

[pramilag (pramila)]

Thank You @aksh1yadav that resolves my issue

Regards,
Pramila

[Rishi_p (Hrishikesh)]

hi Login error … i have to extract text details from "Note " but condition is if “Notetype” is ERROR then only details from Note should be extracted . the response is huge (below). can you please help me the solution .

{
“NotesReplyWithMetadata”: [
{
“MetaData”: [
{
“Name”: “ApplicationName”,
“Value”: “SwIFT”,
“NoteID”: 0
},
{
“Name”: “OrderPackageId”,
“Value”: “550721026”,
“NoteID”: 0
}
],
“Revision”: 0,
“Action”: “Create”,
“NoteID”: 36660581,
“ModifiedBy”: “Workflow”,
“ModifiedDate”: “2018-11-15T19:40:09.373”,
“CreatedBy”: “Workflow”,
“CreatedDate”: “2018-11-15T19:40:09.373”,
“Source”: “SwIFT”,
“NoteType”: “ERROR”,
“Note”: "PSP ID =440876543 GetIsmOrder was Successful ",
“NoteXml”: null,
“MetaValue”: null
},
{
“MetaData”: [
{
“Name”: “ApplicationName”,
“Value”: “SwIFT”,
“NoteID”: 0
},
{
“Name”: “OrderPackageId”,
“Value”: “550721026”,
“NoteID”: 0
}
],
“Revision”: 0,
“Action”: “Create”,
“NoteID”: 36660576,
“ModifiedBy”: “WORKFLOW”,
“ModifiedDate”: “2018-11-15T19:39:58.083”,
“CreatedBy”: “WORKFLOW”,
“CreatedDate”: “2018-11-15T19:39:58.083”,
“Source”: “SwIFT”,
“NoteType”: “SYSTEM”,
“Note”: “Order Package Changed by WORKFLOW as :\r\n Update Order Package Status EMP Message was successfully sent.\r\n”,
“NoteXml”: null,
“MetaValue”: null
},
{
“MetaData”: [
{
“Name”: “ApplicationName”,
“Value”: “SwIFT”,
“NoteID”: 0
},
{
“Name”: “OrderPackageId”,
“Value”: “550721026”,
“NoteID”: 0
}
],
“Revision”: 0,
“Action”: “Create”,
“NoteID”: 36660575,
“ModifiedBy”: “WORKFLOW”,
“ModifiedDate”: “2018-11-15T19:39:57.973”,
“CreatedBy”: “WORKFLOW”,
“CreatedDate”: “2018-11-15T19:39:57.973”,
“Source”: “SwIFT”,
“NoteType”: “SYSTEM”,
“Note”: “Order Package Changed by WORKFLOW as :\r\n Order Package Status changed to PartiallyOrdered\r\n”,
“NoteXml”: null,
“MetaValue”: null
},
{
“MetaData”: [
{
“Name”: “ApplicationName”,
“Value”: “SwIFT”,
“NoteID”: 0
},
{
“Name”: “OrderPackageId”,
“Value”: “550721026”,
“NoteID”: 0
}
],
“Revision”: 0,
“Action”: “Create”,
“NoteID”: 36660574,
“ModifiedBy”: “WORKFLOW”,
“ModifiedDate”: “2018-11-15T19:39:57.863”,
“CreatedBy”: “WORKFLOW”,
“CreatedDate”: “2018-11-15T19:39:57.863”,
“Source”: “SwIFT”,
“NoteType”: “SYSTEM”,
“Note”: “Order Package Changed by WORKFLOW as :\r\n Product Service Package Status changed to Ordered : Product Package Id 440876544\r\n”,
“NoteXml”: null,
“MetaValue”: null
},
“Name”: “OrderPackageId”,
“Value”: “550721026”,
“NoteID”: 0
}
],
“Revision”: 0,
“Action”: “Create”,
“NoteID”: 36660573,
“ModifiedBy”: “Workflow”,
“ModifiedDate”: “2018-11-15T19:39:57.76”,
“CreatedBy”: “Workflow”,
“CreatedDate”: “2018-11-15T19:39:57.76”,
“Source”: “SwIFT”,
“NoteType”: “SYSTEM”,
“Note”: "PSP ID =440876544 GetIsmOrder was Successful ",
“NoteXml”: null,
“MetaValue”: null
},
{
“MetaData”: [
{
“Name”: “ApplicationName”,
“Value”: “SwIFT”,
“NoteID”: 0
},
{
“Name”: “OrderPackageId”,
“Value”: “550721026”,
“NoteID”: 0
}
],
“Revision”: 0,
“Action”: “Create”,
“NoteID”: 36660569,
“ModifiedBy”: “WORKFLOW”,
“ModifiedDate”: “2018-11-15T19:39:50.343”,
“CreatedBy”: “WORKFLOW”,
“CreatedDate”: “2018-11-15T19:39:50.343”,
“Source”: “SwIFT”,
“NoteType”: “SYSTEM”,
“Note”: "Order Package Changed by WORKFLOW as :\r\n Update Order Package Status EMP Message was successfully

[loginerror (Maciej Kuźmicz)]

The json file doesn’t seem to be correct… Could you double check it with some json validation website, for example here.

[Rishi_p (Hrishikesh)]

yes i did check in beautify json site… above is part of the json rsponse . I need to search for “NoteType”: “ERROR”, then fetch the corresponding text which is “Note” tag .

“NoteType”: “ERROR”,
“Note”: "PSP ID =440876543 GetIsmOrder was Successful ",

from above ex:“PSP ID =440876543 GetIsmOrder was Successful” should be retrieved . Need to appropriate syntax in jsonarray

[loginerror (Maciej Kuźmicz)]

Check this sample
ProcessJSON.zip (2.9 KB)

The entire process looks like that:

Keep in mind that I had to cut your JSON file from above a bit to make it recognizable, see it here:

SampleJSON

[Rishi_p (Hrishikesh)]

Thanks so much @loginerror. it worked . Appreciate all your help .

[Cosmin_Ion_Nicolae (Ion Nicolae)]

In the past I Also used Execute Xpath Activity.

[loginerror (Maciej Kuźmicz)]

Hi @Cosmin_Ion_Nicolae

Then you might also be able to help this user here

Xml parsing

Hi, After failing to extract Threaded Comments with VBA from Excel 2016, I went on a different approach. I have extracted the xml files from xlsx and tried to parse the comments from there. I have gone through the example here, but I still can not extract the comments… Source files comments2.xml (1.2 KB) Main.xaml (8.2 KB) Error on first for each [image]

[Rishi_p (Hrishikesh)]

hi @Loginerror, i need to extract PON value from the response below . Can you please help me with the syntax … If “relatedNumberType”: "PON then extract “value”: “2819892039902701”… the sequence of these responses keeps on changing …

[
{
“relatedNumberId”: null,
“relatedNumberType”: “LDTS”,
“transactionId”: 550749370,
“name”: “LDTS”,
“value”: “421293TWH”,
“displayUrl”: “http://workspace2/applications/ldts.nsf/ViewForm?OpenForm=Customer+Summaries”,
“displayValue”: “421293TWH”
},
{
“relatedNumberId”: null,
“relatedNumberType”: “MSA”,
“transactionId”: 550749370,
“name”: “MSA”,
“value”: “421318TWH 6th”,
“displayUrl”: “”,
“displayValue”: “421318TWH 6th”
},
{
“relatedNumberId”: null,
“relatedNumberType”: "PON ",
“transactionId”: 550749370,
“name”: "PON ",
“value”: “2819892039902701”,
“displayUrl”: “”,
“displayValue”: “2819892039902701”
},
{
“relatedNumberId”: “8dc86d73-e212-4f8e-8dd3-0b65d690c190”,
“relatedNumberType”: “SEO ID”,
“transactionId”: 550749370,
“name”: “SEO ID”,
“value”: “224974”,
“displayUrl”: “”,
“displayValue”: “224974”
}
]

[Rishi_p (Hrishikesh)]

Issue resolved ,I got the solution … thanks