Article

How to Model JSON Event Data With SQL

By Gavin Somers · 10 min read · Published 3 July 2026

• Analytics Engineering
• Data Modelling
• SQL
• Reporting Trust

Semi-structured event data is easy to query badly.

A table may look simple at first:

Here is a small synthetic raw data sample using anonymised healthcare-style events:

event_name,event_timestamp,json_payload
patient_message_sent,2026-03-01T09:07:00Z,"{""messageId"":""msg_1000"",""organisationId"":101,""userId"":5003,""messageType"":""Email"",""messageLength"":49,""countAttachment"":0,""eventVersion"":1}"
patient_message_sent,2026-03-01T10:18:00Z,"{""messageId"":""msg_1001"",""organisationId"":102,""userId"":5004,""messageType"":""NHS App"",""messageLength"":62,""countAttachment"":0,""eventVersion"":1}"
patient_video_invite_sent,2026-03-01T11:29:00Z,"{""Id"":""vid_1002"",""Type"":""Video"",""OrganisationID"":103,""UserID"":5005,""Messagelength"":88,""eventVersion"":1}"
patient_message_read,2026-03-01T12:40:00Z,"{""messageId"":""msg_1003"",""organisationId"":104,""userId"":5006,""messageType"":""Email"",""messageLength"":88,""countAttachment"":0,""eventVersion"":1}"

You can download the larger sample CSV here: healthcare-event-data.csv.

The trap is answering every question directly from the raw JSON.

That works for one query. It does not work well for a reporting system.

If the source payload changes field casing, mixes event versions, stores numeric values as JSON strings, or uses different keys for related event types, the raw table is not a safe analytical interface.

The first job is to create a normalised event model.

Then answer questions from that model.

The modelling principle

Before answering the business question directly, create a staging layer from the JSON payload so the downstream query is readable, reusable, and less brittle.

In practical terms:

1. Preserve the raw event table.
2. Use exploratory analysis to inspect the source shape.
3. Determine the grain of the raw data.
4. Extract JSON fields into typed columns.
5. Handle schema drift deliberately.
6. Build question-specific models from the cleaned event model.

This is the same principle whether the source is healthcare messaging, product analytics, support tickets, CRM activity, or operational workflow data.

The raw event is evidence.

The model is interpretation.

Exploratory analysis

Exploratory analysis is separate from answering business questions.

At this stage, the goal is not to calculate a polished KPI. The goal is to understand what the raw table represents, which fields are reliable, where the source schema drifts, and what grain the data appears to be stored at.

Start by profiling the source

Do not begin with the final metric.

Start with the shape of the data.

select *
from event_data
limit 20;

Then check the row count and date range.

select
  count(*) as row_count,
  min(substr(event_timestamp, 1, 10)) as min_event_date,
  max(substr(event_timestamp, 1, 10)) as max_event_date
from event_data;

Check the event types.

select
  event_name,
  count(*) as row_count
from event_data
group by 1
order by row_count desc;

For JSON payloads, inspect the keys before assuming the schema is stable.

In SQLite, json_each over an object returns one row per key.

select
  j.key,
  count(*) as rows_with_key
from event_data e,
json_each(e.json_payload) as j
group by 1
order by rows_with_key desc;

This is where source problems usually show up.

You may find that messageId becomes MessageID, organisationId becomes OrganisationID, or a different event type uses Id instead of messageId.

That is not just a syntax nuisance.

It is a modelling decision.

Determine the grain of the raw data

Before building any model, decide what one row in the raw table represents.

For event data, the expected grain is often one row per source event. But do not assume that. Check whether a message, invite, response, or workflow action can appear more than once.

Start by creating a candidate event ID.

with stg_events as (

  select
    event_name,
    event_timestamp,

    coalesce(
      json_extract(json_payload, '$.messageId'),
      json_extract(json_payload, '$.MessageID'),
      json_extract(json_payload, '$.Id')
    ) as event_id
  from event_data
)

select
  count(*) as row_count,
  count(distinct event_id) as distinct_event_ids,
  count(*) - count(distinct event_id) as possible_duplicate_rows
from stg_events;

Then inspect repeated IDs.

with stg_events as (

  select
    event_name,
    event_timestamp,

    coalesce(
      json_extract(json_payload, '$.messageId'),
      json_extract(json_payload, '$.MessageID'),
      json_extract(json_payload, '$.Id')
    ) as event_id
  from event_data
)

select
  event_id,
  count(*) as row_count,
  count(distinct event_name) as event_name_count,
  min(event_timestamp) as first_seen_at,
  max(event_timestamp) as last_seen_at
from stg_events
where event_id is not null
group by 1
having count(*) > 1
order by row_count desc;

If the raw table is one row per event, downstream models can aggregate from that event grain.

If the raw table is one row per delivery attempt, update, recipient, attachment, or event version, the model needs a different grain and probably a deduplication or rollup step.

This is one of the most important exploratory questions because it controls what count(*) means.

Extract JSON fields safely

The simplest JSON extraction looks like this:

json_extract(json_payload, '$.messageId')

But production-style event data often needs fallback logic.

coalesce(
  json_extract(json_payload, '$.messageId'),
  json_extract(json_payload, '$.MessageID'),
  json_extract(json_payload, '$.Id')
) as message_id

Cast values into analytical types.

cast(json_extract(json_payload, '$.messageLength') as integer) as message_length

Standardise timestamps into useful reporting grains.

date(substr(event_timestamp, 1, 10)) as event_date,
date(substr(event_timestamp, 1, 10), 'start of month') as event_month,
datetime(substr(event_timestamp, 1, 19)) as event_at

If the payload is a flat JSON object, json_extract is usually enough.

If the payload contains nested arrays, use json_each against the array path.

select
  e.event_name,
  json_extract(item.value, '$.id') as item_id
from event_data e,
json_each(e.json_payload, '$.items') as item;

Check field completeness

Before building business metrics, check whether the fields you need are reliably populated.

with stg_events as (

  select
    coalesce(
      json_extract(json_payload, '$.messageId'),
      json_extract(json_payload, '$.MessageID'),
      json_extract(json_payload, '$.Id')
    ) as message_id,

    coalesce(
      json_extract(json_payload, '$.organisationId'),
      json_extract(json_payload, '$.OrganisationID')
    ) as organisation_id,

    coalesce(
      json_extract(json_payload, '$.userId'),
      json_extract(json_payload, '$.UserID')
    ) as patient_id,

    coalesce(
      json_extract(json_payload, '$.messageType'),
      json_extract(json_payload, '$.Type')
    ) as message_type,

    coalesce(
      json_extract(json_payload, '$.messageLength'),
      json_extract(json_payload, '$.Messagelength')
    ) as message_length,

    json_extract(json_payload, '$.countAttachment') as attachment_count
  from event_data
)

select
  sum(message_id is null) as missing_message_id,
  sum(organisation_id is null) as missing_organisation_id,
  sum(patient_id is null) as missing_patient_id,
  sum(message_type is null) as missing_message_type,
  sum(message_length is null) as missing_message_length,
  sum(attachment_count is null) as missing_attachment_count
from stg_events;

This check turns hidden source variation into visible modelling risk.

If a field is missing for one event type but required for another, that might be fine.

If a field is missing unpredictably inside the same event type, the metric needs a caveat, exclusion rule, or upstream fix.

Build a reusable event model first

This is the main pattern.

The raw table is not the analytical interface.

The reusable interface is a typed, cleaned event model with one row per source event.

with raw_events as (

  select
    event_name,
    event_timestamp,
    json_payload
  from event_data
),

stg_events as (

  select
    event_name,
    datetime(substr(event_timestamp, 1, 19)) as event_at,
    date(substr(event_timestamp, 1, 10)) as event_date,
    date(substr(event_timestamp, 1, 10), 'start of month') as event_month,

    coalesce(
      json_extract(json_payload, '$.messageId'),
      json_extract(json_payload, '$.MessageID'),
      json_extract(json_payload, '$.Id')
    ) as event_id,

    cast(coalesce(
      json_extract(json_payload, '$.organisationId'),
      json_extract(json_payload, '$.OrganisationID')
    ) as integer) as organisation_id,

    cast(coalesce(
      json_extract(json_payload, '$.userId'),
      json_extract(json_payload, '$.UserID')
    ) as integer) as patient_id,

    coalesce(
      json_extract(json_payload, '$.messageType'),
      json_extract(json_payload, '$.Type')
    ) as channel,

    cast(coalesce(
      json_extract(json_payload, '$.messageLength'),
      json_extract(json_payload, '$.Messagelength')
    ) as integer) as content_length,

    cast(json_extract(json_payload, '$.countAttachment') as integer) as attachment_count,
    cast(json_extract(json_payload, '$.eventVersion') as integer) as event_version,

    case
      when event_name = 'patient_message_sent' then 'patient_message'
      when event_name = 'patient_video_invite_sent' then 'video_invite'
      when event_name = 'patient_message_read' then 'message_read'
      else 'other'
    end as event_type
  from raw_events
),

final as (

  select
    event_id,
    event_type,
    event_name,
    event_at,
    event_date,
    event_month,
    organisation_id,
    patient_id,
    channel,
    content_length,
    attachment_count,
    event_version
  from stg_events
)

select *
from final;

This model does not answer every question by itself.

It gives every downstream question a safer starting point.

Example questions

Once the source has been explored and the reusable event model is clear, move to question-specific models.

Each example below states the business question and the grain of the final answer. That grain should be different from the raw event grain when the question asks for a daily, monthly, patient-level, organisation-level, or channel-level view.

Example 1: message volume over time

Question:

What is message volume doing over time?

Grain of the final model: one row per date.

with stg_events as (

  select
    event_name,
    date(substr(event_timestamp, 1, 10)) as event_date
  from event_data
),

final as (

  select
    event_date,
    count(*) as message_count
  from stg_events
  where event_name = 'patient_message_sent'
  group by 1
)

select *
from final
order by event_date;

In a mature project, this query would use the cleaned event model rather than repeat the raw extraction logic.

The point is the grain: daily message volume is one row per date, not one row per raw event.

Example 2: messages by patient over time

Question:

How many messages did each patient send over time?

Grain of the final model: one row per patient per month.

with stg_events as (

  select
    event_name,
    date(substr(event_timestamp, 1, 10), 'start of month') as event_month,

    cast(coalesce(
      json_extract(json_payload, '$.userId'),
      json_extract(json_payload, '$.UserID')
    ) as integer) as patient_id,

    cast(coalesce(
      json_extract(json_payload, '$.messageLength'),
      json_extract(json_payload, '$.Messagelength')
    ) as integer) as message_length
  from event_data
),

final as (

  select
    patient_id,
    event_month,
    count(*) as message_count,
    avg(message_length) as avg_message_length
  from stg_events
  where event_name = 'patient_message_sent'
  group by 1, 2
)

select *
from final
order by event_month, patient_id;

This query makes the analytical grain explicit.

That matters because many metric mistakes happen when an event-level table is accidentally treated like a patient-level table.

Example 3: organisation activity

Question:

Which organisations are most active?

Grain of the final model: one row per organisation.

with stg_events as (

  select
    event_name,

    cast(coalesce(
      json_extract(json_payload, '$.organisationId'),
      json_extract(json_payload, '$.OrganisationID')
    ) as integer) as organisation_id,

    cast(coalesce(
      json_extract(json_payload, '$.userId'),
      json_extract(json_payload, '$.UserID')
    ) as integer) as patient_id,

    coalesce(
      json_extract(json_payload, '$.messageId'),
      json_extract(json_payload, '$.MessageID'),
      json_extract(json_payload, '$.Id')
    ) as message_id
  from event_data
),

final as (

  select
    organisation_id,
    count(*) as event_count,
    count(distinct patient_id) as distinct_patients,
    count(distinct message_id) as distinct_messages
  from stg_events
  where organisation_id is not null
  group by 1
)

select *
from final
order by event_count desc;

Notice that event_count, distinct_patients, and distinct_messages are different measures.

They may rank organisations differently.

That is not a problem as long as the question is clear.

Example 4: channel mix

Question:

What proportion of messages are SMS versus email?

Grain of the final model: one row per month and channel.

with stg_events as (

  select
    date(substr(event_timestamp, 1, 10), 'start of month') as event_month,

    coalesce(
      json_extract(json_payload, '$.messageType'),
      json_extract(json_payload, '$.Type')
    ) as channel
  from event_data
),

monthly_channel_counts as (

  select
    event_month,
    channel,
    count(*) as message_count
  from stg_events
  group by 1, 2
),

final as (

  select
    event_month,
    channel,
    message_count,
    1.0 * message_count / sum(message_count) over (
      partition by event_month
    ) as share_of_month
  from monthly_channel_counts
)

select *
from final
order by event_month, channel;

The window function avoids a second join back to monthly totals.

More importantly, the query states the denominator: share of messages within the month.

Example 5: attachment usage

Question:

How often do messages include attachments?

Grain of the final model: one row per month.

with stg_events as (

  select
    event_name,
    date(substr(event_timestamp, 1, 10), 'start of month') as event_month,
    cast(json_extract(json_payload, '$.countAttachment') as integer) as attachment_count
  from event_data
),

final as (

  select
    event_month,
    count(*) as message_count,
    sum(case when attachment_count > 0 then 1 else 0 end) as messages_with_attachment,
    1.0 * sum(case when attachment_count > 0 then 1 else 0 end) / count(*) as attachment_rate,
    avg(attachment_count) as avg_attachment_count
  from stg_events
  where event_name = 'patient_message_sent'
  group by 1
)

select *
from final
order by event_month;

This is also a good example of where event-type filtering matters.

If attachment fields are only populated for message events, including unrelated event types may create misleading nulls.

Example 6: data quality by event version

Question:

Did the source schema change, and did that affect data quality?

Grain of the final model: one row per event version.

with stg_events as (

  select
    cast(json_extract(json_payload, '$.eventVersion') as integer) as event_version,

    coalesce(
      json_extract(json_payload, '$.messageId'),
      json_extract(json_payload, '$.MessageID'),
      json_extract(json_payload, '$.Id')
    ) as message_id,

    coalesce(
      json_extract(json_payload, '$.organisationId'),
      json_extract(json_payload, '$.OrganisationID')
    ) as organisation_id,

    coalesce(
      json_extract(json_payload, '$.userId'),
      json_extract(json_payload, '$.UserID')
    ) as patient_id,

    coalesce(
      json_extract(json_payload, '$.messageLength'),
      json_extract(json_payload, '$.Messagelength')
    ) as message_length
  from event_data
),

final as (

  select
    event_version,
    count(*) as row_count,
    sum(message_id is null) as missing_message_id,
    sum(organisation_id is null) as missing_organisation_id,
    sum(patient_id is null) as missing_patient_id,
    sum(message_length is null) as missing_message_length
  from stg_events
  group by 1
)

select *
from final
order by event_version;

This is the query that connects technical profiling to reporting trust.

If a metric changes when the source event version changes, the dashboard may be reflecting a tracking change rather than a real operational change.

How this translates to dbt

In dbt, split the work into layers instead of keeping everything in one query.

Useful tests:

• not_null on event ID, event timestamp, and patient ID where applicable.
• unique on event ID if the source guarantees uniqueness.
• Accepted values on event_type and channel.
• Freshness checks on the raw source.
• Reconciliation checks where raw included rows equal staged valid rows plus excluded rows.

This is where exploratory SQL becomes analytics engineering.

The work moves from “can I answer this once?” to “can the business safely reuse this answer?”

A recovery pattern when stuck

When working with unfamiliar JSON, do not try to solve the full model from memory.

Recover the shape step by step.

First, inspect one payload.

select
  json_payload
from event_data
limit 1;

Then extract one field.

select
  json_extract(json_payload, '$.userId') as user_id
from event_data
limit 10;

Then handle casing or version drift.

select
  coalesce(
    json_extract(json_payload, '$.userId'),
    json_extract(json_payload, '$.UserID')
  ) as patient_id
from event_data
limit 10;

Then build the staging CTE.

The goal is not to remember every JSON function instantly.

The goal is to inspect the source, normalise the fields, make the grain explicit, and show good modelling judgement.

The wider reporting lesson

JSON event data makes the problem visible, but the lesson is broader.

Source systems store operational events in the shape needed to run the process.

Reporting needs stable concepts, controlled grain, tested fields, and clear exclusions.

If those decisions stay hidden inside one-off queries, every downstream dashboard inherits the same fragility.

If they are made explicit in a staged event model, the business gets a reusable path from raw source data to trusted reporting.

For the broader modelling context, read Data Modelling for Reporting Trust and Source-to-Report Lineage Explained. If this model feeds a semantic layer, read Looker, dbt, and the Semantic Layer Gate.