How to extract URL from JSON file that is available in public?

Question

How to extract URL from JSON file that is available in public?

Justin Doh 860

I am trying to extract certain URL information from JSON file that is available in public.

I am referring to this URL:

https://data.cms.gov/provider-data/api/1/metastore/schemas/dataset/items/hicp-9999?show-reference-ids=true

What I need to get is extract this particular URL on a daily basis.

distribution --> data --> downloadURL

User's image

What is big picture of objects (architecture) that I could use in Logic App?

Eventually, this is a csv file, so I need to also load the csv file in Azure.

Thanks.

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Marcin Policht 68,145 MVP Volunteer Moderator

So it looks like you want to set up an Azure Logic App pipeline that does the following every day:

Fetch JSON metadata (the one at https://data.cms.gov/provider-data/api/...).
Extract the distribution → data → downloadURL value (the actual CSV link).
Download the CSV file from that link.
Store the CSV in Azure (e.g., in Blob Storage, Data Lake, or SQL depending on your use case).

If so, here's a high-level flow:

Trigger
- Recurrence trigger → runs daily.
HTTP Request (GET)
- Use HTTP action to call the JSON metadata API (https://data.cms.gov/provider-data/api/...).
Parse JSON
- Use Parse JSON action with the schema from the API response.
- This will let you navigate to distribution[0].data.downloadURL.
Extract URL
- From the parsed JSON, get the field downloadURL.
HTTP Request (GET CSV)
- Use the extracted downloadURL in another HTTP action to download the CSV file.
Save File to Azure
- Use Azure Blob Storage - Create blob action (or Data Lake equivalent).
- File name can include timestamp (e.g., NH_HlthInsp_@{utcNow()}.csv).

+------------------+ | Recurrence | | (Daily Trigger) | +------------------+ | v +---------------------+ | HTTP - GET JSON | | (CMS API Metadata) | +---------------------+ | v +---------------------+ | Parse JSON | | (extract downloadURL)| +---------------------+ | v +---------------------+ | HTTP - GET CSV | | (use downloadURL) | +---------------------+ | v +--------------------------+ | Azure Blob Storage | | (Save CSV file) | +--------------------------+



---
If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Krishna Chowdary Paricharla 2,915 Reputation points Microsoft External Staff Moderator

2025-08-25T17:34:41.4933333+00:00

Hello Justin Doh,

Following up to see if you have a chance to check the previous response from Marcin Policht and let me know if you have any further questions on this.
Justin Doh 860 Reputation points

2025-08-25T18:33:22.91+00:00

@Marcin Policht Thank you for your help. Let me try out the architecture and let you know if I come up with questions. Thank you!
Justin Doh 860 Reputation points

2025-08-25T21:38:13.2566667+00:00

@Marcin Policht I am struggling with finding JSON file from its website:

https://data.cms.gov/provider-data/dataset/hicp-9999#api

When I tried the URL, it appears that it is displaying in JSON format.https://data.cms.gov/provider-data/api/1/metastore/schemas/dataset/items/hicp-9999?show-reference-ids=true

But, when I copied the whole data into "Schema" section of Logic App's "Parse JSON", it would not display JSON format properly, but getting an error - "Enter a valid JSON".

How do I properly get the JSON document?
Marcin Policht 68,145 Reputation points MVP Volunteer Moderator

2025-08-25T23:41:57.72+00:00
The data you are parsing is a sample JSON response. Parse JSON expects a schema (a description of the structure), not the raw JSON. That's why Logic Apps throws “Enter a valid JSON” when you paste the dataset into the Schema box.

To get the correct schema:

In your Logic App:

After the HTTP action that calls https://data.cms.gov/provider-data/api/1/metastore/schemas/dataset/items/hicp-9999?show-reference-ids=true

Add Parse JSON action.

In Parse JSON:

Click Use sample payload to generate schema.

Paste your JSON sample (the big { "accessLevel": ... } you showed).

Logic App will auto-generate the schema for you.

After parsing, you'll be able to drill down:

distribution[0].data.downloadURL

That's the field with the CSV link. In Logic App dynamic content, you'll see something like:

Body('Parse_JSON')?['distribution'][0]['data']['downloadURL']

So the full flow (daily run) would consist of:

Recurrence trigger (daily).

HTTP GET → call CMS API JSON.

Parse JSON → generate schema using sample payload.

Get downloadURL → distribution[0].data.downloadURL.

HTTP GET CSV → pass in the URL from step 4.

Create Blob (Azure Storage) → save CSV.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Krishna Chowdary Paricharla 2,915 Reputation points Microsoft External Staff Moderator

2025-08-26T18:32:01.4366667+00:00

Hello Justin Doh,

Following up to see if you have a chance to check the previous response from Marcin Policht and let me know if you have any further questions on this.
Justin Doh 860 Reputation points

2025-08-26T18:33:30.34+00:00

@Marcin Policht Thank you so much again.

I have following three actions so far:

What am I missing?
Marcin Policht 68,145 Reputation points MVP Volunteer Moderator

2025-08-26T23:08:40.07+00:00
The reason Logic Apps shows “Unable to generate schema” is because the JSON you pasted is not valid JSON in a strict sense. More specificaly, you have:

Property names starting with % Example:
"%Ref:downloadURL": [...]
JSON does not allow % in property names without quotes, and even with quotes some generators choke on them.

Trailing commas or comments If the JSON you pasted has any stray commas (, at the end of an object or array), Logic Apps will fail schema generation.

To fix this:

Copy the JSON returned from the API URL directly:
https://data.cms.gov/provider-data/api/1/metastore/schemas/dataset/items/hicp-9999?show-reference-ids=true
(don't copy it from the webpage view with formatting, use the raw API response in Postman or a browser → Save As).

Remove/ignore extra properties you don't need, especially the problematic %Ref:downloadURL and %modified. For schema generation, you only need the path to:
distribution[0].data.downloadURL
So you can simplify the JSON sample to something like this:
{ "distribution": [ { "data": { "downloadURL": "https://data.cms.gov/provider-data/sites/default/files/resources/sample.csv" } } ] }

Paste this cleaned JSON into “Use sample payload to generate schema”. Logic Apps will successfully generate a schema like this:
{ "type": "object", "properties": { "distribution": { "type": "array", "items": { "type": "object", "properties": { "data": { "type": "object", "properties": { "downloadURL": { "type": "string" } } } } } } } }

Once Parse JSON has this schema, you should be able to reference:

body('Parse_JSON')?['distribution'][0]['data']['downloadURL']

and pass it into your HTTP (GET CSV) step.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Justin Doh 860 Reputation points

2025-08-27T17:20:20.78+00:00

@Marcin Policht Thank you so much again!

I made some progress as far as putting necessary JSON inside "Parse JSON", but I am not sure how to pass HTTP (GET CSV).

I am at this point (up to second HTTP):

How do I pass HTTP (GET CSV)?

I think, after this, I should be able to handle rest (putting csv into Azure Blob Storage).
Krishna Chowdary Paricharla 2,915 Reputation points Microsoft External Staff Moderator

2025-08-29T17:29:52.5+00:00
Hello Justin Doh,
You are proceeding correctly. As you have parsed the JSON and obtained the download URL, the next step is to use that URL in your second HTTP action.

In your HTTP 1 action (the second HTTP), set:

Method: GET

URI: use the expression you already built: body('Parse_JSON')?['distribution'][0]['data']['downloadURL']

This will dynamically call the CSV download URL.

Once the HTTP GET runs, the response body will contain the CSV file contents. You can then pass this output into your next action (for example, Create Blob in Azure Blob Storage). Use @body('HTTP_1') (or whatever your HTTP step name is) as the input for the blob content.

Make sure to set the Content-Type in your HTTP action if needed (for CSV it is usually text/csv), but many APIs return it correctly by default.

So, your flow will look like this:

HTTP (initial) → Parse JSON → HTTP (GET CSV with downloadURL) → Create Blob (Azure Storage)

This will fetch the CSV file from the parsed URL and save it to your blob container.
Justin Doh 860 Reputation points

2025-08-29T18:03:23.8633333+00:00

@Krishna Chowdary Paricharla Thank you so much for your help!

Thank you for confirmation.

I guess I am stuck at Create Blob now.

I am not sure how to complete the Connection from Logic App.

Where do I find "Authentication Type" , "Clinet ID" and "Tenant" informations?

I might create an Azure Support Ticket for this, but if you can give me any insight, that would be great, too.

I will close the ticket and give credit to @Marcin Policht since he helped a lot.
Krishna Chowdary Paricharla 2,915 Reputation points Microsoft External Staff Moderator

2025-08-29T18:10:28.1033333+00:00
Hello Justin Doh,

Thank you for your response! Regarding the Logic App connection to Azure Blob Storage, here’s some guidance:

Authentication Type:

When you create the connection in Logic Apps, you usually have options like Account Key, Service Principal, or Managed Identity.

If you want to use a Service Principal, you will need the Client ID, Tenant ID, and Client Secret.

Finding Client ID, Tenant ID, and Secret:

Go to Azure Active Directory → App Registrations → [Your App].

Client ID: Found on the app’s Overview page.

Tenant ID: Also on the Overview page.

Client Secret: Go to Certificates & Secrets → New client secret. Make sure to copy it when created, as it will not be visible later.

Alternative:

If you use Managed Identity, you don’t need to manually provide these values. You can enable Managed Identity for your Logic App and assign it Storage Blob Data Contributor role on the Blob Storage.

Share via

How to extract URL from JSON file that is available in public?

0 additional answers

Your answer