Difference between revisions of "DuckDB Cheatsheet"
(+ dates + typos) |
|||
(4 intermediate revisions by the same user not shown) | |||
Line 5: | Line 5: | ||
We assume that you've downloaded the <code>food.parquet</code> file locally, and that you've launched DuckDB CLI. (see [[Reusing Open Food Facts Data#Parquet file hosted on Hugging Face .28beta.29|Reusing Open Food Facts Data]]). | We assume that you've downloaded the <code>food.parquet</code> file locally, and that you've launched DuckDB CLI. (see [[Reusing Open Food Facts Data#Parquet file hosted on Hugging Face .28beta.29|Reusing Open Food Facts Data]]). | ||
− | + | === Modify the type of output === | |
By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see [https://duckdb.org/docs/api/cli/output_formats.html DuckDB documentation about them]. | By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see [https://duckdb.org/docs/api/cli/output_formats.html DuckDB documentation about them]. | ||
− | + | === Describe the data === | |
It's interesting to have the whole list of fields and their characteristics. | It's interesting to have the whole list of fields and their characteristics. | ||
.mode box -- comment: change display mode to nice table | .mode box -- comment: change display mode to nice table | ||
Line 18: | Line 18: | ||
At the end of the page, we provide the full list of fields based on this query. | At the end of the page, we provide the full list of fields based on this query. | ||
− | + | === Creating a new parquet file for my country === | |
COPY ( | COPY ( | ||
SELECT * FROM 'food.parquet' | SELECT * FROM 'food.parquet' | ||
Line 25: | Line 25: | ||
TO 'off-canada.parquet' (FORMAT 'parquet', COMPRESSION 'zstd'); | TO 'off-canada.parquet' (FORMAT 'parquet', COMPRESSION 'zstd'); | ||
− | + | === Display product name (for main language) of all products of with category <code>en:butters</code>=== | |
SELECT | SELECT | ||
code, | code, | ||
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' |
− | + | WHERE list_contains (categories_tags, 'en:butters') | |
− | WHERE | + | LIMIT 10; |
− | |||
− | LIMIT | ||
− | |||
┌───────────────┬─────────────────────────────────────────────────────────────────────┐ | ┌───────────────┬─────────────────────────────────────────────────────────────────────┐ | ||
│ code │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │ | │ code │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │ | ||
Line 55: | Line 52: | ||
└─────────────────────────────────────────────────────────────────────────────────────┘ | └─────────────────────────────────────────────────────────────────────────────────────┘ | ||
− | + | === Who are the biggest contributors? === | |
''Ex: Top 10 best contributors in OFF'' | ''Ex: Top 10 best contributors in OFF'' | ||
Line 81: | Line 78: | ||
└──────────────────────────────────────┘ | └──────────────────────────────────────┘ | ||
− | + | === 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 110: | Line 107: | ||
└──────────────────┘ | └──────────────────┘ | ||
− | + | === Search for a string in a list === | |
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' | |
− | + | WHERE regexp_matches(array_to_string(allergens_tags, ','), 'fr:') | |
− | + | GROUP BY allergens_tags | |
− | + | 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] │ | ||
+ | └─────┴─────────────────┴──────────────────────────────────────────────┘ | ||
+ | |||
+ | === Playing with dates === | ||
+ | |||
+ | ==== Last products created in 2023? ==== | ||
+ | <pre> | ||
+ | SELECT code | ||
+ | , strftime(epoch_ms(created_t * 1000), '%Y-%m-%dT%H:%M:%SZ') AS iso_date | ||
+ | , creator | ||
+ | , 'https://world.openfoodfacts.org/product/' || code as url | ||
+ | FROM read_parquet('food.parquet') | ||
+ | WHERE true | ||
+ | AND created_t < epoch (TIMESTAMP '2024-01-01 00:00:00') | ||
+ | AND created_t > epoch (TIMESTAMP '2023-12-31 00:00:00') | ||
+ | ORDER BY iso_date desc | ||
+ | LIMIT 7; | ||
+ | ┌───────────────┬──────────────────────┬──────────────────┬───────────────────────────────────────────────────────┐ | ||
+ | │ code │ iso_date │ creator │ url │ | ||
+ | ├───────────────┼──────────────────────┼──────────────────┼───────────────────────────────────────────────────────┤ | ||
+ | │ 0699058466038 │ 2023-12-31T23:59:09Z │ smoothie-app │ https://world.openfoodfacts.org/product/0699058466038 │ | ||
+ | │ 2030503844885 │ 2023-12-31T23:58:38Z │ kiliweb │ https://world.openfoodfacts.org/product/2030503844885 │ | ||
+ | │ 3551720206854 │ 2023-12-31T23:56:51Z │ kiliweb │ https://world.openfoodfacts.org/product/3551720206854 │ | ||
+ | │ 17705288 │ 2023-12-31T23:54:45Z │ smoothie-app │ https://world.openfoodfacts.org/product/17705288 │ | ||
+ | │ 72734452 │ 2023-12-31T23:52:06Z │ insectproductadd │ https://world.openfoodfacts.org/product/72734452 │ | ||
+ | │ 3495040348439 │ 2023-12-31T23:51:38Z │ kiliweb │ https://world.openfoodfacts.org/product/3495040348439 │ | ||
+ | │ 8801114140741 │ 2023-12-31T23:50:58Z │ foodless │ https://world.openfoodfacts.org/product/8801114140741 │ | ||
+ | └───────────────┴──────────────────────┴──────────────────┴───────────────────────────────────────────────────────┘ | ||
+ | </pre> | ||
==== How many images where uploaded in 2024? ==== | ==== How many images where uploaded in 2024? ==== | ||
Line 141: | Line 177: | ||
└─────────────┘ | └─────────────┘ | ||
− | ==== Annex: parquet schema | + | === Count things === |
+ | |||
+ | CASE allows to filter things when counting, eg. a given value in a list. Example: | ||
+ | |||
+ | -- count the number of products (nb), | ||
+ | -- the number of products with en:ingredients-photo-selected (nb_ing_photo_selected), | ||
+ | -- the number of products with en:ingredients-photo-to-be-selected (nb_ing_photo_to_be_selected), | ||
+ | -- the number of products with ingredients-photo-selected or ingredients-photo-to-be-selected (nb_ing_photo_tags), | ||
+ | -- and the number of products lacking either en:ingredients-photo-selected | ||
+ | -- or en:ingredients-photo-to-be-selected (ing_photo_tags_lacking) | ||
+ | SELECT | ||
+ | count(*) as nb, | ||
+ | count(CASE WHEN 'en:ingredients-photo-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_selected, | ||
+ | count(CASE WHEN 'en:ingredients-photo-to-be-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_to_be_selected, | ||
+ | nb_ing_photo_selected + nb_ing_photo_to_be_selected as nb_ing_photo_tags, | ||
+ | nb - nb_ing_photo_tags as ing_photo_tags_lacking | ||
+ | FROM read_parquet('food.parquet'); | ||
+ | ┌─────────┬───────────────────────┬─────────────────────────────┬───────────────────┬────────────────────────┐ | ||
+ | │ nb │ nb_ing_photo_selected │ nb_ing_photo_to_be_selected │ nb_ing_photo_tags │ ing_photo_tags_lacking │ | ||
+ | ├─────────┼───────────────────────┼─────────────────────────────┼───────────────────┼────────────────────────┤ | ||
+ | │ 3621956 │ 951676 │ 1949239 │ 2900915 │ 721041 │ | ||
+ | └─────────┴───────────────────────┴─────────────────────────────┴───────────────────┴────────────────────────┘ | ||
+ | |||
+ | UNNEST allows to play with nested fields such as `images`, `nutriments`, `packagings`, etc. | ||
+ | |||
+ | -- Top 10 languages for the ingredients' images | ||
+ | SELECT n.unnest.key, count(code) as nb | ||
+ | FROM (SELECT code, images FROM read_parquet('food.parquet')) as f, | ||
+ | UNNEST(f.images) as n | ||
+ | WHERE n.unnest.key like 'ingredients_%' | ||
+ | GROUP BY n.unnest.key | ||
+ | ORDER BY nb DESC | ||
+ | LIMIT 10; | ||
+ | ┌────────────────┬────────┐ | ||
+ | │ key │ nb │ | ||
+ | ├────────────────┼────────┤ | ||
+ | │ ingredients_fr │ 574588 │ | ||
+ | │ ingredients_en │ 209511 │ | ||
+ | │ ingredients_de │ 106001 │ | ||
+ | │ ingredients_es │ 77712 │ | ||
+ | │ ingredients_it │ 28467 │ | ||
+ | │ ingredients_nl │ 18756 │ | ||
+ | │ ingredients_pt │ 10820 │ | ||
+ | │ ingredients_pl │ 9465 │ | ||
+ | │ ingredients_ro │ 7115 │ | ||
+ | │ ingredients_sv │ 6744 │ | ||
+ | └────────────────┴────────┘ | ||
+ | |||
+ | |||
+ | === Annex: parquet schema === | ||
{| class="wikitable" | {| class="wikitable" | ||
! column_name !! column_type | ! column_name !! column_type |
Latest revision as of 13:34, 26 March 2025
The Parquet file dump of the Open Food Facts database contains a variety of data in different format, such as string, list, struct, timestamp,...
In this cheat sheet, we will learn how to use the Parquet dump using DuckDB to perform data analysis, by solving some of the most common use-cases asked by the community.
We assume that you've downloaded the food.parquet
file locally, and that you've launched DuckDB CLI. (see Reusing Open Food Facts Data).
Modify the type of output
By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see DuckDB documentation about them.
Describe the data
It's interesting to have the whole list of fields and their characteristics.
.mode box -- comment: change display mode to nice table describe select * from 'food.parquet';
You can also restrict listing to column_name and column_type:
select column_name, column_type from (describe select * from 'food.parquet');
At the end of the page, we provide the full list of fields based on this query.
Creating a new parquet file for my country
COPY ( SELECT * FROM 'food.parquet' WHERE 'en:canada' IN food.countries_tags ) TO 'off-canada.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');
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:butters') 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 │ └──────────────────┘
Search for a string in a list
Eg. search in the list allergen_tags
values containing fr:
. You have to convert the array into a string using the array_to_string
.
SELECT count(code) as nb, ANY_VALUE(code), allergens_tags FROM 'food.parquet' WHERE regexp_matches(array_to_string(allergens_tags, ','), 'fr:') GROUP BY allergens_tags 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] │ └─────┴─────────────────┴──────────────────────────────────────────────┘
Playing with dates
Last products created in 2023?
SELECT code , strftime(epoch_ms(created_t * 1000), '%Y-%m-%dT%H:%M:%SZ') AS iso_date , creator , 'https://world.openfoodfacts.org/product/' || code as url FROM read_parquet('food.parquet') WHERE true AND created_t < epoch (TIMESTAMP '2024-01-01 00:00:00') AND created_t > epoch (TIMESTAMP '2023-12-31 00:00:00') ORDER BY iso_date desc LIMIT 7; ┌───────────────┬──────────────────────┬──────────────────┬───────────────────────────────────────────────────────┐ │ code │ iso_date │ creator │ url │ ├───────────────┼──────────────────────┼──────────────────┼───────────────────────────────────────────────────────┤ │ 0699058466038 │ 2023-12-31T23:59:09Z │ smoothie-app │ https://world.openfoodfacts.org/product/0699058466038 │ │ 2030503844885 │ 2023-12-31T23:58:38Z │ kiliweb │ https://world.openfoodfacts.org/product/2030503844885 │ │ 3551720206854 │ 2023-12-31T23:56:51Z │ kiliweb │ https://world.openfoodfacts.org/product/3551720206854 │ │ 17705288 │ 2023-12-31T23:54:45Z │ smoothie-app │ https://world.openfoodfacts.org/product/17705288 │ │ 72734452 │ 2023-12-31T23:52:06Z │ insectproductadd │ https://world.openfoodfacts.org/product/72734452 │ │ 3495040348439 │ 2023-12-31T23:51:38Z │ kiliweb │ https://world.openfoodfacts.org/product/3495040348439 │ │ 8801114140741 │ 2023-12-31T23:50:58Z │ foodless │ https://world.openfoodfacts.org/product/8801114140741 │ └───────────────┴──────────────────────┴──────────────────┴───────────────────────────────────────────────────────┘
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 │ └─────────────┘
Count things
CASE allows to filter things when counting, eg. a given value in a list. Example:
-- count the number of products (nb), -- the number of products with en:ingredients-photo-selected (nb_ing_photo_selected), -- the number of products with en:ingredients-photo-to-be-selected (nb_ing_photo_to_be_selected), -- the number of products with ingredients-photo-selected or ingredients-photo-to-be-selected (nb_ing_photo_tags), -- and the number of products lacking either en:ingredients-photo-selected -- or en:ingredients-photo-to-be-selected (ing_photo_tags_lacking) SELECT count(*) as nb, count(CASE WHEN 'en:ingredients-photo-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_selected, count(CASE WHEN 'en:ingredients-photo-to-be-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_to_be_selected, nb_ing_photo_selected + nb_ing_photo_to_be_selected as nb_ing_photo_tags, nb - nb_ing_photo_tags as ing_photo_tags_lacking FROM read_parquet('food.parquet'); ┌─────────┬───────────────────────┬─────────────────────────────┬───────────────────┬────────────────────────┐ │ nb │ nb_ing_photo_selected │ nb_ing_photo_to_be_selected │ nb_ing_photo_tags │ ing_photo_tags_lacking │ ├─────────┼───────────────────────┼─────────────────────────────┼───────────────────┼────────────────────────┤ │ 3621956 │ 951676 │ 1949239 │ 2900915 │ 721041 │ └─────────┴───────────────────────┴─────────────────────────────┴───────────────────┴────────────────────────┘
UNNEST allows to play with nested fields such as `images`, `nutriments`, `packagings`, etc.
-- Top 10 languages for the ingredients' images SELECT n.unnest.key, count(code) as nb FROM (SELECT code, images FROM read_parquet('food.parquet')) as f, UNNEST(f.images) as n WHERE n.unnest.key like 'ingredients_%' GROUP BY n.unnest.key ORDER BY nb DESC LIMIT 10; ┌────────────────┬────────┐ │ key │ nb │ ├────────────────┼────────┤ │ ingredients_fr │ 574588 │ │ ingredients_en │ 209511 │ │ ingredients_de │ 106001 │ │ ingredients_es │ 77712 │ │ ingredients_it │ 28467 │ │ ingredients_nl │ 18756 │ │ ingredients_pt │ 10820 │ │ ingredients_pl │ 9465 │ │ ingredients_ro │ 7115 │ │ ingredients_sv │ 6744 │ └────────────────┴────────┘
Annex: parquet schema
column_name | column_type |
---|---|
additives_n | INTEGER |
additives_tags | VARCHAR[] |
allergens_tags | VARCHAR[] |
brands_tags | VARCHAR[] |
brands | VARCHAR |
categories | VARCHAR |
categories_tags | VARCHAR[] |
checkers_tags | VARCHAR[] |
ciqual_food_name_tags | VARCHAR[] |
cities_tags | VARCHAR[] |
code | VARCHAR |
compared_to_category | VARCHAR |
complete | INTEGER |
completeness | FLOAT |
correctors_tags | VARCHAR[] |
countries_tags | VARCHAR[] |
created_t | BIGINT |
creator | VARCHAR |
data_quality_errors_tags | VARCHAR[] |
data_quality_info_tags | VARCHAR[] |
data_quality_warnings_tags | VARCHAR[] |
data_sources_tags | VARCHAR[] |
ecoscore_data | VARCHAR |
ecoscore_grade | VARCHAR |
ecoscore_score | INTEGER |
ecoscore_tags | VARCHAR[] |
editors | VARCHAR[] |
emb_codes_tags | VARCHAR[] |
emb_codes | VARCHAR |
entry_dates_tags | VARCHAR[] |
food_groups_tags | VARCHAR[] |
generic_name | STRUCT(lang VARCHAR, "text" VARCHAR)[] |
images | STRUCT("key" VARCHAR, imgid INTEGER, sizes STRUCT("100" STRUCT(h INTEGER, w INTEGER), "200" STRUCT(h INTEGER, w INTEGER), "400" STRUCT(h INTEGER, w INTEGER), "full" STRUCT(h INTEGER, w INTEGER)), uploaded_t BIGINT, uploader VARCHAR)[] |
informers_tags | VARCHAR[] |
ingredients_analysis_tags | VARCHAR[] |
ingredients_from_palm_oil_n | INTEGER |
ingredients_n | INTEGER |
ingredients_original_tags | VARCHAR[] |
ingredients_percent_analysis | INTEGER |
ingredients_tags | VARCHAR[] |
ingredients_text | STRUCT(lang VARCHAR, "text" VARCHAR)[] |
ingredients_with_specified_percent_n | INTEGER |
ingredients_with_unspecified_percent_n | INTEGER |
ingredients_without_ciqual_codes_n | INTEGER |
ingredients_without_ciqual_codes | VARCHAR[] |
ingredients | VARCHAR |
known_ingredients_n | INTEGER |
labels_tags | VARCHAR[] |
labels | VARCHAR |
lang | VARCHAR |
languages_tags | VARCHAR[] |
last_edit_dates_tags | VARCHAR[] |
last_editor | VARCHAR |
last_image_t | BIGINT |
last_modified_by | VARCHAR |
last_modified_t | BIGINT |
last_updated_t | BIGINT |
link | VARCHAR |
main_countries_tags | VARCHAR[] |
manufacturing_places_tags | VARCHAR[] |
manufacturing_places | VARCHAR |
max_imgid | INTEGER |
minerals_tags | VARCHAR[] |
misc_tags | VARCHAR[] |
new_additives_n | INTEGER |
no_nutrition_data | BOOLEAN |
nova_group | INTEGER |
nova_groups_tags | VARCHAR[] |
nova_groups | VARCHAR |
nucleotides_tags | VARCHAR[] |
nutrient_levels_tags | VARCHAR[] |
nutriments | STRUCT("name" VARCHAR, "value" FLOAT, "100g" FLOAT, serving FLOAT, unit VARCHAR, prepared_value FLOAT, prepared_100g FLOAT, prepared_serving FLOAT, prepared_unit VARCHAR)[] |
nutriscore_grade | VARCHAR |
nutriscore_score | INTEGER |
nutrition_data_per | VARCHAR |
obsolete | BOOLEAN |
origins_tags | VARCHAR[] |
origins | VARCHAR |
owner_fields | STRUCT(field_name VARCHAR, "timestamp" BIGINT)[] |
owner | VARCHAR |
packagings_complete | BOOLEAN |
packaging_recycling_tags | VARCHAR[] |
packaging_shapes_tags | VARCHAR[] |
packaging_tags | VARCHAR[] |
packaging_text | STRUCT(lang VARCHAR, "text" VARCHAR)[] |
packaging | VARCHAR |
packagings | STRUCT(material VARCHAR, number_of_units BIGINT, quantity_per_unit VARCHAR, quantity_per_unit_unit VARCHAR, quantity_per_unit_value VARCHAR, recycling VARCHAR, shape VARCHAR, weight_measured FLOAT)[] |
photographers | VARCHAR[] |
popularity_key | BIGINT |
popularity_tags | VARCHAR[] |
product_name | STRUCT(lang VARCHAR, "text" VARCHAR)[] |
product_quantity_unit | VARCHAR |
product_quantity | VARCHAR |
purchase_places_tags | VARCHAR[] |
quantity | VARCHAR |
rev | INTEGER |
scans_n | INTEGER |
serving_quantity | VARCHAR |
serving_size | VARCHAR |
states_tags | VARCHAR[] |
stores_tags | VARCHAR[] |
stores | VARCHAR |
traces_tags | VARCHAR[] |
unique_scans_n | INTEGER |
unknown_ingredients_n | INTEGER |
unknown_nutrients_tags | VARCHAR[] |
vitamins_tags | VARCHAR[] |
with_non_nutritive_sweeteners | INTEGER |
with_sweeteners | INTEGER |