Line 55: |
Line 55: |
| ==== Import CSV in PostgreSQL ==== | | ==== Import CSV in PostgreSQL ==== |
| See this article: https://blog-postgresql.verite.pro/2018/12/21/import-openfoodfacts.html (in french, but should be understandable with Google Translator). | | See this article: https://blog-postgresql.verite.pro/2018/12/21/import-openfoodfacts.html (in french, but should be understandable with Google Translator). |
| + | |
| + | Alternative way - feel free to use a project from github: https://github.com/ArchiMageAlex/off_converter |
| | | |
| ==== Import CSV to SQLite ==== | | ==== Import CSV to SQLite ==== |
Line 105: |
Line 107: |
| If you don't have enough disk space to uncompress the .gz file, you can use zcat directly on the compressed file. Example: | | If you don't have enough disk space to uncompress the .gz file, you can use zcat directly on the compressed file. Example: |
| $ zcat openfoodfacts-products.jsonl.gz | jq -r '[.code,.product_name] | @csv' # output CSV data containing code,product_name | | $ zcat openfoodfacts-products.jsonl.gz | jq -r '[.code,.product_name] | @csv' # output CSV data containing code,product_name |
| + | |
| + | ==== Filtering JSONL export with jq ==== |
| + | Filtering a specific country: |
| + | $ zcat openfoodfacts-products.jsonl.gz | jq '. | select(.countries_tags[]? == "en:germany")' |
| + | |
| + | The previous command produces a json output containing all the products sold in Germany. If you want a JSONL output, add -c parameter. |
| + | $ zcat openfoodfacts-products.jsonl.gz | jq -c '. | select(.countries_tags[]? == "en:germany")' |
| + | |
| + | You can add multiple filters and export the result to a CSV file. For example, here is a command that 1. selects products having the Nutri-Score computed and belonging to the TOP 90% most scanned products in 2020, and 2. exports barcode (<code>code</code>) and number of scans (<code>scans_n</code>) as a CSV file. |
| + | $ zcat openfoodfacts-products.jsonl.gz | jq -r '. | select(.misc_tags[]? == "en:nutriscore-computed" and .popularity_tags[]? == "top-90-percent-scans-2020") | [.code,.scans_n] | @csv' > displayed.ns.in.top90.2020.world.csv |
| + | |
| + | These operations can be quite long (more than 10 minutes depending on your computer and your selection). |