cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thakks Regular Visitor
Regular Visitor

Counting JSON Element Value Pair

I was looking to count how many element value pair value we have received in the json response from one of our Web API. Anyone had any luck or can figure out for me how to achieve this. I have included the sample json for your reference and the expected output in power bi, if possible.

We tried to use Record.FieldCount, but that only counted the number of element in the next immediate nested level instead of counting all the element value pair available at the lowest depth.

 

This is one json with 3 element

(header_acc,accordion_acc2, form_attributes) in the formdata and then it has 20 value in total in those three element. {"schema": {"type": "object", "$schema": "http://json-schema.org/draft-04/schema# …", "required": ["header_acc"], "properties": {"header_acc": {"$ref": "#/definitions/header_acc"}, "accordion_acc2": {"$ref": "#/definitions/accordion_acc2"}}, "definitions": {"header_acc": {"required": ["first_name", "surname", "email_address"], "properties": {"surname": {"type": "string", "pattern": "[a-zA-Z'\\ \\s\\-]*$"}, "first_name": {"type": "string", "pattern": "[a-zA-Z'\\ \\s\\-]*$", "maxLength": 11, "minLength": 1}, "email_address": {"type": "string", "pattern": "(^[a-zA-Z0-9]([a-zA-Z0-9\\_\\-]|\\.[a-zA-Z0-9]){0,29}@[a-zA-Z0-9\\_\\-]{1,30}\\.[a-zA-Z0-9]([a-zA-Z0-9]|\\.[a-zA-Z0-9])+$)"}}}, "accordion_acc2": {"properties": {}}}}, "formdata": {"header_acc": {"surname": "Writing", "first_name": "Will", "email_address": "a.tellman@savethechildren.org.uk", "joinus_update_email": "false"}, "accordion_acc2": {}, "form_attributes": {"hash": "aqe8KTF3s5VWERmHftYYARmsjBrM09BjC9pr+CDFK+Q=", "userNo": "6 5 5 7 0", "channel": "", "form_id": "69", "fund_id": "61", "pageUrl": "https://savethechildren-prod.adobecqms.net/how-you-can-help/leave-a-gift-in-your-will/get-your-free-will-writing-guide …", "form_name": "data-legacy-00019", "fund_code": "Non-financial_0000", "fund_name": "Non-Financial Fund", "campaign_id": "63", "redirectUrl": "/content/stc/gb/en/admin/forms/thankyou.html", "campaign_code": "Will-Writing-Guide-Sign-Up", "campaign_name": "Request a Will Writing Guide", "form_type_name": "Data", "marketing_source": "", "marketing_campaign": ""}}}

 

This one have 11 element and then 56 element value pair between those 11 element.

{"schema": {"type": "object", "$schema": "http://json-schema.org/draft-04/schema# …", "required": ["donation_amount_acc"], "properties": {"gift_aid_acc": {"$ref": "#/definitions/gift_aid_acc"}, "accordion_endpar": {"$ref": "#/definitions/accordion_endpar"}, "donation_type_acc": {"$ref": "#/definitions/donation_type_acc"}, "review_confirm_acc": {"$ref": "#/definitions/review_confirm_acc"}, "address_billing_acc": {"$ref": "#/definitions/address_billing_acc"}, "donation_amount_acc": {"$ref": "#/definitions/donation_amount_acc"}, "donation_method_acc": {"$ref": "#/definitions/donation_method_acc"}, "donation_prompt_acc": {"$ref": "#/definitions/donation_prompt_acc"}}, "definitions": {"gift_aid_acc": {"properties": {"giftAid": {"radionButtonValue": {"enum": []}}}}, "accordion_endpar": {"properties": {}}, "donation_type_acc": {"properties": {}}, "review_confirm_acc": {"properties": {}}, "address_billing_acc": {"properties": {"joinUs": {"radionButtonValue": {"enum": []}}, "surname": {"type": "string", "pattern": "^[a-zA-Z'\\ \\s\\-]*$"}, "first_name": {"type": "string", "pattern": "^[a-zA-Z'\\ \\s\\-]*$"}, "expiry_date": {"type": "string", "pattern": "(1[0-2]|0[1-9])/[0-9]{2}"}, "account_name": {"type": "string", "pattern": "^[a-zA-Z ,.'-]+$"}, "securityCode": {"type": "string", "pattern": "^[0-9]{3}$"}, "email_address": {"type": "string", "pattern": "^[a-zA-Z0-9]([a-zA-Z0-9\\_\\-]|\\.[a-zA-Z0-9]){0,29}@[a-zA-Z0-9\\_\\-]{1,30}\\.[a-zA-Z0-9]([a-zA-Z0-9]|\\.[a-zA-Z0-9])+$"}, "personal_phone": {"type": "string", "pattern": "(^$)|(^(?Smiley Sad?:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(?Smiley Sad?:\\d{5}\\)?[\\s-]?\\d{4,5})|(?:\\d{4}\\)?[\\s-]?(?:\\d{5}|\\d{3}[\\s-]?\\d{3}))|(?:\\d{3}\\)?[\\s-]?\\d{3}[\\s-]?\\d{3,4})|(?:\\d{2}\\)?[\\s-]?\\d{4}[\\s-]?\\d{4}))(?:[\\s-]?(?:x|ext\\.?|\\#)\\d{3,4})?$)"}, "personal_mobile": {"type": "string", "pattern": "(^$)|(^(?Smiley Sad?:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(?Smiley Sad?:\\d{5}\\)?[\\s-]?\\d{4,5})|(?:\\d{4}\\)?[\\s-]?(?:\\d{5}|\\d{3}[\\s-]?\\d{3}))|(?:\\d{3}\\)?[\\s-]?\\d{3}[\\s-]?\\d{3,4})|(?:\\d{2}\\)?[\\s-]?\\d{4}[\\s-]?\\d{4}))(?:[\\s-]?(?:x|ext\\.?|\\#)\\d{3,4})?$)"}}}, "donation_amount_acc": {"required": ["currency_amount"], "properties": {"currency_symbol": {"enum": ["€", "£", "$"]}}}, "donation_method_acc": {"properties": {"donation_method_rbg": {"enum": ["paypal", "debit"]}}}, "donation_prompt_acc": {"properties": {"inspired_to_give_rbg": {"radionButtonValue": {"enum": ["Email", "Mail Pack", "Press/News Story", "TV Advert", "Online Advert", "Magazine Leaflet"]}}}}}}, "formdata": {"header_acc": {"above18_cb": "true"}, "gift_aid_acc": {"gift_aid_declaration": "false"}, "transactionId": "0d7ccb45-08df-491c-8109-75b2cf3e6954", "form_attributes": {"hash": "Yhdgl813BgW5QZz/VaHkDJ/5xEgGAba2lDbTCkCh9lQ=", "userNo": "6 5 5 7 0", "channel": "", "form_id": "56", "fund_id": "51", "pageUrl": "https://savethechildren-prod.adobecqms.net/donate/single/donation-single-00005 …", "form_name": "donation-single-00005", "fund_code": "UK-Linked_L010", "fund_name": "Our Work In The UK", "campaign_id": "49", "redirectUrl": "/content/stc/gb/en/admin/forms/thankyou.html", "campaign_code": "UK-Cash", "campaign_name": "UK Single Donation", "form_type_name": "Donation", "marketing_source": "", "marketing_campaign": ""}, "donation_type_acc": {"donation_type_rbg": "own_money"}, "worldpay_response": {"transactionId": "0d7ccb45-08df-491c-8109-75b2cf3e6954", "worldPayToken": "LIVE_SU_0d72d5df-054b-4fec-a1c1-af1787042c1a", "worldPayPaymentStatus": "SUCCESS", "worldPayAuthorizedAmount": 1.0, "worldPayOrderDescription": "Save The Children Donation"}, "review_confirm_acc": {}, "address_billing_acc": {"title": "Mrs", "surname": "DONATION", "mail_town": "London", "billing_cb": "true", "first_name": "TEST", "account_name": "Lim gondal", "billing_town": "London", "mail_country": "GB", "email_address": "xxxx@stc.org.uk", "mail_postcode": "ECxM 4AR", "worldPayToken": "LIVE_SU_0d72d5df-054b-4fec-a1c1-af1787042c1a", "personal_phone": "", "billing_country": "GB", "personal_mobile": "", "billing_postcode": "EC1M 4AR", "joinus_update_sms": "false", "mail_organisation": "Save the Children", "joinus_update_post": "true", "joinus_update_email": "false", "joinus_update_phone": "true", "mail_address_line_1": "1 St. John's Lane", "mail_address_line_2": "", "mail_address_line_3": "", "billing_organisation": "Save the Children", "billing_address_line_1": "1 St. John's Lane", "billing_address_line_2": "", "billing_address_line_3": ""}, "donation_amount_acc": {"currency_code": "USD", "currency_amount": "1", "currency_symbol": "$", "donation_frequency": "single"}, "donation_method_acc": {"donation_method_rbg": "debit"}, "donation_prompt_acc": {}}} 

So is there a way to show number of element value pair at the lowest level or at any given level. As in the above second example we have transaction id at the depth 1, while by default all our element value pair are at depth 4

3 REPLIES 3
v-huizhn-msft Super Contributor
Super Contributor

Re: Counting JSON Element Value Pair

Hi @thakks,

I am not specific about web API, you'd better post this thread to Power BI developer community to get professional support. 

Thanks,
Angelia

thakks Regular Visitor
Regular Visitor

Re: Counting JSON Element Value Pair

Thanks @v-huizhn-msft, now posted as advise in developer forum here 

 

 

v-huizhn-msft Super Contributor
Super Contributor

Re: Counting JSON Element Value Pair

Hi @thakks,

Ok, got it, welcome to share the advice you got in another community, please mark the helpful reply and your reply as answer, more people will benefit from here.

Best Regards,
Angelia