目前還沒找到有超簡單攤平 JSON 的方法，不過在 PGSQL 倒是有系統性的手法展開欄位。
這篇筆記會分狀況說明拆解方式，並且拿一兩個開放資料的實際範例作拆解。
Note：這篇筆記使用套色標示～使用黑白螢幕（電子紙或是映像管～！？）的人要留意一下～
由於 JSON 先天就是巢狀的儲存結構，因此 JSON 資料在攤開成表格/欄位形式時，表格資料呈現要留意兩個方向：
- wide column 形式：一個 key 就可以當一個欄位，有超級多欄位，可以理解成攤開為 CSV 一筆的狀況（可以想像在 CSV / Excel / Spreadsheet 上，橫向開的一行行欄位）
- de-normalize 形式：有 array 內容，可以攤開成多筆相似資料，每筆資料差異在 array 內容。這種狀況一筆 JSON 可能會產生數筆資料（可以想像在 CSV / Excel / Spreadsheet 上，縱向開的一列列資料）
但不論哪個面向，都意味著 RDBMS 理論中的 de-normalization 形式。因此若要作到符合 RDBMS normalization 的資料配置，就不能把 JSON 壓扁到單一表格，而需要準備一組相互關聯的表格，並且將不同部份的 JSON 資料拆解寫入。不過接下來的撇步都還是能有幫助～
首先，最陽春的方式，在 PGSQL 可以手工用 JSON operator 一條條把資料攤開，但 SQL 寫起來就醜醜的，而且遇到巢狀內容會越寫越長。如以下示意：
select
jsonb_col->>'l1_att1',
jsonb_col->>'l1_att2',
jsonb_col->'l1_l3'->>'l2_att1',
jsonb_col#>> '{l1_att4,1}',
jsonb_col#>> '{l1_att4,2}'
from some_table;
另外的方式，是透過 PGSQL 內建不少 JSON function，把 sub-JSON 與 JSON array 攤開，轉換成 set of records。這就是這篇筆記要紀錄的撇步～
這種寫法相較之下可以讓 SQL 表達的比較結構化，相對起來也比較好閱讀一點。
個人感覺編排起來像是 golang 的 JSON unmarshal() 處理一樣：golang 內建拆解 JSON 的方式，也是結構性的方式用 struct 宣告 JSON 巢狀內容，再以 unmarshal() 解析指定的 JSON 資料。
轉換狀況的小結
以下要示範的幾個功能（JSON 拆解積木）跟對應處理的狀況總結：可以在比對以下示範後在回頭
這邊的 JSON 攤平功能有以下注意事項：
- 使用這些功能時，需要透過 SQL JOIN 串連各個 set-returning function；且 JOIN 先後順序有關：在組合 SQL 時，後面承接（參照）前面
- 攤開後的資料格式，為 RDBMS 理論 de-normalize 的結果，而且資料會比原 JSON 筆數為多（一個 JSON 會被攤開成數筆資料，沒有符合 primary key 規則）；若有需要作 RDBMS normalization，可以運用這些手法自行挑選部份內容，寫入不同表。
- 對於不定數量的 dynamic sub-JSON，本處採用 json_each_text() 作 key-value 形式展開，不作不定數量欄位的資料展開：這個作法有點類似 EAV，但是對這邊 key-value 的預期是，不同的 key 對應相同的 value datatype；若有不同的 value datatype 或甚至結構各異，就需要進一步搭配 JSON datatype 作進一步的展開。
- 已知限制： 當 JSON key/value 仿照 XML，使用 Attribute/Value 來表現真實的資料 key/value 時，此拆解手法一樣可以達成目標，但不符實用：因為真實的 key 跟 value 分別被存成一組通用 key/value 欄位內，展開的結果會變成典型的 EAV 格式，且依照資料的內容，可能會過度展開而不實用。這種情況，建議不拘泥於單一 SQL 完成轉換，而是再搭配第二步驟的 SQL，配合 aggregation 作處理。例如以下範例中「自動氣象站-氣象觀測資料」的節錄
{
"parameter": [
{
"parameterName": "CITY",
"parameterValue": "臺北市"
},
{
"parameterName": "CITY_SN",
"parameterValue": "01"
},
{
"parameterName": "TOWN",
"parameterValue": "士林區"
},
{
"parameterName": "TOWN_SN",
"parameterValue": "007"
}
]
}
<location>
<!-- 其餘內容略 -->
<parameter>
<parameterName>CITY</parameterName>
<parameterValue>臺北市</parameterValue>
</parameter>
<parameter>
<parameterName>CITY_SN</parameterName>
<parameterValue>01</parameterValue>
</parameter>
<parameter>
<parameterName>TOWN</parameterName>
<parameterValue>士林區</parameterValue>
</parameter>
<parameter>
<parameterName>TOWN_SN</parameterName>
<parameterValue>007</parameterValue>
</parameter>
</location>
而想像起來好處理的內容會長成以下樣子
{
"CITY": "臺北市",
"CITY_SN": "01",
"TOWN": "士林區",
"TOWN_SN": "007"
}
以下說明各塊「JSON 拆解積木」的堆疊方式：
狀況一：sub-JSON 變成多欄
json_to_record() 是一個典型用來拆解外層 JSON 結構，轉換為欄位格式的函數。（注意 jsonb_to_record() 是相同功能的函數，差別僅在支援的 datatype 不同）。
該函數如同其他 PGSQL 的不定形 set-returning function，呼叫之時需要預先指定好回傳的欄位型態，就像是在撰寫表格 DDL 一樣。以下示範拆解最外層：
edb=# select jsonb_pretty(jsoncol) from test_case1;
jsonb_pretty
------------------------------------------------------------
{ +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",+
"layer1_att2": { +
"key1": "風和日曆", +
"key2": "0123456789", +
"key3": "wow@iam.email.address", +
"key4": true +
} +
}
(1 row)
edb=#
edb=# select test_case1_flat.* from test_case1 j cross join jsonb_to_record(j.jsoncol) as test_case1_flat( layer1_att1 varchar, layer1_att2 jsonb); layer1_att1 | layer1_att2 --------------------------------------+------------------------------------------------------------------------------------------- a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | {"key1": "風和日曆", "key2": "0123456789", "key3": "wow@iam.email.address", "key4": true} (1 row) edb=#
就如上面所述，json_to_record() 只能拆解外層 JSON，那這樣怎樣能拆解「內層」的 sub-JSON？答案在於搭配 SQL JOIN 功能：set-returning function 回傳的表格樣貌，可以在搭配 JOIN 語法，當作欄位參照功能。因此要拆解 sub-JSON，就是要參照前一層的 sub-JSON 欄位當作 json_to_record() 的參數～
以下示範的是使用 json_to_record() 拆解第二層 sub-JSON 的方式，並搭配套色對應。如法炮製就能夠用來拆解巢狀的 sub-JSON。
edb=# -- 跟上面一模一樣，只是重貼一次作比對
edb=# select jsonb_pretty(jsoncol) from test_case1;
jsonb_pretty
------------------------------------------------------------
{ +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",+
"layer1_att2": { +
"key1": "風和日曆", +
"key2": "0123456789", +
"key3": "wow@iam.email.address", +
"key4": true +
} +
}
(1 row)
edb=#
edb=# select j.jsoncol->>'layer1_att1' as layer1_att1, layer1_att2.* from test_case1 j cross join jsonb_to_record(jsoncol->'layer1_att2') as layer1_att2( key1 varchar, key2 numeric, key3 varchar, key4 bool) ; layer1_att1 | key1 | key2 | key3 | key4 --------------------------------------+----------+-----------+-----------------------+------ a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 風和日曆 | 123456789 | wow@iam.email.address | t (1 row) edb=#
注意這邊採用的 SQL JOIN 類型，使用的是 CROSS JOIN：這邊原則上向上參照的來源，不論如何只有一筆（因為吃進來的參數就只有來自一筆的一個值；就算同一層有多個 sub-JSON，每次呼叫 function 的時候，還是只有塞一個值～）。使用 INNER JOIN 的話，若有部份 sub-JSON 有缺值，可能就會少掉。
以下其他狀況，也是採用相似機制達成參照。
狀況二：array 內的均勻 sub-JSON 拆解成多筆＋多欄
json_to_recordset() 基本上是拆解 JSON array 裡面均勻排列的 sub-JSON，大概看過兩種模式：
(a) 有實際意涵的 key
這個拆解比較符合使用的預期，array 元素的 sub-JSON 直接攤平成一筆欄位性質的資料，而全數 array 元素疊起來就是多筆資料了。
以下示範
edb=# select jsonb_pretty(jsoncol) from test_case2_a;
jsonb_pretty
------------------------------------------------------------
{ +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",+
"layer1_att5": [ +
{ +
"msg": "天氣晴天", +
"code": "1", +
"timestamp": { +
"$date": "2022-12-07T01:34:41.051Z" +
} +
}, +
{ +
"msg": "晴時多雲偶陣雨", +
"code": "4", +
"timestamp": { +
"$date": "2022-12-07T01:37:14.368Z" +
} +
} +
] +
}
(1 row)
edb=#
edb=# select j.jsoncol->>'layer1_att1' as layer1_att1, layer1_att5.code, layer1_att5.msg, to_timestamp(layer1_att5.timestamp->>'$date', 'YYYY-MM-DDTHH24:MI:SS.sssZ') as timestamp from test_case2_a j cross join jsonb_to_recordset(j.jsoncol->'layer1_att5') as layer1_att5( code int, msg varchar, timestamp jsonb) ; layer1_att1 | code | msg | timestamp --------------------------------------+------+----------------+--------------------------- a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 1 | 天氣晴天 | 07-DEC-22 00:34:05 +00:00 a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 4 | 晴時多雲偶陣雨 | 07-DEC-22 00:37:36 +00:00 (2 rows) edb=#
(b) 仿 XML 只是標誌「我是 key」的 key
原始資料的 array 裡面存放了單調的 meta-key 值，實際有意義的 key 被當成 value 放置，會被攤開形成 EAV 格式，但這個接著不好在單一句 SQL 完成（需要 aggregation 在重新轉 json_to_record()，得框成 CTE 會太難看）。
這邊就直接借用上面所列的範例來測試
edb=# select jsonb_pretty(jsoncol) from test_case2_b;
jsonb_pretty
-----------------------------------------------------------
{ +
"parameter": [ +
{ +
"parameterName": "CITY", +
"parameterValue": "臺北市" +
}, +
{ +
"parameterName": "CITY_SN", +
"parameterValue": "01" +
}, +
{ +
"parameterName": "TOWN", +
"parameterValue": "士林區" +
}, +
{ +
"parameterName": "TOWN_SN", +
"parameterValue": "007" +
} +
], +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8"+
}
(1 row)
edb=#
edb=# select j.jsoncol->>'layer1_att1' as layer1_att1, parameter."parameterName", parameter."parameterValue" from test_case2_b j cross join jsonb_to_recordset(j.jsoncol->'parameter') as parameter( "parameterName" varchar, "parameterValue" varchar) ; layer1_att1 | parameterName | parameterValue --------------------------------------+---------------+---------------- a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | CITY | 臺北市 a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | CITY_SN | 01 a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | TOWN | 士林區 a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | TOWN_SN | 007 (4 rows) edb=#
edb=# -- 為了 EAV 攤平，破壞整齊感，但還算有效（效能待定）
edb=# with tobeflatten as(
select j.jsoncol->>'layer1_att1' as layer1_att1,
jsonb_object_agg(
parameter."parameterName",
parameter."parameterValue") as parameters
from test_case2_b j
cross join jsonb_to_recordset(j.jsoncol->'parameter')
as parameter(
"parameterName" varchar,
"parameterValue" varchar)
group by 1
)
select
layer1_att1,
eavflatten.*
from tobeflatten
cross join jsonb_to_record(parameters)
as eavflatten(
"CITY" varchar,
"CITY_SN" int,
"TOWN" varchar,
"TOWN_SN" int
);
layer1_att1 | CITY | CITY_SN | TOWN | TOWN_SN
--------------------------------------+--------+---------+--------+---------
a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 臺北市 | 1 | 士林區 | 7
(1 row)
edb=#
狀況三：扁平的 sub-JSON 變成 EAV 格式（key/value 欄位）
當 sub-JSON 內容均勻且不定數量，這種狀況就不見得一定要把所有 key 都轉換成欄位，而改以 key/value 的格式儲存。
edb=# select jsonb_pretty(jsoncol) from test_case3_a;
jsonb_pretty
------------------------------------------------------------
{ +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",+
"layer1_att3": { +
"01": true, +
"02": true, +
"03": false, +
"04": false +
} +
}
(1 row)
edb=#
edb=# select j.jsoncol->>'layer1_att1' as layer1_att1, layer1_att3.* from test_case3_a j cross join jsonb_each_text(jsoncol->'layer1_att3') as layer1_att3( layer2, layer2_yesno) ; layer1_att1 | layer2 | layer2_yesno --------------------------------------+--------+-------------- a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 01 | true a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 02 | true a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 03 | false a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | 04 | false (4 rows) edb=#
儘管 EAV 格式的資料在 PGSQL 並非最佳儲存建議，不過要是 sub-JSON 格式均勻，其實攤開成 EAV 所處理的欄位內容也是均勻的，PGSQL 基本上仍然可以恰當的處理。
另外，對於 JSON 有一種形狀，會把 sub-JSON 假裝 array，裡面放了形同形貌但不定種類的 sub-sub-JSON，這種狀況也是會傾向用 key-value 展開，此時會疊套兩次，變成兩層 key-value。
以下範例中，item36 與 item37 還有其他不定數量的夥伴，而個別 item 內部如上一小範例一樣，有一個格式均勻的 key/value。
edb=# select jsonb_pretty(jsoncol) from test_case3_b;
jsonb_pretty
------------------------------------------------------------
{ +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",+
"layer1_att4": { +
"item36": { +
"layer3_att1": true, +
"layer3_att2": { +
"03": false, +
"04": false +
}, +
"layer3_att3": { +
} +
}, +
"item37": { +
"layer3_att1": true, +
"layer3_att2": { +
"01": false, +
"04": true +
} +
} +
} +
}
(1 row)
edb=#
edb=# select j.jsoncol->>'layer1_att1' as layer1_att1, layer1_att4.layer2_item, (layer1_att4.item_content->>'layer3_att1')::bool as layer3_att1, layer3_att2.layer3_key, layer3_att2.layer3_value::bool from test_case3_b j cross join jsonb_each(j.jsoncol->'layer1_att4') as layer1_att4( layer2_item, item_content) cross join jsonb_each_text(layer1_att4.item_content->'layer3_att2') as layer3_att2( layer3_key, layer3_value) ; layer1_att1 | layer2_item | layer3_att1 | layer3_key | layer3_value --------------------------------------+-------------+-------------+------------+-------------- a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | item36 | t | 03 | f a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | item36 | t | 04 | f a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | item37 | t | 01 | f a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | item37 | t | 04 | t (4 rows) edb=#
狀況四：展開純 JSON array 變成一欄多筆資料
使用狀況大致上分成中間過水的 key （也就是用 array 放 sub-JSON）或是純資料 array，
這項比較沒有爭議，RDBMS 標準來說比較寄望使用者避免一個欄位裡面塞 array，因此會被攤成多筆。不過其實也是可以用 postgres array datatype 來裝啦～
這邊就直接攤成多筆就好～
edb=# select jsonb_pretty(jsoncol) from test_case4;
jsonb_pretty
------------------------------------------------------------
{ +
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",+
"layer1_att6": [ +
"Mu continent", +
"D-brane", +
"Nazca lines" +
] +
}
(1 row)
edb=#
edb=# select j.jsoncol->>'layer1_att1' as layer1_att1, layer1_att6 from test_case4 j cross join jsonb_array_elements_text(j.jsoncol->'layer1_att6') as layer1_att6 ; layer1_att1 | layer1_att6 --------------------------------------+-------------- a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | Mu continent a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | D-brane a3cd2c1e-d24a-4336-a0e2-773b7d0aade8 | Nazca lines (3 rows) edb=#
以上是積木的範例，接著用幾個範例示範怎樣疊這些積木。
（文長注意～！）
測試範例一：某個自訂結構
其實就是以上幾個狀況的集合，一次疊在一起就是了。這邊一樣搭配套色作辨識。
這邊不把輸出內容列出來。。。。一個 JSON 變成 120 筆。。。
{
"layer1_att1": "a3cd2c1e-d24a-4336-a0e2-773b7d0aade8",
"layer1_att2": {
"key1": "風和日曆",
"key2": "0123456789",
"key3": "wow@iam.email.address",
"key4": true
},
"layer1_att3": {
"01": true,
"02": true,
"03": false,
"04": false
},
"layer1_att4": {
"item36": {
"layer3_att1": true,
"layer3_att2": {
"03": false,
"04": false,
"05": false
},
"layer3_att3": {}
},
"item37": {
"layer3_att1": true,
"layer3_att2": {
"01": false,
"04": true
},
"layer3_att3": {}
}
},
"layer1_att5": [
{
"timestamp": {
"$date": "2022-12-07T01:34:41.051Z"
},
"code": "1",
"msg": "天氣晴天"
},
{
"timestamp": {
"$date": "2022-12-07T01:37:14.368Z"
},
"code": "4",
"msg": "晴時多雲偶陣雨"
}
],
"layer1_att6": ["Mu continent", "D-brane", "Nazca lines"],
"recordDate": {
"$date": "2022-12-07T06:57:01.386Z"
}
}
select
j.jsoncol->>'layer1_att1' as layer1_att1,
layer1_att2.*,
layer1_att3.*,
layer1_att4.layer2_item,
(layer1_att4.item_content->>'layer3_att1')::bool as layer3_att1,
layer3_att2.layer3_key,
layer3_att2.layer3_value::bool,
layer1_att5.code,
layer1_att5.msg,
to_timestamp(layer1_att5.timestamp->>'$date', 'YYYY-MM-DDTHH24:MI:SS.sssZ') as timestamp,
layer1_att6
from test_case j
cross join jsonb_to_record(j.jsoncol->'layer1_att2')
as layer1_att2(
key1 varchar,
key2 numeric,
key3 varchar,
key4 bool)
cross join jsonb_each_text(j.jsoncol->'layer1_att3')
as layer1_att3(
layer2,
layer2_yesno)
cross join jsonb_each(j.jsoncol->'layer1_att4')
as layer1_att4(
layer2_item,
item_content)
cross join jsonb_each_text(layer1_att4.item_content->'layer3_att2')
as layer3_att2(
layer3_key,
layer3_value)
cross join jsonb_to_recordset(j.jsoncol->'layer1_att5')
as layer1_att5(
code int,
msg varchar,
timestamp jsonb)
cross join jsonb_array_elements_text(j.jsoncol->'layer1_att6')
as layer1_att6
;
測試範例二：教育部重編國語辭典資料檔
這也稱作「萌典」，g0v 有提供整理後原始資料的 JSON，因此拿來拆解。萌典的資料預先要匯入的話，要注意整份資料被匡成單一個 JSON，但實際內容其實是一筆筆獨立的 JSON，因此匯入建議注意拆解（Postgres 不像 MongoDB 專門吃 JSON 的 mongoimport 指令一樣提供 --jsonArray 的匯入設計）。
為了作完整紀錄，這邊還是把原始其中一筆 JSON 紀錄一份（我不擁有這東東的版權喔～作者是 MOE dict 歷來各相關團隊～）
{
"heteronyms": [
{
"bopomofo": "ㄑㄧㄣˋ",
"definitions": [
{
"def": "狗嘔吐。",
"link": [
"通「唚」。"
],
"quote": [
"《集韻．去聲．沁韻》：「𢙈，犬吐。」"
],
"type": "動"
}
],
"pinyin": "qìn"
}
],
"non_radical_stroke_count": 6,
"radical": "心",
"stroke_count": 10,
"title": "{[92ef]}"
}
拆解的 SQL
edb=# select -- heteronym.definitions, bopomofo, pinyin, -- definition.*, definition.def, definition.type, -- quote.quote, quote.value as quote, non_radical_stroke_count, radical, stroke_count, title from moedict_fgn cross join json_to_recordset(heteronyms) as heteronym ( bopomofo varchar, definitions jsonb, pinyin varchar ) cross join jsonb_to_recordset(heteronym.definitions) as definition ( def varchar, type varchar, quote jsonb ) cross join jsonb_array_elements_text (definition.quote) as quote ; ...略... -[ RECORD 17 ]-----------+------------------------------------- bopomofo | ㄑㄧㄣˋ pinyin | qìn def | 狗嘔吐。 type | 動 quote | 《集韻．去聲．沁韻》：「𢙈，犬吐。」 non_radical_stroke_count | 6 radical | 心 stroke_count | 10 title | {[92ef]} -[ RECORD 18 ]-----------+------------------------------------- Time: 7073.443 ms (00:07.073) edb=#
小姿勢：查字典～辭典檢視[𢙈 : ㄑㄧㄣˋ] - 教育部《重編國語辭典修訂本》2021
測試範例三：自動氣象站-氣象觀測資料
這個資料要以 API 機制存取，這邊先手工下載一筆，餵進去資料庫：由於 JSON 有換行，要透過 COPY 指令餵的話，要先把換行字元挑掉。edb=# create table CWB_A0001_JSON(cwbopendata JSONB); CREATE TABLE edb=#
[enterprisedb@edb14 ~]$ sed -z 's/\n//g' /tmp/O-A0001-001.json | psql -d edb -c 'COPY CWB_A0001_JSON FROM STDIN;' COPY 1 [enterprisedb@edb14 ~]$ ## 這個「一筆」很大一份，全數觀測站一個時刻都在這
為了作完整紀錄，這邊還是把原始 JSON 節錄紀錄一份（我不擁有這東東的版權喔～作者是氣象局～）
{
"cwbopendata": {
"@xmlns": "urn:cwb:gov:tw:cwbcommon:0.1",
"identifier": "e9e1cb83-af6a-4f0c-aafb-e818620d8048",
"sender": "weather@cwb.gov.tw",
"sent": "2022-12-02T22:18:05+08:00",
"status": "Actual",
"msgType": "Issue",
"dataid": "CWB_A0001",
"scope": "Public",
"dataset": null,
"location": [
{
"lat": "25.098133",
"lon": "121.508275",
"lat_wgs84": "25.0963555555556",
"lon_wgs84": "121.516505555556",
"locationName": "科教館",
"stationId": "C0A770",
"time": {
"obsTime": "2022-12-02T22:00:00+08:00"
},
"weatherElement": [
{
"elementName": "ELEV",
"elementValue": {
"value": "60.0"
}
},
{
"elementName": "WDIR",
"elementValue": {
"value": "129"
}
},
{
"elementName": "WDSD",
"elementValue": {
"value": "3.2"
}
},
{
"elementName": "TEMP",
"elementValue": {
"value": "20.2"
}
},
{
"elementName": "HUMD",
"elementValue": {
"value": "0.91"
}
},
{
"elementName": "PRES",
"elementValue": {
"value": "1012.9"
}
},
{
"elementName": "H_24R",
"elementValue": {
"value": "2.0"
}
},
{
"elementName": "H_FX",
"elementValue": {
"value": "-99"
}
},
{
"elementName": "H_XD",
"elementValue": {
"value": "-99"
}
},
{
"elementName": "H_FXT",
"elementValue": {
"value": "-99"
}
},
{
"elementName": "D_TX",
"elementValue": {
"value": "20.90"
}
},
{
"elementName": "D_TXT",
"elementValue": {
"value": "2022-12-02T14:00:00+08:00"
}
},
{
"elementName": "D_TN",
"elementValue": {
"value": "18.60"
}
},
{
"elementName": "D_TNT",
"elementValue": {
"value": "2022-12-02T06:50:00+08:00"
}
}
],
"parameter": [
{
"parameterName": "CITY",
"parameterValue": "臺北市"
},
{
"parameterName": "CITY_SN",
"parameterValue": "01"
},
{
"parameterName": "TOWN",
"parameterValue": "士林區"
},
{
"parameterName": "TOWN_SN",
"parameterValue": "007"
}
]
},
{。。。重複內容不同地點：略過。。。}
]
}
}
select l1.sent, l1.scope, l1."@xmlns" as xmlns, l1.dataid, l1.sender, l1.status, l1.dataset, l1."msgType" as msgType, -- l2.value as location, l3.lat, l3.lon, l3.time->'obsTime' as obsTime, l3.lat_wgs84, l3.lon_wgs84, -- l3.parameter, l4_1."parameterName", l4_1."parameterValue", l4_2."elementName", l4_2."elementValue" from cwb_a0001_json cross join jsonb_to_record(cwbopendata->'cwbopendata') as l1( sent varchar, scope varchar, "@xmlns" varchar, dataid varchar, sender varchar, status varchar, dataset varchar, "msgType" varchar, location jsonb ) cross join jsonb_array_elements(l1.location) as l2 -- 預設欄位叫做 value 沒得選 cross join jsonb_to_record(l2.value) as l3( lat numeric, lon numeric, time jsonb, lat_wgs84 numeric, lon_wgs84 numeric, parameter jsonb, "stationId" varchar, "locationName" varchar, "weatherElement" jsonb ) cross join jsonb_to_recordset(l3.parameter) as l4_1( --看起來是從 xml 硬轉的 "parameterName" varchar, "parameterValue" varchar ) cross join jsonb_to_recordset(l3."weatherElement") as l4_2( "elementName" varchar, "elementValue" jsonb ) ;
從以上的範例中，可以觀察到，直接疊套，一筆 JSON 其實會被拆解成重複性很高的多筆資料，此外這些資料好像直接看起來不太容易還原成 JSON。
不過這個手法，還是可以擷取部份，搭配正規化的 RDBMS 表格，各自塞妥塞滿，如此應該多少能看到原始 JSON 的巢狀性質。
筆記到此結束～後續還有所謂 JSON table 功能（原訂 PGSQL 15 釋出，但應該是延了一期），把 PGSQL 的 JSON 能力更加補完。以後有機會在看看～
參考資訊：
筆記到此結束～後續還有所謂 JSON table 功能（原訂 PGSQL 15 釋出，但應該是延了一期），把 PGSQL 的 JSON 能力更加補完。以後有機會在看看～
參考資訊：
測試資料
jdorfman/awesome-json-datasets: A curated list of awesome JSON datasets that don't require authentication.
資料工程初探 以開放資料清理轉換為例 - 王學治
自動氣象站-氣象觀測資料| 政府資料開放平臺
自動雨量站-雨量觀測資料| 政府資料開放平臺
GitHub - g0v/moedict-data: 教育部重編國語辭典 資料檔 (萌典)
https://github.com/leinstay/steamdb
筆記整理過程找到或用到的功能
GitHub - juanjoDiaz/json2csv: Flexible conversion between JSON and CSV
Using COPY to import a .json file into a PostgreSQL table - Stack Overflow
Import JSON into Postgres using COPY - Konbert
#Importing the data - Loading JSON into Postgres | Paul Copplestone
Can sed replace new line characters? - Unix & Linux Stack Exchange
How to turn a json array into rows in postgres - Stack Overflow
Flattening JSON(b) in Postgres · Ellis Valentiner
Recursively Merging JSONB in PostgreSQL | by Calvin Lobo | Hootsuite Engineering | Medium
Flatten JSON data to individual columns-postgresql - Coding Tutorials and Tools for Developers, Get Skilled Developers,IT Solutions & Services
Flattening json data in PostgreSQL · GitHub Gist
Flatten nested JSON structure in PostgreSQL - Stack Overflow
Postgres JSONB - Flatten nested objects and groupings - Database Administrators Stack Exchange
Postgres: Flatten JSON keys and keys paths - igrek8/query.sql - GitHub Gist
Flatten aggregated key/value pairs from a JSONB field?_json_Postgredaxiang-DevPress官方社区
jdorfman/awesome-json-datasets: A curated list of awesome JSON datasets that don't require authentication.
資料工程初探 以開放資料清理轉換為例 - 王學治
自動氣象站-氣象觀測資料| 政府資料開放平臺
自動雨量站-雨量觀測資料| 政府資料開放平臺
GitHub - g0v/moedict-data: 教育部重編國語辭典 資料檔 (萌典)
https://github.com/leinstay/steamdb
筆記整理過程找到或用到的功能
GitHub - juanjoDiaz/json2csv: Flexible conversion between JSON and CSV
Using COPY to import a .json file into a PostgreSQL table - Stack Overflow
Import JSON into Postgres using COPY - Konbert
#Importing the data - Loading JSON into Postgres | Paul Copplestone
Can sed replace new line characters? - Unix & Linux Stack Exchange
How to turn a json array into rows in postgres - Stack Overflow
Flattening JSON(b) in Postgres · Ellis Valentiner
Recursively Merging JSONB in PostgreSQL | by Calvin Lobo | Hootsuite Engineering | Medium
Flatten JSON data to individual columns-postgresql - Coding Tutorials and Tools for Developers, Get Skilled Developers,IT Solutions & Services
Flattening json data in PostgreSQL · GitHub Gist
Flatten nested JSON structure in PostgreSQL - Stack Overflow
Postgres JSONB - Flatten nested objects and groupings - Database Administrators Stack Exchange
Postgres: Flatten JSON keys and keys paths - igrek8/query.sql - GitHub Gist
Flatten aggregated key/value pairs from a JSONB field?_json_Postgredaxiang-DevPress官方社区
沒有留言:
張貼留言