Line 31:
Line 31:
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 list_contains (categories_tags, 'en:butters')
−
WHERE
+
LIMIT 10;
−
list_contains (categories_tags, 'en:butters')
−
LIMIT
−
10;
┌───────────────┬─────────────────────────────────────────────────────────────────────┐
┌───────────────┬─────────────────────────────────────────────────────────────────────┐
│ code │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
│ code │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
Line 82:
Line 79:
==== Number of added products per year ====
==== Number of added products per year ====
−
--entry_dates_tags is a list of texts. We take the value at position 3: the 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
SELECT entry_dates_tags[3] AS year, count(*) AS count
FROM read_parquet('food.parquet')
FROM read_parquet('food.parquet')
Line 113:
Line 110:
Eg. search in the list <code>allergen_tags</code> values containing <code>fr:</code>. You have to convert the array into a string using the <code>array_to_string</code>.
Eg. search in the list <code>allergen_tags</code> values containing <code>fr:</code>. You have to convert the array into a string using the <code>array_to_string</code>.
SELECT count(code) as nb, ANY_VALUE(code), allergens_tags
SELECT count(code) as nb, ANY_VALUE(code), allergens_tags
−
FROM 'food.parquet'
+
FROM 'food.parquet'
−
where regexp_matches(array_to_string(allergens_tags, ','), 'fr:')
+
WHERE regexp_matches(array_to_string(allergens_tags, ','), 'fr:')
−
group by allergens_tags
+
GROUP BY allergens_tags
−
order by nb desc
+
ORDER BY nb DESC
+
LIMIT 7;
+
┌─────┬─────────────────┬──────────────────────────────────────────────┐
+
│ nb │ any_value(code) │ allergens_tags │
+
├─────┼─────────────────┼──────────────────────────────────────────────┤
+
│ 337 │ 0205004001439 │ [en:gluten, fr:avoine] │
+
│ 131 │ 3445731105896 │ [fr:non] │
+
│ 116 │ 3175681118911 │ [en:gluten, en:milk, en:soybeans, fr:avoine] │
+
│ 104 │ 3175681140776 │ [en:gluten, en:nuts, fr:avoine] │
+
│ 104 │ 20936365 │ [en:gluten, en:milk, fr:avoine] │
+
│ 56 │ 3222476727046 │ [en:gluten, fr:avoine, fr:avoine] │
+
│ 55 │ 3700389705158 │ [fr:non-renseigne] │
+
└─────┴─────────────────┴──────────────────────────────────────────────┘
==== How many images where uploaded in 2024? ====
==== How many images where uploaded in 2024? ====