Counting JSON Element Value Pair

[thakks]

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": "(^$)|(^(??:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(??:\\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": "(^$)|(^(??:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(??:\\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

[Eric_Zhang]

---

@thakks wrote:

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": "(^$)|(^(??:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(??:\\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": "(^$)|(^(??:\\(?(?:0(?:0|11)\\)?[\\s-]?\\(?|\\+)44\\)?[\\s-]?(?:\\(?0\\)?[\\s-]?)?)|(?:\\(?0))(??:\\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

---

@thakks

Per my knowledege on PowerQuery, there's no approach or at least no elegant approach to get the count of all leaf nodes elements from a dynamic given JSON. Even in other language like Python or C#, to get the count, you'll have to apply nested loops/recursions, I don't think it is a good approach to get that count in Power BI end. If the count does matter to you, I'd suggest you deal with it in data end, before importing to Power BI.

@MarcelBeug and @ImkeF may have more constructive advice.

[irisp]

I would love to share a tool which I use to manage JSON Extensivly. 

JSON Editor

JSON viewer

[thakks]

Thanks for sharing the @irisp for sharing the tools. I use the plugin within Notepad mostly but nice to know.

@Eric_Zhang we are looking to do exactly what you mentioned by doing at the data end, which in our case is PostgreSQL, but the issue is we have the solution in place in production and we identified that for some reason our current solution is not extracting all the element from the json object. 

So we trying to have a temporary report in Power BI for now to see how many of those records with extracted element do not match to the actual number of element available in the json object. 

thanks