DuckDB Cheatsheet

The OFF database contains a variety of data in different format, such as TEXTS, LISTS, STRUCT, DATES, and even more...

But no worries, DuckDB handles any type of data! Learn how to use the database by solving the most common use-cases asked by the community.

Display product name (for main language) of all products of with category en:butters

SELECT
 code,
 unnest(
   list_filter (product_name, x -> x.lang = = 'main')
 ) ['text']
FROM
 'food.parquet'
WHERE
 list_contains (categories_tags, 'en:milks')
LIMIT
 10;
┌───────────────┬─────────────────────────────────────────────────────────────────────┐
│     code      │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
│    varchar    │                               varchar                               │
├───────────────┼─────────────────────────────────────────────────────────────────────┤
│ 0011110807625 │ Quality food centers, salted butter                                 │
│ 0011110808998 │ Unsalted Butter                                                     │
│ 0011110842640 │ Ralphs, Salted Sticks, Butter                                       │
│ 0011110852878 │ Salted butter                                                       │
│ 0011110854384 │ Qfc, unsalted butter                                                │
│ 0011110862600 │ King Soopers City Market, Salted Butter                             │
│ 0011110863256 │ Salted Butter                                                       │
│ 0011110863270 │ Unsalted Butter                                                     │
│ 0011110893017 │ Salted Butter                                                       │
│ 0011110893055 │ Unsalted butter sticks                                              │
├───────────────┴─────────────────────────────────────────────────────────────────────┤
│ 10 rows                                                                   2 columns │
└─────────────────────────────────────────────────────────────────────────────────────┘

Who are the biggest contributors?

Ex: Top 10 best contributors in OFF

SELECT creator, count(*) AS count 
FROM read_parquet('food.parquet')
GROUP BY creator 
ORDER BY count DESC 
LIMIT 10;
┌────────────────────────────┬─────────┐
│          creator           │  count  │
│          varchar           │  int64  │
├────────────────────────────┼─────────┤
│ kiliweb                    │ 1883982 │
│ foodvisor                  │  208270 │
│ openfoodfacts-contributors │  199459 │
│ usda-ndb-import            │  169554 │
│ org-database-usda          │  134461 │
│ prepperapp                 │  110841 │
│ macrofactor                │   92148 │
│ foodless                   │   87839 │
│ smoothie-app               │   74339 │
│ inf                        │   37999 │
├────────────────────────────┴─────────┤
│ 10 rows                    2 columns │
└──────────────────────────────────────┘

Number of added products per year

--entry_dates_tags is a list of texts. We take the value at position 3: the year
SELECT entry_dates_tags[3] AS year, count(*) AS count 
FROM read_parquet('food.parquet') 
GROUP BY year 
ORDER BY year DESC;
┌─────────┬────────┐
│  year   │ count  │
│ varchar │ int64  │
├─────────┼────────┤
│ 2024    │ 463257 │
│ 2023    │ 361240 │
│ 2022    │ 598326 │
│ 2021    │ 514052 │
│ 2020    │ 466269 │
│ 2019    │ 364272 │
│ 2018    │ 318010 │
│ 2017    │ 279737 │
│ 2016    │  44618 │
│ 2015    │  33968 │
│ 2014    │  12892 │
│ 2013    │   9587 │
│ 2012    │   4267 │
│ 1970    │      3 │
│         │      1 │
├─────────┴────────┤
│     15 rows      │
└──────────────────┘

How many images where uploaded in 2024?

SELECT
  SUM(images_filter_count) AS image_count
FROM
  (
    SELECT
      len (
        list_filter (
          images,
          x -> struct_extract (x, 'uploaded_t') > epoch (TIMESTAMP '2024-01-01 00:00:00')
          AND struct_extract (x, 'uploaded_t') < epoch (TIMESTAMP '2025-01-01 00:00:00')
        )
      ) as images_filter_count
    FROM
      'food.parquet'
  );
┌─────────────┐
│ image_count │
│   int128    │
├─────────────┤
│      716207 │
└─────────────┘