Extract specific values from nested JSOn output

Help
, ,
1

Hello All,

I need to get specific values from output json. Like from below example I want to get value of ‘phyMarks’.

{
    "data": {
        "accounts": {
            "studentId": "2345",
            "schoolId": "034234",
            "SchoolName": "ABC",
            "schoolCode": "MU123",
            "State": "MH",
            "studentCount": 1234
        },
        "transactionSummary": [
            {
                "calDate": "2018-05-25T00:00:00.000",
                "phyMarks": 65,
                "choiceLanguage": "MAR",
                "studentBatch": "C",
                "studentId": 1234
            },
            {
               
                "calDate": "2018-05-25T00:00:00.000",
                "phyMarks": 75,
                "choiceLanguage": "HIN",
                "studentBatch": "D",
                "studentId": 342
            }
        ]
    },
    "status": {
        "isSuccess": true,
        "message": "SUCCESS",
        "statusCode": "0"
    }
}

Any help would be appreciated.
Thanks

1 Like
2

Refer this

@Rahul_S

3

@Rahul_S

First Read the text and serialise it to a JObject Let us take it as obj
Create a List of strings List A

 ListA= (From p In obj(“data”)(“transactionsummary”)
            Select Convert.ToString(p("phyMarks")).ToList

Now List A will contain the values of PhyMarks.

Regards,
Mahesh

4

Thanks for reply Shreekanth

I am trying to save my output json in one variable and then I am applying the given solution.

But I am getting following error:

“For each : Object reference not set to an instance of an object.”

Thanks

5
7

@Rahul_S

No actually now I cant send the xaml. try on your own, If you find any difficulties. please ask i will try to help you.

Regard,
Mahesh

9

@Rahul_S
where you have stucked.
first read the notepas by using readtextfile activity.
then use serialise jason activity the store it in a jobject variable type.

then use the Linq which i have given to you in assign activity.

Regards,
Mahesh

10

Hi

I am not storing JSON output to any file.

I am storing JSON to string variable and then trying to perform.

Getting error “Object Reference Not set to Object.”

Please can you send one sample example?

That will really help me.

11

@Rahul_S

Read the text file and store it in a string variable, then Parse the string to a Jobject then use the Linq to get the required values

I have attached the xaml for your reference.

JSON.zip (2.8 KB)

Regards,
Mahesh

1 Like
12

@MAHESH1

Thanks a lot Mahesh.

14

Hi Mahesh,

The above solution works fine if we are trying to extract any data under “transactionSummary” but when the same logic is applied to extract any value under “student” (say studentId) then its throwing an error in the assign activity where we are retrieving the list using Linq.
Please find the error : “cannot access child value on newtonsoft.jlinq.jproperty”

I guess the logic works fine when we are trying to retrieve the array value

Could you please suggest as in how to get the value of “studentId” for the same since its not enclosed in an array and is an object

Appreciate your massive help.

Regards,
Pramila

15

@pramilag

First you have to read the json file and deserialise it to Jobject. Let us take the Jobject as Jobj

The property account is assigned with Json string, but the property transactionSummary is assigned with Array of Json.

try like this

strStudentID= Jobj(“data”)(“accounts”)(“studentId”).ToString

Regards,
Mahesh

1 Like
16

Thank you for your inputs mahesh :slight_smile:

PFB my json template i need to extract the values from the planAdminList and PlanAdmin i.e. lob, proxyID, premiumEHB.

{
  "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"
		}
		}
	]
  }
}

I have tried the same logic as you had suggested earlier for json array but its throwing error as
Object reference not set to an instance of an object.

Am able to extract the values which are not associated to json array

Could you please help me out with the above error

Thanks for your help

Regards,
Pramila

17

@pramilag

First read the Json and Deserealize it to Jobj.

Next Use Deserealize JasonArray Activity and pass the string as Jobj(“planAdminList”).ToString and store as Jarray.

Next Run Foreach for Jarray.

Inside ForEach Use Deserealise Json Activity and store the value as Jvalue. the data type should be Jobject.

strlob=Jvalue(“lob”).ToString
strpremiumEHB=Jvalue(“premiumEHB”).ToString
strpremiumStateMandatedBenefits=Jvalue(“premiumStateMandatedBenefits”).ToString
strpremiumNonStateMandatedBenefits=Jvalue(“premiumNonStateMandatedBenefits”).ToString

Use the same logic for extracting the property of Plans. If you need more help please let me know :slightly_smiling_face:

Regards,
Mahesh

18

Hi Mahesh,

Thank you for responding at the earliest .

But whenever I am trying to use deserialize JSON array activity its always throwing me the same error as : object reference not set to an instance of an object

I had tried the same with another json file as well to retrieve the values associated with the json array but the same error shows up not sure why.

I tried using the deserialize json array using the approach put forth by you but still same issue.

Please find as attached the xaml file. Kindly let me know if am going wrong anywhere.

Thanks for your help.

Regards,
PramilaArray_Json.xaml (8.3 KB)

19

@MAHESH1

Hi Mahesh,

Did you get any chance to look into the issue.

If you could help me out with that it would be really great.

Thank You.

Regards,
Pramila

20

@pramilag

Sorry for Late Reply.

I have attached Zip File . Please refer it.

JSONArray.zip (3.6 KB)

Regards,
Mahesh

2 Likes
21

Thank you so much mahesh :slight_smile:

That works perfect.

Regards,
Pramila

1 Like
22

@pramilag

You are welcome :slightly_smiling_face:

Regards,
Mahesh

23

Hi Mahesh,

I tried the same scenario to extract the price mixer data but not sure it is retrieving me null value.
Please refer to the json for pricingcoveragecode :
priceMixerDataList": [ {
“priceMixerData”: {
“pricingCoverageCode”: “DEN”,
“pricingCode”: “00001”,
“pricingNetwork”: “PPO”

Regards,
Pramila

Please find as attached the xaml file :
ContractScreen_1.xaml (15.5 KB)