JSON
Searching Values Inside a MySQL JSON Array Efficiently
MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS and JSON_SEARCH compared on real tables, with a multi-valued index, and the matching rules for numbers and strings.
8 min read
Tag
how-to
590 posts
XML
Stopping the Billion Laughs Attack in Your XML Parser
A 547-byte file that expands to 30 million characters. How lxml, ElementTree and Java refused it, what the limits are, and the settings that keep them on.
6 min read
CSV
Joining Two CSV Files With SQL in DuckDB Without a Database
Join two CSV files on a key with plain SQL in DuckDB. Covers a 2M by 250k row join, keys that differ by case and spaces, left and anti joins, and type errors.
8 min read
CSV
Converting a CSV to Parquet With DuckDB in One Line
One COPY statement turns a CSV into Parquet in DuckDB. See the schema it writes, fix zip and money columns, compare compression codecs, and verify the result.
8 min read
JSON
Using JSON_TABLE to Turn MySQL JSON Into Rows and Columns
JSON_TABLE in MySQL 8 turns JSON arrays into typed rows. Syntax, nested paths, default and error clauses, the silent NULLs, and timings on 250,000 items.
8 min read
CSV
DuckDB Skipping Bad Rows While Reading a Messy CSV
Use ignore_errors and store_rejects in DuckDB to read past bad CSV rows, then query reject_errors to see which lines were dropped and why.
9 min read
YAML
Validating Config YAML With JSON Schema in CI Pipelines
Write a JSON Schema for a YAML config, run check-jsonschema in CI, get every error with a path, and know that the loader decides what NO and 3.10 are.
7 min read
CSV
Reading a CSV With Mixed Date Formats in DuckDB Safely
A date column with five formats makes DuckDB fall back to text. Parse it with try_strptime and coalesce, list the failures, and avoid the day-first trap.
7 min read
ExcelGuide
Understanding Excel Errors When Data Comes From Code
A map of Excel errors seen when scripts write or read workbooks: file and format messages, cell error codes, library exceptions and a debugging order.
19 min read
JSON
Indexing a MySQL JSON Field With a Generated Column
MySQL cannot index a JSON column directly. Add a generated column, index it, and learn the collation, NULL and cast traps that change query results.
8 min read
CSV
Querying a Folder of CSV Files With a Glob in DuckDB
Read every CSV in a folder as one table in DuckDB with a glob, add the file name as a column, and handle files whose columns differ in count or order.
8 min read
XML
Reading Office Open XML From Docx and Xlsx Files in Python
A docx or xlsx is a zip of XML files. Open it with zipfile, read the paragraphs, runs and tables of a docx and the shared strings and cells of an xlsx.
7 min read