DEV Community

Gowtham D
Gowtham D

Posted on AI-assisted

How to query 65,000 rows of NYC nutrition data without downloading a CSV

#webdev #opendata #api #tutorial

You want to answer a small question say, which chain restaurant items have the most calories? and the data exists. NYC Open Data publishes MenuStat: 65,219 menu items from 96 US chains, with calories, sodium, serving sizes.

The usual path: download the CSV, write a loader, discover every number is a string, discover calories is sometimes null and sometimes the literal text "110-550", build a little Flask thing so your frontend can query it, deploy the little Flask thing, and three days later you have infrastructure instead of an answer.

This tutorial takes the other path: the dataset is already hosted as a typed REST API on API Bay. We'll query it with nothing but curl, page through it correctly, and because this is the part most tutorials skip go through the gotchas that cost real (if tiny) money.

The approach

Every dataset in the API Bay catalog answers the same query model: one key, one URL shape, the same parameters and filter operators everywhere. Learn it once on this dataset and it's the same on the next one. There's also a sandbox host where calls are free (100/day), so you can follow along without spending anything create a test key and swap the host.

Setup, once:

# paste your key when prompted, so it never lands in shell history
read -rs KEY
printf 'LB-Access-Token: %s\n' "$KEY" > ~/.apibay-key && chmod 600 ~/.apibay-key
Enter fullscreen mode Exit fullscreen mode

Your first call

The dataset is nyc_food_cart_menu_statistics. Ask for two rows, four columns:

curl -H @$HOME/.apibay-key \
  "https://api.selectstar.wtf/v1/apibay/nyc_food_cart_menu_statistics?ps=2&pg=0&fc=menu_item_id,restaurant,item_name,calories"
Enter fullscreen mode Exit fullscreen mode 
[
  { "calories": 25, "item_name": "Broccoli", "menu_item_id": 14850, "restaurant": "Denny's" },
  { "calories": 130, "item_name": "Mushroom Chicken, Kids", "menu_item_id": 62672, "restaurant": "Panda Express" }
]
Enter fullscreen mode Exit fullscreen mode

Three parameters did the work:

  • ps page size (rows per call, max 100)
  • pg page number, counting from 0
  • fc the columns you want; omit it and you get all 49

Note calories: 25 is a number. The schema is typed (Int32, Float64, String, Nullable) if you've ever pulled this dataset raw from the Socrata API, where every value arrives as a string, this alone saves you a parsing layer.

The response headers are half the product, so read them once:

x-cost-per-1k: USD 1.70          ← this dataset's price per 1,000 calls
x-wallet-remaining: USD 99.99    ← your prepaid balance after this call
x-ratelimit-remaining: 143994    ← calls left in the 24h window
Enter fullscreen mode Exit fullscreen mode

Every metered response prices itself. A program that spends money should read x-wallet-remaining-requests and x-ratelimit-remaining on every response more on why below.

Filtering, ordering, paging

A filter is column=operator.value. Answer the opening question highest-calorie items at or above 9,000 kcal:

curl -H @$HOME/.apibay-key \
  "https://api.selectstar.wtf/v1/apibay/nyc_food_cart_menu_statistics?ps=3&pg=0&fc=restaurant,item_name,calories&calories=ge.9000&oy=calories.ds"
Enter fullscreen mode Exit fullscreen mode 
[
  { "calories": 13960, "item_name": "Crave Crate w/ Any 100 Sliders in a Crate", "restaurant": "White Castle" },
  { "calories": 10205, "item_name": "Prime Rib", "restaurant": "Dickey's Barbeque Pit" },
  { "calories": 9240, "item_name": "Rib Tips Til Payday, Dinner", "restaurant": "Famous Dave's" }
]
Enter fullscreen mode Exit fullscreen mode

(Yes, White Castle sells a 13,960-calorie crate of 100 sliders. Open data delivers.)

There are 19 operators eq, ne, gt/ge/lt/le, bw (between), in, li (contains), il (contains, case-insensitive), mt (regex), is.NULL, and friends. Filters on different columns AND together; or=(...) groups alternatives:

# McDonald's or Subway, at least 1,000 kcal
...&or=(restaurant.eq.McDonald%27s,restaurant.eq.Subway)&calories=ge.1000
Enter fullscreen mode Exit fullscreen mode

Page like you mean it. Every call must carry ps and pg leave one out and you get a 406. That's a guardrail: no call can accidentally return the whole table. But correct paging is still on you:

# WRONG-ish: ties in `calories` have no defined order between pages
oy=calories.ds

# RIGHT: add tie-breakers until no two rows compare equal
oy=calories.ds,menu_item_id.as,item_name.as
Enter fullscreen mode Exit fullscreen mode

This dataset is full of ties (and menu_item_id itself repeats across collection years the real key is id + year). Page on an ambiguous order and a row can show up on two pages, or on none. This is true of every paginated API you'll ever use; it just usually bites silently.

The gotchas nobody documents

This is a metered API calls cost money ($1.70 per 1,000 on this dataset, i.e. $0.0017 each). That changes the failure modes in ways worth knowing before you write a loop:

1. Mistakes that reach the dataset are billed. An unknown column, a missing pg, a filter value with a bare SQL keyword these come back as 417/406/400 with the metering headers, logged and charged like a success. Validate the URL before you send it; resending a broken request just repeats the charge. (Refusals at the door no key, POST to a read-only API, ps over 100 cost nothing.)

2. Only two errors are worth retrying. 429 means rate limit: wait Retry-After seconds, send the same request. 402 means your wallet or a spend cap is done: no amount of retrying helps. Everything else repeats until you change the request.

3. Cache hits are billed too. Repeat an identical request within moments and apisix-cache-status: HIT tells you it came from cache at full price. Same for a ~rand order returning the same rows. If you need fresh rows, change the request, don't re-send it.

4. A call costs the same for 1 row or 100. So ps=100 always, and ask for only the columns you need with fc.

5. The platform types the data; it doesn't interpret it. calories being null in ~9,900 rows, a calories_text column holding ranges like "110-550", a recorded 0 meaning something different from both that's the source dataset, served faithfully. The serving layer has no business silently "fixing" public data, so those decisions ship in your client code.

The worst case is bounded, though: the wallet is prepaid and spending stops at its balance. A runaway loop ends when the wallet does, not when the invoice arrives.

Result

From zero to answering real questions against 65,219 rows: one key, four or five curl commands, no download, no loader, no deploy. The question from the top most caloric chain-restaurant items took one request.

Honest limitations, so you can decide if this fits your use case:

  • Read-only. GET only; anything else is refused.
  • No aggregates. There's no count(*) or sum() grouping answers "which groups exist", not "how many in each". If you need analytics, pull the rows (at 100/page) and aggregate client-side, or this isn't your tool.
  • 100 rows per page, 512 KB per response. Bulk export is not the use case; the CSV download still wins there.
  • It's in beta. Screens and details may shift.

If you want the free route instead: the raw dataset is on NYC Open Data with a Socrata API you'll just be doing your own typing, hosting and semantics. I wrote up what that actually involves (the null-vs-text calories, the duplicate IDs, one genuine two-chains-one-ID bug) in a longer companion piece.

Disclosure: I'm the co-founder/CTO of Littlebit Labs API Bay is our product. The API Bay Bible documents the full query model, every refusal, and the publisher side (uploading your own table and pricing it), if you want to go deeper.

What's your default move when a side project needs public data download and load, or find an API? Curious what the split looks like.

https://littlebitlabs.com
https://selectstar.wtf

Top comments (0)