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

About

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

Example:

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

The analytics SQL

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

select 
  -- the ->> is an accessor operator for the context json property
  context->>'country' as country, 
  count(1) 
from read_json_auto(
    -- the path may use a Glob pattern to select more than one file
    'C:\data\*\analytics-consent_shown-*.jsonl', 
    -- defined that this is a JSONL file
    format=newline_delimited)
group by country
order by count(1) desc