Line 3:
Line 3:
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:'''
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'''.
+
* '''Display product name (for main language) of all products of with category en:butters'''.
+
SELECT
SELECT
code,
code,
Line 9:
Line 10:
list_filter (product_name, x -> x.lang = = 'main')
list_filter (product_name, x -> x.lang = = 'main')
) ['text']
) ['text']
−
FROM
+
FROM
'food.parquet'
'food.parquet'
−
WHERE
+
WHERE
list_contains (categories_tags, 'en:milks')
list_contains (categories_tags, 'en:milks')
−
LIMIT
+
LIMIT
10;';
10;';
−
┌───────────────┬──────────────────────────────────────────────────────────────┐
+
┌───────────────┬─────────────────────────────────────────────────────────────────────┐
−
│ code │ product_name │
+
│ code │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
−
│ varchar │ varchar │
+
│ varchar │ varchar │
−
├───────────────┼──────────────────────────────────────────────────────────────┤
+
├───────────────┼─────────────────────────────────────────────────────────────────────┤
−
│ 0000234022960 │ Croissants pur beurre │
+
│ 0011110807625 │ Quality food centers, salted butter │
−
│ 0002000013363 │ Grandes galettes au beurre │
+
│ 0011110808998 │ Unsalted Butter │
−
│ 0008421369416 │ Brioche Pur Beurre │
+
│ 0011110842640 │ Ralphs, Salted Sticks, Butter │
−
│ 00089739 │ Beurre doux │
+
│ 0011110852878 │ Salted butter │
−
│ 0016073123478 │ Beurre d'arachide en poudre │
+
│ 0011110854384 │ Qfc, unsalted butter │
−
│ · │ · │
+
│ 0011110862600 │ King Soopers City Market, Salted Butter │
−
│ · │ · │
+
│ 0011110863256 │ Salted Butter │
−
│ · │ · │
+
│ 0011110863270 │ Unsalted Butter │
−
│ 3560071532741 │ Beurre demi-sel À teneur réduite en matière grasse 60% Mat… │
+
│ 0011110893017 │ Salted Butter │
−
│ 77646187 │ Beurre sans sel │
+
│ 0011110893055 │ Unsalted butter sticks │
−
│ 3596710524006 │ Cosmia crème corps nourrissante - beurre de macadamia + ni… │
+
├───────────────┴─────────────────────────────────────────────────────────────────────┤
−
│ 3596710523986 │ Cosmia crème mains nourrissante - au beurre de karité et à… │
+
│ 10 rows 2 columns │
−
│ 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?'''
* '''Who are the biggest contributors?'''
Line 121:
Line 92:
└──────────────────┘
└──────────────────┘
−
===== DICTIONNARY fields =====
+
* '''How many images where uploaded in 2024?'''
−
* '''Extract macro-nutriments'''
+
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'
+
);
−
''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
+
│ image_count │
−
SET threads to 4; --avoid out of memory issue by limiting the number of threads
+
│ int128 │
−
SELECT
+
├─────────────┤
−
code,
+
│ 716207 │
−
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!''