Structured vs Semi-Structured vs Unstructured Data — Differences Explained Simply

-- Structured: every row has exactly these columns, these types
CREATE TABLE orders (
    order_id    INT            NOT NULL PRIMARY KEY,
    customer_id INT            NOT NULL REFERENCES customers(id),
    amount      DECIMAL(10,2)  NOT NULL CHECK (amount > 0),
    status      VARCHAR(20)    NOT NULL CHECK (status IN ('pending','shipped','delivered','cancelled')),
    created_at  TIMESTAMP      NOT NULL DEFAULT NOW(),
    shipped_at  TIMESTAMP      NULL  -- nullable: only set when shipped
);

-- Rigid schema enables fast, indexed queries:
SELECT
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'delivered'
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 10;

-- Adding a field requires a migration:
ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(20);
-- Every row now has this column (NULL for old rows)

// Semi-structured: self-describing, but flexible
// Different products can have completely different field sets:
{
  "product_id": "prod-001",
  "type": "laptop",
  "name": "MacBook Pro 16",
  "specs": {
    "cpu": "Apple M3 Pro",
    "ram_gb": 18,
    "storage_tb": 0.5,
    "display": "16.2-inch Liquid Retina XDR"
  },
  "tags": ["electronics", "apple", "portable"],
  "variants": null
}

// vs a different product type with completely different fields:
{
  "product_id": "prod-002",
  "type": "clothing",
  "name": "Running Shoes",
  "specs": {
    "size_us": "10",
    "color": "Black/White",
    "material": "Mesh upper, rubber sole",
    "weight_oz": 9.1
  },
  "variants": [
    {"size_us": "9", "in_stock": true},
    {"size_us": "10", "in_stock": false},
    {"size_us": "11", "in_stock": true}
  ]
}

// MongoDB stores both in the same "products" collection —
// no schema migration needed when product types change

-- PostgreSQL JSONB: structured table columns + flexible JSON column
CREATE TABLE events (
    id         BIGSERIAL    PRIMARY KEY,
    event_type VARCHAR(50)  NOT NULL,
    user_id    INT          NOT NULL REFERENCES users(id),
    created_at TIMESTAMP    DEFAULT NOW(),
    metadata   JSONB        -- flexible semi-structured data, indexed
);

-- GIN index for fast JSON field queries:
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

-- Insert structured + semi-structured data
INSERT INTO events (event_type, user_id, metadata) VALUES
('purchase', 1, '{"product_id": "prod-001", "amount": 999, "coupon": "SAVE10", "currency": "USD"}'),
('login',    1, '{"ip": "192.168.1.1", "device": "iPhone 15", "country": "US", "2fa": true}'),
('search',   2, '{"query": "laptop", "results_count": 42, "filters": {"price_max": 2000}}');

-- Query JSON fields with full SQL power:
SELECT user_id,
       metadata->>'product_id'       AS product,
       (metadata->>'amount')::numeric AS amount
FROM events
WHERE event_type = 'purchase'
  AND (metadata->>'amount')::numeric > 500
  AND metadata->>'currency' = 'USD'
ORDER BY amount DESC;

-- Query nested JSON (->  returns JSON, ->> returns text):
SELECT * FROM events
WHERE metadata->'filters'->>'price_max' IS NOT NULL;  -- has a price filter

-- Aggregate on JSON field:
SELECT metadata->>'country' AS country, COUNT(*) AS logins
FROM events
WHERE event_type = 'login'
GROUP BY metadata->>'country'
ORDER BY logins DESC;

# Customer review — pure unstructured text
review = """
The laptop is incredibly fast and the battery life is amazing.
However, the keyboard feels a bit mushy and the price is too high for what you get.
Would still recommend it for developers who need the performance.
"""

# 1. Sentiment analysis — extract structured signal from unstructured text
from transformers import pipeline

sentiment_analyzer = pipeline("sentiment-analysis",
                               model="distilbert-base-uncased-finetuned-sst-2-english")
result = sentiment_analyzer(review)
# → [{'label': 'POSITIVE', 'score': 0.76}]  (mixed review, but leans positive)

# 2. Named entity recognition — extract product names, features, etc.
ner = pipeline("ner", grouped_entities=True)
entities = ner(review)
# → [{'entity_group': 'PRODUCT', 'word': 'laptop', ...},
#    {'entity_group': 'FEATURE', 'word': 'keyboard', ...}]

# 3. Embeddings — convert text to vector for semantic search
from sentence_transformers import SentenceTransformer
import numpy as np

model = SentenceTransformer('all-MiniLM-L6-v2')
embedding = model.encode(review)
# → numpy array of 384 floats — the semantic "fingerprint"

# Store in vector database for semantic search:
# "find reviews mentioning keyboard issues" → similarity search
# Returns this review even if query words don't exactly match

# 4. LLM extraction — structured data from unstructured text
import anthropic

client = anthropic.Anthropic()
response = client.messages.create(
    model="claude-sonnet-4-6",
    max_tokens=500,
    messages=[{
        "role": "user",
        "content": f"""Extract from this review as JSON:
- overall_sentiment: positive/negative/mixed
- pros: list of mentioned positives
- cons: list of mentioned negatives
- would_recommend: true/false
- product_category: laptop/phone/etc

Review: {review}"""
    }]
)
# Returns structured JSON extracted from unstructured text