HOW TO Filter Products on Open Food Facts by Country (India) Using DuckDB & Superset

Open Food Facts (OFF) is a crowd-sourced database of food products from around the world. 

Somewhere in the world, a shopper scans the barcode on a packet of biscuits and adds basic product details. Another person photographs a package label for the same product in Hyderabad. A volunteer in France fixes a typo. 

That's Open Food Facts, a volunteer-built, openly licensed database of food products that's continually changing and growing.

Each product is identified by a barcode and carries ingredients, nutrition facts, brands, labels and more. The whole database has millions of products, and let's say you only want the products recorded as being sold or available in one country like India.

There are multiple ways to extract a subset of the OFF database. I have already tried using the daily dump available on Hugging Face through Google Colab and GitHub Actions in the past but it blew my mind that using the OFF deployed Apache Superset, you can execute a SQL query in a browser and pull out a country specific subset of the large OFF dataset and export it as a CSV file within seconds!

DuckDB provides cheap, fast analytics over OFF's Parquet data and Superset provides the interface, sharing and visualization. 

Open Food Facts maintains the Superset instance, the DuckDB database behind it, and the regular import of its Parquet product export (into the food_products table). As a developer, you don't install, load or refresh anything. 

While researching food products from India and periodically retrieving the India-specific subset of the OFF database, I enlisted my AI chatbots to help me craft the SQL query and explain how it works. Here's the query -

If you are new to Superset and DuckDB and excited to know how it all works like I did, read on...

Superset & DuckDB — What’s the Link?

DuckDB is an open-source, in-process analytical database that can query large files such as Parquet directly. It runs as a library inside your program or inside Superset's backend, with no server to install or manage. It stores and processes data in a columnar, vectorized way, which suits "scan lots of rows, aggregate a few columns" workloads (OLAP).

OFF publishes its full product database as a Parquet file, with over 4.7 million products and about 115 columns, some of them nested (lists and structs). Parquet is a file format for storing big tables. Instead of storing data row by row, it stores it column by column (a columnar format) so DuckDB can often read only the columns needed by a query rather than scanning the entire file. If your query only needs 5 of 115 columns, the engine reads just those 5 and skips the rest. That is why analytics on a huge file can still be fast. 

DuckDB can query that data directly with its dialect of SQL so the file behaves like a table. 

Apache Superset is an open-source, web-based business intelligence tool. It does not store data. It lets you run SQL in SQL Lab - the query editor in Superset, and turns results into charts, dashboards and CSV exports. With a DuckDB connection configured, Superset gives anyone with a browser access to the OFF data. 

Superset is just the interface, while DuckDB is the execution engine.

Superset takes some effort to deploy and administer and that aspect has been handled by Open Food Facts. 

Its behavior depends on the SQL dialect of the connected engine (PostgreSQL and DuckDB). I choose DuckDB and queries must be written in DuckDB syntax.

When you type SQL in SQL Lab (Superset's query editor) and press Run, Superset sends your text to DuckDB. DuckDB executes it and returns rows and Superset displays them as a table you can export to CSV.

Before asking anything clever, let's ask DuckDB to show us the floor plan:

PRAGMA table_info('food_products');

This DuckDB command returns every column with its position, name and type. You can review and download the result as a CSV.

The floor plan or the schema reveals an important complication. OFF's Parquet data contains nested fields. Some columns are not simple values:

• countries_tags is a LIST: several values in one cell.
• product_name is a LIST of STRUCTs, each with a lang and a text. A typical result looks like a tangle of JSON:
  [{"lang": "main", "text": "Cassatta"}, {"lang": "en", "text": "Cassatta"}]
  
  The first language entry refers to the "main" name. The data is nested. Ordinary SQL expects one tidy value per cell. DuckDB is happy to unfold these, but you have to ask in its dialect.
• nutriments is a LIST of STRUCTs: name, 100g, serving and more.

Next, DESCRIBE tells us the shape of a table or of a query's output without running the whole thing -

DESCRIBE SELECT unnest(product_name) FROM food_products;

To get a sense of how many records are there for a specific country (India in this example), you can use the COUNT function like this -

SELECT COUNT(*) AS india_products

FROM food_products p

WHERE list_contains(p.countries_tags, 'en:india');

The table food_products holds the OFF data. Each row is one product. Some columns hold lists. For example, the column countries_tags holds a list of country labels. OFF's countries_tags represents countries where a product is sold/available, not necessarily where it was manufactured.

The label for India is en:india. The query considers only the rows that have this label. Swap the values to en:france or en:usa or any other country using OFF's language:value tag convention if you're interested only in products from those countries.

When I ran this COUNT query on 4th October 2026, there were 24,588 records carrying the en:india country tag. The number will change as contributors add or update products.

Upon running the query to get values for a specified set of columns for each product (basic details of each product including ingredients, nutrient values, NOVA and Nutri-Score) in the India subset of OFF database, the output (5.9MB CSV export) was returned within 5 seconds!

One of the main highlights of the output is the health rating based on the popular NOVA and Nutri-Score frameworks generated by OFF:

• NOVA group: a 1-to-4 score for how heavily processed a food is, where 1 means unprocessed or minimally processed and 4 means ultra-processed. 
• Nutri-Score: a letter grade from A (most favourable nutrition profile) to E (least favourable), calculated from the nutrients per 100 g. 
  
  NOVA and Nutri-Score have no unit.

How the query works

The query has three main parts -

1. FROM food_products p selects the table. The letter p is a short name for the table.
2. WHERE list_contains(p.countries_tags, 'en:india') is the filter. It keeps a row only when the list contains en:india.
3. SELECT selects the columns for the result.

The function list_filter examines each item in a list. It keeps the items that match the condition. 

(list_filter(p.nutriments, x -> x.name = 'energy-kcal')[1])."100g" AS energy_kcal,

→ find the item in the nutriments list whose name is energy-kcal, take the first matching item, and read its 100g field (the value per 100 grams). The quotes around "100g" are needed because a name starting with a digit isn't a normal identifier. This repeats for each nutrient you want.

The x -> ... part is a lambda, a tiny inline function meaning "for each item x, test this".  

The text x -> x.lang = 'main' is the condition. It keeps the item with the language main. 

The text [1] selects the first item. In DuckDB, the first item is number 1.

.text and ."100g" reach inside a struct to grab one field.

The function COALESCE gives the first value that is not empty. If no main text exists, it gives the first available text.

'https://in.openfoodfacts.org/product/' || p.code || '/' AS URL

→ the symbol || joins text. The query uses it to make the product URL from the barcode, turning a barcode like 8906084521276 into a clickable product page.

The icon labeled with the tooltip "Download as CSV," as shown in the screenshot, allows you to export the query results as a CSV file.

Differences from standard SQL

• Standard SQL does not have list_contains and list_filter. These are DuckDB functions.
• Standard SQL does not have the x -> condition syntax in these functions.
• Standard SQL does not use the .text syntax to read a field in a struct.
• PRAGMA is not part of standard SQL, and databases that offer it use it differently.

A query written for DuckDB can fail in PostgreSQL.

Epilogue: Why this matters

Open Food Facts provides a setup where you can query a large public dataset from a browser without first downloading the entire dataset or installing a database server.

It's amazing to me that with an open dataset, an open-source engine (DuckDB), an open-source interface (Superset) and curiosity, everything can be inspected, reproduced and improved by anyone, and that includes the data itself. If you spot a wrong ingredient list on one of those product pages, you can fix it, and the next person's query will return your correction.