一般社団法人 全国個人事業主支援協会

COLUMN コラム

「SaaSでありがちな JSONB + 集計」を、具体的なテーブル定義 → データ例 → 集計クエリでまとめます。
（PostgreSQL前提。JSONB + GINインデックスを使うパターン）

 

例1: イベントログ（JSONB）から「日別・機能別」集計

テーブル

CREATE TABLE event_logs (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
user_id uuid NOT NULL,
event_type text NOT NULL, -- 'click', 'purchase', 'login' など
occurred_at timestamptz NOT NULL,
props jsonb NOT NULL -- 追加属性はJSONBに逃がす
);-- JSONB検索を速くする（contains系に効く）
CREATE INDEX event_logs_props_gin ON event_logs USING gin (props);-- 時系列検索も多いので
CREATE INDEX event_logs_tenant_time ON event_logs (tenant_id, occurred_at);

データ例（propsに機能名や金額など）

INSERT INTO event_logs (tenant_id, user_id, event_type, occurred_at, props)
VALUES
('11111111-1111-1111-1111-111111111111','aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa','click', now() - interval '1 day',
'{"feature":"dashboard","button":"export","plan":"pro"}'),
('11111111-1111-1111-1111-111111111111','bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb','purchase',now() - interval '1 day',
'{"feature":"billing","amount":1200,"currency":"JPY","plan":"pro"}'),
('11111111-1111-1111-1111-111111111111','aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa','click', now(),
'{"feature":"billing","button":"change_card","plan":"pro"}');

集計①: 日別 × feature別 のイベント数

SELECT
date_trunc('day', occurred_at) AS day,
props->>'feature' AS feature,
count(*) AS events
FROM event_logs
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
AND occurred_at >= now() - interval '7 days'
GROUP BY 1, 2
ORDER BY 1, 2;

集計②: 日別売上（purchaseの amount をJSONBから取り出してsum）

SELECT
date_trunc('day', occurred_at) AS day,
sum( (props->>'amount')::numeric ) AS revenue
FROM event_logs
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
AND event_type = 'purchase'
AND occurred_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

例2: テナントごとの「カスタム項目（JSONB）」で集計（SaaSあるある）

SaaSは顧客ごとに「カスタムフィールド」が増える：
例）CRMの顧客（customer）の attrs に自由項目

テーブル

CREATE TABLE customers (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
name text NOT NULL,
attrs jsonb NOT NULL, -- {"industry":"IT","employees":50,"region":"Kanto"}
created_at timestamptz NOT NULL DEFAULT now()
);CREATE INDEX customers_attrs_gin ON customers USING gin (attrs);
CREATE INDEX customers_tenant ON customers (tenant_id);

集計①: 業種（industry）別の件数

SELECT
attrs->>'industry' AS industry,
count(*) AS customers
FROM customers
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
GROUP BY 1
ORDER BY customers DESC;

集計②: 従業員数（employees）のレンジ別（数値として扱う）

SELECT
CASE
WHEN (attrs->>'employees')::int < 10 THEN '1-9'
WHEN (attrs->>'employees')::int < 50 THEN '10-49'
WHEN (attrs->>'employees')::int < 200 THEN '50-199'
ELSE '200+'
END AS size_bucket,
count(*) AS customers
FROM customers
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
AND attrs ? 'employees' -- キー存在チェック
GROUP BY 1
ORDER BY 1;

例3: JSONB配列（items）を展開して集計（注文の明細）

テーブル

CREATE TABLE orders (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
ordered_at timestamptz NOT NULL,
payload jsonb NOT NULL
-- payload例:
-- {"order_id":"x","items":[{"sku":"A","qty":2,"price":300},{"sku":"B","qty":1,"price":500}]}
);
CREATE INDEX orders_tenant_time ON orders (tenant_id, ordered_at);

集計: SKU別の販売数量と売上（配列を展開）

SELECT
item->>'sku' AS sku,
sum( (item->>'qty')::int ) AS total_qty,
sum( ((item->>'qty')::int) * ((item->>'price')::numeric) ) AS revenue
FROM orders
CROSS JOIN LATERAL jsonb_array_elements(payload->'items') AS item
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
AND ordered_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY revenue DESC;
The following two tabs change content below.

小久保 暁人

最新記事 by 小久保 暁人 (全て見る)

この記事をシェアする

  • Twitterでシェア
  • Facebookでシェア
  • LINEでシェア
記事一覧

関連記事

8月の報告
8月の報告
山登り
山登り
2024年12月31日
2024年12月31日




全体の人気記事

カテゴリー