How to read a nested property in a JSONL format with DuckDb?

Card Puncher Data Processing


This article shows you how you can read a JSONL file with DuckDb that contains JSON with nested property.

The JSON format

The format of each JSON is based on the standard user event JSON data with a context property that gives context to the event


  "context": {
    "country": "FR"
  ...other properties

The analytics SQL

The below SQL will count the number of lines and group them by country.

  -- the ->> is an accessor operator for the context json property
  context->>'country' as country, 
from read_json_auto(
    -- the path may use a Glob pattern to select more than one file
    -- defined that this is a JSONL file
group by country
order by count(1) desc

Share this page:
Follow us:
Task Runner