渡鴉之丘: 在 PGSQL 把 EAV 表格轉成 JSON 欄位與進一步攤平

Entity-Attribute-Value 型態（縮寫 EAV）的資料蠻常出現在實際應用上，最典型的就是資料分析時很多屬性標籤。

但是。。。PGSQL 其實不擅長對付這種屬性的資料。。。第二個無腦建議總是說：想辦法轉成關聯式結構的表格（第一個建議呢～就是建 GIN Index 之類的ㄅ～）

不過，這種屬性的資料正好可以用另一種主流資料儲存型態代換—JSON 型態。正好現在的 Postgres 也很擅長處理 JSON 資料。

另外值得提到的是，PGSQL 並不像 Cassandra 這種 Wide-Column Store 一樣可以開「很多」欄位，因此若 Attribute 標籤種類繁雜，就不是很適合在 PGSQL 開超多欄位的表格來存放。

因此這篇筆記嘗試把 EAV 轉換成 JSON 儲存的方式（測試版本：EDB PGSQL13，不過用到的功能基本上 PGSQL 9.5 以上都可以）。

另外，當標籤的類型很少，可以轉換成一般欄位的話（也就是關聯式表格），這篇筆記也嘗試演練一下～

如果看到這邊～還是不知道窩在講蝦米～請上網查一下 EAV pattern 與 Relational Model～

[Update 2022/12/03] 文末有彩蛋～

首先讓我們先來準備測試資料：因為突然找不到合適的測試資料，只好參考一些範例作一個意思意思。有時 value 的內容會複雜一點（字串/數值混雜），就會變成用 TEXT 型態通吃～

create table test_eav_to_json (name text, attr text, value int);
insert into test_eav_to_json values 
('u1', 'key1', 1), ('u1', 'key2', 2), ('u1', 'key3', 3),
('u2', 'key1', 4), ('u2', 'key2', 5), ('u2', 'key3', 6);

edb=# select * from test_eav_to_json;
 name | attr | value 
------+------+-------
 u1   | key1 |     1
 u1   | key2 |     2
 u1   | key3 |     3
 u2   | key1 |     4
 u2   | key2 |     5
 u2   | key3 |     6
(6 rows)

Time: 1.232 ms
edb=#

這邊提一下PGSQL 不擅長應付的原因

EAV Pattern 並非 Relational Model，先天就直接違背大象本身的存在惹。。無法套用關聯式資料庫的正規劃理論，例如，沒有什麼 foreign key constraint 可以建構
Attribute 通常是重複值的字串內容，這種欄位建立 BTree Index 沒什麼效，通常得建立比較胖的 GIN 看看有沒有顯著效果
Value 內容混雜，極度可能發生字串/數值混合存放，這種時候資料庫抽樣（ANALYZE Table）沒辦法有效使用這種資料。例如，若使用 WHERE 條件過濾數值屬性的內容作運算，資料庫內的抽樣就沒辦法在速度上幫上什麼忙

因此通常會建議 PGSQL 避開 EAV pattern（其他商用資料庫可能有優化，但我只能說目前的 PGSQL 沒有這種機能）。

首先第一步轉成 json：這邊使用 json_object_agg() 函數，可以把 EAV 表格的 Attribute 欄位跟 Value 欄位抽離拆開，變成扁平的 JSON 資料。如此一來就可以把「EAV 資料」轉成「主檔欄位/JSON明細」的混搭版關聯/非結構表格了。

Note：跟這邊使用的函數的孿生兄弟是 jsonb_object_agg() 函數。

edb=# select name, 
  json_object_agg(attr, value) as jsn_kv 
 from test_eav_to_json 
 group by name;
 name |                 jsn_kv                 
------+----------------------------------------
 u1   | { "key1" : 1, "key2" : 2, "key3" : 3 }
 u2   | { "key1" : 4, "key2" : 5, "key3" : 6 }
(2 rows)

Time: 0.579 ms
edb=#

看起來很容易，感覺筆記在這邊就結束惹～

不過事情沒那麼單純～～一般來說在 PGSQL 裡面的 JSON 應用，優先建議 JSONB 型態而非 JSON 型態。但是！若遇到重複 key 的狀況（相同 Entity 但是重複的 Attribute），轉成 JSONB 的話，資料會被吃掉，只剩下一個 Attribute 跟它的 Value。

為了筆記行文方便，我先針對少量 Attribute 的狀況，操作如何轉成關聯式表格。

EAV 轉一般表

對於 Attribute 種類很少的 EAV 資料（這種情形的資料，通常是只是資料來源以這種方式提供，並非欄位數量問題），可以利用 JSON 型態作為中間步驟，進一步轉成關聯式表格（表格直轉橫）。

基於上面使用 json_object_agg()，接著只要把扁平 JSON 的 key/value 轉成表格欄位就可以了～

這邊的全部步驟如下：

處理目標是僅有少量 attribute 的 EAV 表格（Note：PGSQL 欄位數量限制大約在 1600~2500 之間，取決於資料型態）
確認這些資料中，相同 entity 的一個 attribute 只有一筆 value
使用 json_object_agg(Attribute, Value) as json_kv group by Entity 的方式，把 EAV 資料轉成「主檔/扁平 json」的形式
準備自訂 composite type 與目標存放表格
利用 select Entity, json_populate_record(json_kv) 的方式，把扁平 json 攤平成表格

上面已經進行到第三步驟，接著就從第四步驟開始。

第四步驟的功用是第五步驟所需要的前置作業：json_populate_record() 函數（與他的堧升兄弟 jsonb_populate_record()）都需要預先指定扁平 JSON 攤平之後的資料型態，有點像是宣告表格一樣。

這功能原因是目前的 PGSQL 還沒有自動的 type inference 功能，因此遇到需要回傳 record 型態的資料都得預定好他要回傳的型態。

這邊的測試範例中，來自 EAV 三個 attribute 的內容都是數字，因此我們這邊要建立一個三欄位的 composite type：

edb=# create type test_eav_flatten_type as ( key1 int, key2 int, key3 int);
CREATE TYPE
Time: 114.50 ms
edb=#

甚至，若我們只想要部份的 Attribute，也可以少指定一點

edb=# create type test_eav_flatten_type2 as ( key1 int, key2 int);
CREATE TYPE
Time: 104.650 ms
edb=#

若有型態混用也不成問題，待會的步驟基本上不會有影響

edb=# create type test_eav_flatten_type as ( key1 int, key2 text, key3 int);
CREATE TYPE
Time: 114.50 ms
edb=#

因為等下想要把資料存成表格，因此這邊建立一個轉換後的表：

create table test_eav_flatten (name text, key1 int, key2 int, key3 int);

最後是第五步驟：

json_populate_record() 函數呼叫的方式有點特別，第一個欄位需要提供上面建的 composite type，而第二個欄位才是 JSON 資料。

傳入 composite type 的方式真的很別。。會利用 NULL 的資料，作 type casting 轉成我們建的型態，提供這個函數參考。通常在 PGSQL 裡面，比較常遇到的狀況會是用回傳結果用 AS 指定殼的形狀（參見 crosstab() 函數或是 dblink() 函數）

edb=# select name, json_populate_record(null::test_eav_flatten_type, jsn_kv) from (
edb(# select name, json_object_agg(attr, value) as jsn_kv from test_eav_to_json group by name);
 name | json_populate_record 
------+----------------------
 u1   | (1,2,3)
 u2   | (4,5,6)
(2 rows)

Time: 1.159 ms
edb=#

不過。。上面回傳的內容怎麼怪怪的。。原因是這函數被當成一欄的關係，因此我們要換一下函數位置，放在 FROM 裡面

edb=# select name, j.* from (
select name, json_object_agg(attr, value) as jsn_kv from test_eav_to_json group by name) e,
json_populate_record(null::test_eav_flatten_type, jsn_kv) j;
 name | key1 | key2 | key3 
------+------+------+------
 u1   |    1 |    2 |    3
 u2   |    4 |    5 |    6
(2 rows)

Time: 0.684 ms
edb=#

這樣就好多了～最後就可以寫入目標表格

edb=# insert into test_eav_flatten 
 select name, j.* from (
 select name, json_object_agg(attr, value) as jsn_kv from test_eav_to_json group by name) e,
json_populate_record(null::test_eav_flatten_type, jsn_kv) j;
INSERT 0 2
Time: 45.378 ms
edb=# 
edb=# select * from test_eav_flatten ;
 name | key1 | key2 | key3 
------+------+------+------
 u1   |    1 |    2 |    3
 u2   |    4 |    5 |    6
(2 rows)

Time: 0.759 ms
edb=#

另外一提，少一點欄位也可以使用喔～這邊指定另外的 test_eav_flatten_type2 作示範

edb=# select name, j.* from (
select name, json_object_agg(attr, value) as jsn_kv from test_eav_to_json group by name),
json_populate_record(null::test_eav_flatten_type2, jsn_kv) j;
 name | key1 | key2 
------+------+------
 u1   |    1 |    2
 u2   |    4 |    5
(2 rows)

Time: 2.118 ms
edb=#

重複 Attribute 值的 EAV 轉成 JSON Array

上面的 EAV 攤平變成一般表格的手段，遇到有重複 Attribute 的狀況就無法完整取得資料：重複 Attribute 轉成 JSON Key 時，重複 key 值只會留下最後一筆

edb=# insert into test_eav_to_json values 
('u1', 'key1', -1);
INSERT 0 1
Time: 2.646 ms
edb=# 
edb=# select * from test_eav_to_json;
 name | attr | value 
------+------+-------
 u1   | key1 |     1
 u1   | key2 |     2
 u1   | key3 |     3
 u2   | key1 |     4
 u2   | key2 |     5
 u2   | key3 |     6
 u1   | key1 |    -1
(7 rows)

Time: 0.464 ms
edb=#

edb=# select name, j.* from (
select name, json_object_agg(attr, value) as jsn_kv from test_eav_to_json group by name),
json_populate_record(null::test_eav_flatten_type, jsn_kv) j;
 name | key1 | key2 | key3 
------+------+------+------
 u1   |   -1 |    2 |    3
 u2   |    4 |    5 |    6
(2 rows)

Time: 0.685 ms
edb=#

在我找到的資料中，JSON 規範並沒有要求 key 可不可以重複，只是多數的程式語言函式庫比較偏好單一 key 值的「自訂標準」，這種情況，重複 JSON key 值轉成 JSON array 應該是一個選項～

不過值得一提的是，PGSQL 是可以支援重複 JSON key 的，只是只有 JSON datatype 才支援，JSONB 則會剔除重複的 key 只保留最後一個（儲存上的 disk order）；但一般在使用 PGSQL 時都建議使用 JSONB 型態，才能有最完整的功能。

edb=# select name, json_object_agg(attr, value) as jsn_kv from test_eav_to_json group by name;
 name |                       jsn_kv                        
------+-----------------------------------------------------
 u1   | { "key1" : 1, "key2" : 2, "key3" : 3, "key1" : -1 }
 u2   | { "key1" : 4, "key2" : 5, "key3" : 6 }
(2 rows)

Time: 0.676 ms
edb=#

edb=# select name, jsonb_object_agg(attr, value) as jsn_kv 
from test_eav_to_json 
group by name;
 name |               jsn_kv               
------+------------------------------------
 u1   | {"key1": -1, "key2": 2, "key3": 3}
 u2   | {"key1": 4, "key2": 5, "key3": 6}
(2 rows)

Time: 0.767 ms
edb=#

因此現在的目標是，想辦法把 EAV 裡面重複 Attribute 內容（對應到 JSON key 值）弄成 JSON array，以便儲存在 JSONB 欄位～

經過一番嘗試之後，最後我試出的方法如下：

使用 Window function 搭配 count(*) 抓出 EAV 重複 Attribute
使用 Window function 把重複 Attribute 的 Value 轉成 JSON Array
使用 jsonb_object_agg()（注意，不是 json_object_agg()）轉成最終含 JSON Array 的 JSON 資料

上面最後一步的 jsonb_object_agg() 同時一舉兩得：除了直接轉成最終儲存的型態之外，這函數還幫忙處理了 window function 顯示重複資料的去重～以下放一下兩者差異

edb=# -- 最後成果
edb=# with separate_json as (
select name, attr, 
case when count(*) over(partition by  name, attr) >1 then
json_agg(to_json (value))  over(partition by  name, attr)
else
to_json (value)
end as vlu_array
from test_eav_to_json 
)
select name, jsonb_object_agg(attr,vlu_array) from separate_json
group by name ;
 name |            jsonb_object_agg             
------+-----------------------------------------
 u1   | {"key1": [-1, 1], "key2": 2, "key3": 3}
 u2   | {"key1": 4, "key2": 5, "key3": 6}
(2 rows)

Time: 0.678 ms
edb=#

edb=# -- 錯誤範例
edb=# with separate_json as (
select name, attr, 
case when count(*) over(partition by  name, attr) >1 then
json_agg(to_json (value))  over(partition by  name, attr)
else
to_json (value)
end as vlu_array
from test_eav_to_json 
)
select name, json_object_agg(attr,vlu_array) from separate_json
group by name ;
 name |                        json_object_agg                         
------+----------------------------------------------------------------
 u1   | { "key1" : [-1, 1], "key1" : [-1, 1], "key2" : 2, "key3" : 3 }
 u2   | { "key1" : 4, "key2" : 5, "key3" : 6 }
(2 rows)

Time: 0.684 ms
edb=#

[2022/12/03 補充] 新的 EAV 轉換方式，簡潔非常多～

基本思路是：一樣先透過 aggregate 把資料湊到同一筆，接著直接拆分。最近發現這兩個功能在 PGSQL 出現的 JSON 功能正好都有出路～

要使用的功能，除了原本用的 jsonb_object_agg()，把 EAV 轉成 JSON，再來的重點就是把 JSON 攤開的內建函數 jsonb_to_record()～

這邊沿用以上的測試範例，不用囉唆，一句 SQL 直接寫出結果～～

edb=# with jagg as not materialized (
    select name,
    jsonb_object_agg(attr, value) as jsn
    from test_eav_to_json
    group by name)
select
    jagg.name,
    jrcd.*
from jagg
cross join jsonb_to_record(jagg.jsn)
as jrcd (
    key1 int,
    key2 varchar,
    key3 int);
 name | key1 | key2 | key3
------+------+------+------
 u1   |    1 | 2    |    3
 u2   |    4 | 5    |    6
(2 rows)

edb=#

（以上用了 CTE 的 NOT MATERIALIZE 修飾，其實可以省略，資料庫執行引擎已經會自己決定是否要走落檔的老路～）

這個方式的彈性：

不用像之前筆記的方式寫的那麼複雜了～
jsonb_to_record() 可以便利的指定要呈現的欄位，不用全數寫出來；欄位寫錯也 OK，只會出現 NULL 而已
可以當場轉換 datatype

最後放個執行計畫（沒有放 NOT MATERIALIZE 的效果，在此處一樣）

edb=# explain with jagg as (
    select name,
    jsonb_object_agg(attr, value) as jsn
    from test_eav_to_json
    group by name)
select
    jagg.name,
    jrcd.*
from jagg
cross join jsonb_to_record(jagg.jsn)
as jrcd (
    key1 int,
    key2 varchar,
    key3 int);
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Nested Loop  (cost=22.75..31.25 rows=200 width=72)
   ->  HashAggregate  (cost=22.75..25.25 rows=200 width=64)
         Group Key: test_eav_to_json.name
         ->  Seq Scan on test_eav_to_json  (cost=0.00..18.50 rows=850 width=68)
   ->  Function Scan on jsonb_to_record jrcd  (cost=0.00..0.01 rows=1 width=40)
(5 rows)

edb=#

至於怎麼反轉，從一般表格匯出 EAV 形式的資料？

這就留待下次再嘗試惹～

參考資料

EAV 介紹

SQL: Design - Entity Attribute Value Tables (Part 1) - Why? - The Bit Bucket

To EAV, or not to EAV? Choosing your data model - wq Framework

Understanding the EAV data model and when to use it - inviqa

Entity-Attribute-Value Implementation - MariaDB Knowledge Base

Handling EAV-data - Info Support Blog

PGSQL 裡面 EAV 替代建議

Replacing EAV with JSONB in PostgreSQL - coussej · coussej | Jeroen Coussement

postgresql - Postgres 9.4+: JSONB instead of EAV - Database Administrators Stack Exchange