liveBook · Manning

Json extension

As explained in DuckDB in Action

The DuckDB JSON extension is an extension that provides functions for creating, reading, manipulating, and structurally interpreting JSON data.

DuckDB’s JSON extension reads, creates, and manipulates JSON while inferring nested structures into DuckDB vectors. It supports querying JSON Lines files directly. section 5.3

Summarize expected goals

After flattening shot records, aggregate expected goals and goals by team: filter season 2022, calculate round(sum(xG), 2), count goal results, group, and order by totalXG.

How DuckDB in Action covers it

How deep it goes
Explains inference, array unnesting, recursive unpacking, STRUCT casting, flattening, and aggregation, but not JSON function cataloging.
Examples
Uses Premier League shot files, home-and-away arrays, flattened shot records, and expected-goals team rankings.
What it assumes
Assumes basic SQL and introduces nested arrays, STRUCT projection, inferred schemas, and JSON conformity fixes through examples.
What you can do after
Apply DuckDB SQL to inspect, normalize, flatten, and aggregate nested JSON files into queryable views.
How it connects
Connects JSON with JSON Lines, schema inference, arrays, STRUCT types, views, wildcards, and relational aggregation.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.