JSON data modelling - prayagupa/rdb GitHub Wiki

create table event(
  event_id int primary key,
  event JSON
);

insert into event values(1, '{"event_type": "ad_sent", "ad_id": 1, "user_device_id": 1}');
insert into event values(2, '{"event_type": "ad_opened", "ad_id": 1, "user_device_id": 1, "opened_at": "2019-06-10"}');
insert into event values(3, '{"event_type": "ad_seen", "ad_id": 1, "user_device_id": 1, "seen_at": "2019-06-10"}');

mysql> select * from event;
+----------+-----------------------------------------------------------------------------------------+
| event_id | event                                                                                   |
+----------+-----------------------------------------------------------------------------------------+
|        1 | {"ad_id": 1, "event_type": "ad_sent", "user_device_id": 1}                              |
|        2 | {"ad_id": 1, "opened_at": "2019-06-10", "event_type": "ad_opened", "user_device_id": 1} |
|        3 | {"ad_id": 1, "seen_at": "2019-06-10", "event_type": "ad_seen", "user_device_id": 1}     |
+----------+-----------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select event->"$.ad_id", event->"$.user_device_id" from event;
+------------------+---------------------------+
| event->"$.ad_id" | event->"$.user_device_id" |
+------------------+---------------------------+
| 1                | 1                         |
| 1                | 1                         |
| 1                | 1                         |
+------------------+---------------------------+
3 rows in set (0.00 sec)


-- search by event_type (present on all)
mysql> select event->"$.ad_id" adv_id, event->"$.user_device_id" user from event where event->"$.event_type" = "ad_sent";
+--------+------+
| adv_id | user |
+--------+------+
| 1      | 1    |
+--------+------+
1 row in set (0.00 sec)

-- search by "seen_at" which is only present for seen events
mysql> select event->"$.ad_id" adv_id, event->"$.user_device_id" user from event where event->"$.seen_at" = "2019-06-10";
+--------+------+
| adv_id | user |
+--------+------+
| 1      | 1    |
+--------+------+
1 row in set (0.01 sec)

https://dev.mysql.com/doc/refman/8.0/en/char.html

mysql> select distinct(warehouse) from Inventory;
+-----------+
| warehouse |
+-----------+
| De Moines |
| Seattle   |
| Luyata    |
+-----------+
3 rows in set (0.01 sec)

mysql> with w as (select warehouse, min(qty) qty from Inventory group by warehouse) select w.warehouse, w.qty from w;
+-----------+------+
| warehouse | qty  |
+-----------+------+
| De Moines |   88 |
| Seattle   |   99 |
| Luyata    |   11 |
+-----------+------+
3 rows in set (0.01 sec)

mysql> with w as (select warehouse, min(qty) qty from Inventory group by warehouse) select i.warehouse, i.sku, i.qty from w join Inventory i on w.warehouse = i.warehouse and w.qty=i.qty;
+-----------+-------+------+
| warehouse | sku   | qty  |
+-----------+-------+------+
| De Moines | sku-1 |   88 |
| Seattle   | sku-2 |   99 |
| Luyata    | sku-3 |   11 |
+-----------+-------+------+
3 rows in set (0.00 sec)

index in timestamp with time zone,

CREATE INDEX event__event_tstz__inverse_idx
ON eventstream (event_tstz DESC);

If its just timestamp,

CREATE INDEX event__event_ts__idx
ON eventstream ((event_ts::DATE));