Not a tech whizz? No problem!

This guide is designed for absolute beginners who want to retrieve data from the NHS Open Data Portal (ODP) using an API. Don’t worry, it’s easier than it sounds, especially with tools like [Postman](https://www.postman.com/) to help you along.

*Don’t panic*—this isn’t going to be a full-on coding lesson. You just need a basic understanding of SQL (Structured Query Language), which is how you’ll ask the API for the data you want.\
\
Let’s get started, shall we?

**Step 1: The API URL – Where It All Happens**\
\
All your queries will be sent to the ODP API’s base URL:\
\
*https://opendata.nhsbsa.net/api/3/action/*

Think of this as the home address of the NHSBSA Open Data Portal.

**Step 2: The Endpoint**

*datastore_search_sql*

The endpoint is the specific “door” you’re using to access the data. In this case, datastore_search_sql allows you to ask the system for specific data using an SQL query (don’t worry, we’ll explain that below).

**Step 3: What does the '?' Mean?**\
\
The '*?*' is what signals to the system, “Hey, I’m about to give you some instructions!”\
\
Everything after the '*?*' in the URL is extra information called *parameters*. These parameters tell the system exactly what data you’re asking for.

**An Example Query**\
\
Here’s an example of a full query you might use:\
\
*https://opendata.nhsbsa.net/api/3/action/datastore_search_sql?resource_id=EPD_202404&sql=select%20\*%20from%20%60EPD_202404%60%20where%20POSTCODE%3D%27NE12%208EW%27%20limit%20100*\
\
Don’t worry if this looks a bit intimidating! We’ll break it down piece by piece.

**Step 4: Breaking Down the Query**\
\
*resource_id=EPD_202404*\
\
This tells the system which dataset you want to look at.\
\
In this case, the dataset is identified as EPD_202404. Think of it like choosing a specific folder in a filing cabinet.\
\
*sql=*\
\
This is where your SQL query goes. SQL is just a way of describing the data you want.

*select%20%2A*\
\
This asks the system to pull all the columns of data from the dataset. The *%20* is a space, and *%2A* is an asterisk (\*), which stands for “everything.”

*from%20%60EPD_202404%60*\
\
This part tells the system which dataset to pull from. The *%60* is a backtick (\`), which helps with dataset names.

*where%20POSTCODE%3D%27NE12%208EW%27*\
\
This filters the data to only show rows where the POSTCODE column equals NE12 8EW. The *%3D* is the equals sign, and *%27* is a single quote, wrapping the postcode.

*limit%20100*\
\
This limits the number of rows returned to 100. It’s like saying, “Give me the first 100 results, please.” The *%20* is just a space.

When you’re sending an API query over the internet, special characters (like spaces and symbols) commonly need to be encoded so they work in a URL. Here’s how the SQL query from the example looked before it was encoded:\
\
*select \* from* \`EPD_202404\` *where POSTCODE='NE12 8EW' limit 100*\
\
*select \**: "Show me all the columns in the dataset."\
*from EPD_202404*: "Look at the dataset called EPD_202404."\
*where POSTCODE='NE12 8EW'*: "Only give me rows where the postcode is NE12 8EW."\
*limit 100*: "Only show me up to 100 results."

Here's a quick and simple refresher to make sure we're all on the same page:

Spaces ( ) are replaced with %20.\
The \* becomes %2A.\
Backticks (\`) become %60.\
= becomes %3D.\
Single quotes (') become %27.

**Step 5: Breaking Down a More Advanced Query**\
\
*https://opendata.nhsbsa.net/api/3/action/datastore_search_sql?resource_id=EPD_202404&sql=select%20\*%20from%20%60EPD_202404%60%20where%20POSTCODE%3D%27NE12%208EW%27%20and%20BNF_DESCRIPTION%20LIKE%20%27%25powder%25%27%20limit%2010000*\
\
What’s happening here?

*select%20%2A*\
This asks for all the columns from the dataset. *%20* is a space, and *%2A* is the asterisk (\*), which means "everything."

*from%20%60EPD_202404%60*\
This part specifies the dataset you’re querying—EPD_202404. The *%60* is a backtick (\`), which is used to wrap the dataset name.

*where%20POSTCODE%3D%27NE12%208EW%27*\
This filters the data to only show rows where the POSTCODE is NE12 8EW. The *%3D* is the equals sign (=), and *%27* is the single quote ('), wrapping the postcode.

*and%20BNF_DESCRIPTION%20LIKE%20%27%25powder%25%27*\
This adds another filter to check that the BNF_DESCRIPTION contains the word "powder."\
*%25* is how % is encoded in a URL, and it’s used here to look for anything before and after the word "powder." This part makes sure you're only getting records where "powder" appears somewhere in the BNF_DESCRIPTION field.\
\
*limit%2010000*\
This limits the number of records returned to 10,000. It’s basically saying, “Give me up to 10,000 results.” The *%20* here is just a space.

**To put it plainly, this API is**:

- querying the EPD_202404 dataset.
- looking for records where the POSTCODE is NE12 8EW.
- checking if the BNF_DESCRIPTION contains the word “powder”.
- limiting the results to 10,000 records.

**Step 6: Customising Your Query**\
\
You can tweak the query to fit your needs. For example:\
\
*https://opendata.nhsbsa.net/api/3/action/datastore_search_sql?resource_id=EPD_202312&sql=select%20%2A%20from%20%60EPD_202312%60%20where%20NIC%3E150%20limit%20100*

*select%20%2A*\
This asks for all the columns from the dataset. *%20* is a space, and *%2A* is the asterisk (\*), which means “everything.”

*from%20%60EPD_202312%60*\
This specifies the dataset you want to query, which is EPD_202312. The *%60* is a backtick (\`), used around the dataset name to handle any special characters.

*where%20NIC%3E150*\
This filters the data to only show records where the NIC (Net Ingredient Cost) is greater than 150. \
*%3E* is the greater-than sign (&gt;), and it checks if NIC is more than 150.

*limit%20100*\
This limits the number of records returned to 100. It’s like saying, “I only want the first 100 results.” The *%20* is just a space.

**Let's say it together, this API is:**

- querying the EPD_202312 dataset.
- looking for records where the NIC is greater than 150.
- limiting the results to 100 records only.

A Note: The API only works with one dataset (resource_id) at a time. If you need data from multiple datasets, you’ll have to run separate queries and merge the results later.

**Step 3: Test Your Queries in Postman**\
\
Postman is a brilliant tool for testing API queries. It’s free and makes things much less intimidating.\
\
**Getting Started with Postman**

1. Install [Postman](https://www.postman.com/) and open it or use the in-browser version.
2. Copy your query URL and paste it into the GET request field.

https://opendata.nhsbsa.net/api/3/action/datastore_search_sql?resource_id=EPD_202312&sql=select%20*%20from%20%60EPD_202312%60%20where%20NIC%3E150 limit 100

**Checking the Response**\
\
• If everything’s working, you’ll see **"success": "true"** in the response.

If **"success": "false"**, there’s likely an error in your query—double-check for typos or missing bits.

**Making the Data Easier to Read**\
\
The response will be in JSON format, which isn’t the prettiest thing to look at. Use Postman’s **Visualize** button to turn it into a table that’s much easier on the eyes.

**Step 4: Download and Open the Data in Excel**\
\
Want to work with the data in Excel? Easy peasy.

1. Save the API response as a JSON file.

2. Open Excel, and go to:\
   \
   **Data → Get Data → From File → From JSON**

3. Import the JSON file from your downloads or whichever location you saved the file to when completing Step 12 earlier

How to Fetch ODP Data via API – A Beginner’s Guide