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:

Ex: 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      │                         product_name                         │
│    varchar    │                           varchar                            │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ 0000234022960 │ Croissants pur beurre                                        │
│ 0002000013363 │ Grandes galettes au beurre                                   │
│ 0008421369416 │ Brioche Pur Beurre                                           │
│ 00089739      │ Beurre doux                                                  │
│ 0016073123478 │ Beurre d'arachide en poudre                                  │
│       ·       │              ·                                               │
│       ·       │              ·                                               │
│       ·       │              ·                                               │
│ 3560071532741 │ Beurre demi-sel À teneur réduite en matière grasse 60% Mat…  │
│ 77646187      │ Beurre sans sel                                              │
│ 3596710524006 │ Cosmia crème corps nourrissante - beurre de macadamia + ni…  │
│ 3596710523986 │ Cosmia crème mains nourrissante - au beurre de karité et à…  │
│ 4820154481786 │ Petit Beurre                                                 │
├───────────────┴──────────────────────────────────────────────────────────────┤
│ 13791 rows (10 shown)                                              2 columns │ 
└──────────────────────────────────────────────────────────────────────────────┘ 

• Search by category

Ex: search for all product belonging to "plant-based food" and "cereals" categories

SELECT code, product_name 
FROM read_parquet('food.parquet')
WHERE categories ILIKE '%plant-based foods%' AND categories ILIKE '%cereal%';
┌───────────────┬───────────────────────────────────────────┐
│     code      │               product_name                │
│    varchar    │                  varchar                  │
├───────────────┼───────────────────────────────────────────┤
│ 0000127534587 │ Today's temptations, lithuanian rye bread │
│ 0000236555909 │ Bakers Best, White Bread                  │
│ 0000236598784 │ Bakers Best, Rye Bread                    │
│ 0000946909078 │ Augason Farms, Vital Wheat Gluten         │
│ 0003026400168 │ Grainaissance, mochi, cashew-date         │
│       ·       │         ·                                 │
│       ·       │         ·                                 │
│       ·       │         ·                                 │
│ 0003003400513 │ Bread, hearty rye                         │
│ 0003003400510 │ Stone Ground Wheat Bread                  │
│ 0003003400514 │ Homestyle potato bread                    │
│ 20043131      │ Sliced Plain Bagel                        │
│ 7020656900144 │ Havregranola Jordbær og bringebær         │
├───────────────┴───────────────────────────────────────────┤
│ 51377 rows (10 shown)                           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 │
└──────────────────────────────────────┘

DATE & ARRAY fields

• 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      │
└──────────────────┘

DICTIONNARY fields

• Extract macro-nutriments

Nutriments are stored as a dictionnary {"proteins": "4.2", "proteins_unit": "g", ...}

--TRY_CAST attributes the FLOAT type to the extracted value, while preventing errors
SET threads to 4; --avoid out of memory issue by limiting the number of threads
SELECT
  code,
  product_name,
  TRY_CAST(nutriments -> 'proteins' AS FLOAT) as proteins, 
  TRY_CAST(nutriments -> 'fat' AS FLOAT) as fat,
  TRY_CAST(nutriments -> 'carbohydrates' AS FLOAT) as carbohydrates
FROM read_parquet('food.parquet')
WHERE 
  (nutriments ->> 'proteins_unit') = 'g' AND 
  (nutriments ->> 'fat_unit') = 'g' AND 
  (nutriments ->> 'carbohydrates_unit') = 'g';
┌───────────────┬──────────────────────────────────────────────┬───────────┬───────┬───────────────┐
│     code      │                 product_name                 │ proteins  │  fat  │ carbohydrates │
│    varchar    │                   varchar                    │   float   │ float │     float     │
├───────────────┼──────────────────────────────────────────────┼───────────┼───────┼───────────────┤
│ 0000101209159 │ Véritable pâte à tartiner noisettes chocol…  │       8.0 │  48.0 │          36.0 │
│ 0000105000011 │ Lagg's, chamomile herbal tea                 │       0.0 │   0.0 │          70.0 │
│ 0000105000042 │ Lagg's, herbal tea, peppermint               │       0.0 │   0.0 │          1.47 │
│ 0000105000059 │ Linden Flowers Tea                           │       0.0 │   0.0 │         53.33 │
│ 0000105000073 │ Herbal Tea, Hibiscus                         │     66.67 │   0.0 │          60.0 │
│       ·       │      ·                                       │        ·  │    ·  │            ·  │
│       ·       │      ·                                       │        ·  │    ·  │            ·  │
│       ·       │      ·                                       │        ·  │    ·  │            ·  │
│ 4711252014016 │ Peanup choco                                 │      15.8 │  32.0 │          34.6 │
│ 18785784      │ Not required                                 │       1.4 │  16.0 │          17.0 │
│ 4994860304117 │ 塩羊羮                                       │       3.9 │   0.5 │          60.6 │
│ 5601312079989 │ Mini delícias (cobertas com chocolate de l…  │ 6.3333335 │  24.0 │          64.0 │
│ 8017104001033 │ Peperoni grigliati                           │       1.8 │   0.1 │           7.6 │
├───────────────┴──────────────────────────────────────────────┴───────────┴───────┴───────────────┤
│ 2396027 rows (10 shown)                                                                5 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘

Enjoy playing with the database!