When I work with JSON or other semi structured payloads, I keep the raw payload and extract stable fields separately. I recommend versioned schemas so flexibility does not turn into unsearchable chaos.
Flexible payload, stable signal
Semi structured data works when you preserve flexibility without letting every query become a JSON archaeology project.
How JSON becomes usable product data
Semi structured data works when you preserve flexibility without letting every query become a JSON archaeology project.
Databases that fit this shape
When I talk about "Databases that fit this shape", I am checking whether Semi Structured Data Architecture can be traced back, trusted, and used by someone making a decision.
MongoDB, DynamoDB, Cosmos DB, Firestore, BigQuery JSON, Snowflake VARIANT, OpenSearch, Elasticsearch, lakehouse tables.
Cloud building blocks
| Provider | Typical services |
|---|---|
| AWS | API Gateway, Kinesis, S3, Glue, DynamoDB, Athena, OpenSearch. |
| Azure | Event Hubs, Data Factory, Cosmos DB, Synapse, AI Search. |
| Google Cloud | Pub/Sub, Dataflow, Cloud Storage, Firestore, BigQuery JSON, Vertex AI Search. |
A known industry pattern
My recommendation in this JSON pattern is to keep the raw payload available, because today's ignored field can become tomorrow's product signal.
Amazon Dynamo is the public reference for large scale key value and document style thinking: the Dynamo paper explains how Amazon designed for high availability and flexible item access for shopping cart style workloads.
Where this shows up in real work
What I learnt around "Where this shows up in real work" is that Semi Structured Data Architecture needs ownership and freshness as much as storage.
A checkout request contains stable fields such as customer id and order id, plus optional experiment, coupon, device, and delivery instructions. The pipeline stores the full JSON, extracts trusted columns, and indexes searchable fields for operations.
Query or pipeline example
I use "Query or pipeline example" to keep Semi Structured Data Architecture grounded in a real system, because abstract patterns are too easy to agree with and too hard to operate.
insert into clean.checkout_events
select
json_value(payload, '$.orderId') as order_id,
json_value(payload, '$.device.type') as device_type,
json_value(payload, '$.coupon.code') as coupon_code,
try_cast(json_value(payload, '$.schemaVersion') as int) as schema_version,
payload as raw_payload
from raw.checkout_events
where event_name = 'checkout_submitted'
and json_value(payload, '$.orderId') is not null
and try_cast(json_value(payload, '$.schemaVersion') as int) >= 3;The load keeps the raw JSON but accepts only events with an order id and a supported schema version. That gives flexibility without letting bad payloads into clean tables.
Watch these edges
- Version the payload contract.
- Separate flexible raw data from trusted analytical columns.
- Do not let optional fields become invisible business rules.