一、前言
商業(yè)場景中，經(jīng)常會出現(xiàn)新的業(yè)務(wù)，繼而產(chǎn)生新的業(yè)務(wù)數(shù)據(jù)，這也難免會導致一些數(shù)據(jù)被孤立，所以便需要對數(shù)據(jù)進行同步整合。在清洗數(shù)據(jù)的過程中，難免也會出現(xiàn)同一個 SQL 邏輯需要使用不同的平臺各自支持的一套 SQL 語言來實現(xiàn)。
本文介紹的就是一個同樣的 SQL 邏輯，通過不同的平臺進行操作。
相關(guān)平臺：阿里云的 postgresql 和 阿里云的 MaxCompute SQL（下面稱 ODPS SQL）。
版本說明：
PostgreSQL：PostgreSQL 11.3 64-bit
MySQL：MySQL 8.0.16
ODPS SQL：odps-sql-function version: r570e07eb77a5063f8c5715b0fa0beeba（阿里云似乎會默認更新到最新版）
二、目標介紹
首先介紹下抽象出來的數(shù)據(jù)，有一個表，記錄2列數(shù)據(jù)，可以看做是一個答題記錄，content列記錄用戶的某個類的答題內(nèi)容，而level列記錄用戶對應類的等級信息，結(jié)構(gòu)如下：
創(chuàng)建臨時數(shù)據(jù)集的 SQL：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
select *
from t1;
最終處理效果為：
將content和level里的鍵值信息分別都取出來，然后拼接成一個用戶在某個類型的答題內(nèi)容和等級信息表單，方便做業(yè)務(wù)分析。
基本分析：content和level的字符類型都是字符串，但他們的數(shù)據(jù)結(jié)構(gòu)比較特殊，content是一個 Json 數(shù)組結(jié)構(gòu)的字符串，而level是一個鍵值對結(jié)構(gòu)的字符串。 進行處理的過程中可以將他們轉(zhuǎn)為 Json 字符類型進行處理。
由于不同的用戶的行為不同，Json 的元素的長度也是不一致的，所以要將類型（type）展開，需要分別處理兩個字段，最后再進行聯(lián)結(jié)。
三、使用 pgsql 實現(xiàn)
pgsql 有比較強大的 json 函數(shù)，可以通過相關(guān)的 json 函數(shù)輔助處理 json 結(jié)構(gòu)的數(shù)據(jù)，參考 阿里云的 postgresql 的 json 函數(shù)文檔
3.1 拆分 content 字段
content是一個 Json 數(shù)組結(jié)構(gòu)的字符串，所以可以通過::json函數(shù)轉(zhuǎn)化為 json 數(shù)據(jù)類型之后，通過json_array_elements()函數(shù)進行元素拆分，一行一個元素。
SQL 參考如下：
-- 拆元素
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
select user_id,json_array_elements(content::json) as "content_kv"
from t1;
拆分結(jié)果如下，可以看到已將content數(shù)據(jù)中的每一個元素都拆分開，一行保留一個元素，這時用戶 102 有兩行記錄。
接下來就是把上面的結(jié)果中，以鍵為字段名，以值為字段值，將固定長度的鍵值對處理為兩個新列type和content。直接通過鍵取值即可，參考 SQL 如下：
-- 拆元素并取值
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
select user_id
,json_array_elements(content::json) as "content_kv"
,json_array_elements(content::json)->>'type' as "type"
,json_array_elements(content::json)->>'content' as "content"
from t1;
結(jié)果如下：
返回結(jié)果中，user_id、type和content三個字段便是最終需要的字段。所以拆分原始content字段的任務(wù)到此完成。
3.2 拆分 level 字段
接下來拆分level字段。level是一個鍵值對結(jié)構(gòu)字符串，鍵值對是標準 json 結(jié)構(gòu)，所以可以通過轉(zhuǎn)化為 json 數(shù)據(jù)類型之后，再借助json_object_keys()提取鍵值對中的所有鍵，一行一個，順帶也將鍵對應的值提取出來。
先將鍵取出來，SQL 如下：
-- 取鍵值對的鍵
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
select user_id,level,json_object_keys(level::json) as "type"
from t1;
結(jié)果如下：
有了鍵，再取值，就很方便了，通過->取值即可。參考 SQL 如下：
-- 取鍵值對的鍵和值
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
select user_id
,level
,json_object_keys(level::json) as "type"
,level::json -> json_object_keys(level::json) as "level"
from t1;
結(jié)果如下：
返回結(jié)果中，user_id、type和level三個字段便是最終需要的字段。所以拆分原始level字段的任務(wù)到此完成。
3.3 拼接兩個拆分結(jié)果
拼接這步則相對比較簡單，分別將以上的兩個拆分的結(jié)果作為兩個子查詢，然后通過user_id和type進行連接即可。
參考 SQL 如下：
-- 拼接
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
select t1_content.user_id,t1_content.type,t1_content.content,t1_level.level
from(
select user_id
,json_array_elements(content::json)->>'type' as "type"
,json_array_elements(content::json)->>'content' as "content"
from t1
)t1_content
left join(
select user_id
,json_object_keys(level::json) as "type"
,level::json -> json_object_keys(level::json) as "level"
from t1
)t1_level on t1_level.user_id=t1_content.user_id and t1_level.type=t1_content.type
;
最終結(jié)果如下：
通過 pgsql 處理還是比較簡單的，基本上就是四個函數(shù)便可解決，四個函數(shù)分別是：::json、json_array_elements()、json_object_keys()、->和->>。
但是使用 ODPS SQL 就沒有那么便捷了！
四、使用 ODPS SQL 實現(xiàn)
ODPS 是阿里基于Hive的核心思想構(gòu)建的，不同的是 Hive 的文件存儲在 hdfs 上，ODPS 則存在阿里的盤古里，而且 ODPS 針對 Hive 做了一些優(yōu)化，所以 ODPS SQL 和 HQL 比較接近，和 MySQL 也有一定的相似性。
由于 ODPS SQL 沒有像 pgsql 那么便捷的 json 函數(shù)，所以需要通過其他的方式進行拆分元素。通過查閱官方的 SQL 文檔，發(fā)現(xiàn)可以通過以下的方式進行替換，僅展示主要函數(shù)：
| Postgres SQL | ODPS SQL |
|---|---|
| ::json | json_parse() |
| json_array_elements() | regexp_count()、lateral view explode() |
| json_object_keys() | str_to_map()、map_values()、lateral view explode() |
| -> | [] 或 json_extract() |
| ->> | json_extract() |
參考：ODPS SQL 的 json 等復雜函數(shù)。
下面具體來介紹一下。
4.1 拆分 content 字段
由于 ODPS SQL 不能一步到位將 json 數(shù)據(jù)拆開，由一行變成多行，所以需要通過另外的方式進行行向擴展，即通過lateral view視圖將數(shù)據(jù)進行發(fā)散，而發(fā)散多少行呢？這個由 json 的元素的個數(shù)來決定，所以需要先計算元素的個數(shù)。
ODPS SQL 提供了regexp_count()函數(shù)，可以通過計算數(shù)組元素的個數(shù)來確定。
那么如何計算數(shù)組的元素個數(shù)呢？通過觀察數(shù)據(jù)的結(jié)構(gòu)特點，我通過識別元素的分割符},{作為標識，即regexp_count(content,'}\\s*,\\s*{')，具體含義如下：
-
\s：匹配任何空白符，避免因為出現(xiàn)空格而匹配不上，等價于** [\t\n\f\r ]** -
\\：由于系統(tǒng)采用反斜線\作為轉(zhuǎn)義符，因此正則表達式的模式中出現(xiàn)的\都要進行二次轉(zhuǎn)義。例如正則表達式要匹配字符串a+b。其中+是正則中的一個特殊字符，因此要用轉(zhuǎn)義的方式表達，在正則引擎中的表達方式是a\\+b。由于系統(tǒng)還要解釋一層轉(zhuǎn)義，因此能夠匹配該字符串的表達式是a\\\+b。簡單理解就是在 SQL 中使用特殊字母轉(zhuǎn)義時需要多加一層轉(zhuǎn)義，即多加一個**\**。 -
*：匹配前面的子表達式0次或多次。
下面來看看處理效果
參考 SQL：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,content
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
from t1
)
select *
from t1_json t1;
輸出結(jié)果：
接下來就是將返回的結(jié)果根據(jù)cnt字段進行發(fā)散，這里有一個臨界點問題，就是當cnt=0時，表示只有一個元素，當cnt=1則是兩個，依次類推。數(shù)組的索引是從 0 開始的，所以通過lateral view創(chuàng)建的視圖，在發(fā)散時，需要注意起點和臨界值的處理。發(fā)散之后，通過發(fā)散的序號來進行索引，取出每一個元素的值。
下面直接看下 SQL 來講解：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,content
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
from t1
)
select t1.user_id
,t1.content_json
,t1.cnt
,index_tbl.index_no
,json_extract(t1.content_json, concat('$[',index_tbl.index_no,']')) as "content"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
;
上面 SQL 通過lateral view發(fā)散，將一行擴展到四行，數(shù)據(jù)增大了4倍，然后再通過限制index_tbl.index_no<=t1.cnt取出符合期望的行，可以看看下面這張圖，返回的結(jié)果是沒有加上最后where條件的結(jié)果，從圖中可以看出，101 用戶發(fā)散的四條記錄中，只有一條是有用的，其他三條返回的content都是空值，這些空值的行可以過濾掉，這就是where的作用。
至此，僅完成了前面json_array_elements()的處理結(jié)果，而我們的目標是要將處理結(jié)果中的鍵值對的type對應的值和content對應的值取出來，然后使用對應的鍵來命名，接下來就把值取出。
取值使用的函數(shù)也是一樣的，使用json_extract()來取，所以直接在上面的 SQL 中的json_extract()加上鍵即可，具體 SQL 如下：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,content
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
from t1
)
select t1.user_id
,t1.content_json
,json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].type')) as "type"
,json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].content')) as "content"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
;
返回結(jié)果如下：
從返回的結(jié)果可以看到，type和content都是帶引號的，這是因為json_extract()函數(shù)返回的數(shù)據(jù)類型是 JSON 類型，而不是字符串，所以還需要進行一步數(shù)據(jù)類型的顯性轉(zhuǎn)換。
最終的 SQL 如下：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,content
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
from t1
)
select t1.user_id
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].type')) as string) as "type"
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].content')) as string) as "content"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
;
4.2 拆分 level 字段
拆分level字段的時候，可以用類似拆分content字段的方法，對level字段計算元素的個數(shù)，然后使用lateral view視圖進行發(fā)散，再取非空的行。
由于level字段的元素個數(shù)和content字段的元素個數(shù)是一致的，所以，可以使用前面已經(jīng)統(tǒng)計好的cnt字段，因為這二者都是通過這種方法進行發(fā)散，最終是可以進行合并處理的，為了不混淆，統(tǒng)一使用cnt字段。
SQL 如下：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
from t1
)
select *
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
接下來，有一個難點，就是怎么知道level的鍵是什么？前面content拆分完，鍵都是一致的，但是這里的鍵是可變長的，可能是 1 個，或 2 個，或 3 個，或十幾個等，所以這里不能一個個邏列，需要使用其他的方法“智能”取鍵。
通過閱讀官方文檔（參考：ODPS SQL 的 json 等復雜函數(shù)），發(fā)現(xiàn)了可以通過轉(zhuǎn)為map數(shù)據(jù)類型進行處理，先通過str_to_map()轉(zhuǎn)為map類型，然后使用map_keys()取鍵，前面是通過鍵取值，但是在map類型中，還提供了map_values()函數(shù)，也就是說，可以直接取值，不通過鍵，一步到位！
下面來實現(xiàn)一下：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
from t1
)
select t1.user_id
,t1.level
,str_to_map(regexp_extract(t1.level,'{(.*?)}'),',',':') as level_map
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
注意：以上對t1.level進行了一層處理：regexp_extract(t1.level,'{(.*?)}')，目的是將外層的花括號去掉，使用str_to_map()不需要花括號，有鍵值對的結(jié)構(gòu)即可。str_to_map(<col_nmae>,',',':')再根據(jù)逗號進行切割元素，通過冒號處理為鍵值對。其實處理前后的“長相”是一樣！
以上 SQL 的返回結(jié)果如下，雖然看上去一樣，但是數(shù)據(jù)類型是不一樣的，level是 string 類型，level_map是 map 類型，繞了這圈子，其實就是轉(zhuǎn)換下數(shù)據(jù)類型，那能不能直接轉(zhuǎn)呢，至少目前從官方文檔看不到這樣的功能。
轉(zhuǎn)換為 map 類型之后，再使用map_keys()和map_values()分別取出鍵和值的數(shù)組，然后再根據(jù)索引取出對應的值。索引取值可以使用[]或json_extract()取值。
SQL 如下：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
from t1
)
select t1.user_id
,t1.level
,map_keys(str_to_map(regexp_extract(t1.level,'{(.*?)}'),',',':'))[index_tbl.index_no] as "type"
,map_values(str_to_map(regexp_extract(t1.level,'{(.*?)}'),',',':'))[index_tbl.index_no] as "level"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
運行結(jié)果如下：
注意：這里返回的結(jié)果也需要進行數(shù)據(jù)類型轉(zhuǎn)換，type轉(zhuǎn)為 string 類型，而level2（有重名，被自動標記序號）轉(zhuǎn)為 int 或 bigint 類型。
到這里會有另外一個小細節(jié)需要處理，就是當將type轉(zhuǎn)換為 string 類型之后，可以發(fā)現(xiàn)type字段依舊帶有雙引號。這是因為字符串是"a"，即帶雙引號的a，字符長度是 3。
怎么辦呢？再做一層處理，可以在處理后將雙引號去掉，也可以在一開始的時候就將雙引號去掉。
下面展示一開始就去掉雙引號的方法參考：
使用replace()先將·level字段的雙引號去掉，最后再轉(zhuǎn)換數(shù)據(jù)類型。
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
from t1
)
select t1.user_id
,t1.level
,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as string) as "type"
,cast(map_values(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as int) as "level"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
至此，完成拆分level字段。
4.3 合并拆分
接下來將上面兩步拆分進行合并。直接來看看代碼：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
-- ,REGEXP_COUNT(level,':') as level_cnt
from t1
)
select t1.user_id
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].type')) as string) as "type1"
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].content')) as string) as "content"
,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as string) as "type2"
,cast(map_values(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as int) as "level"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt;
返回結(jié)果如下：
至此，是不是就大功告成了呢？
不！還不行，還有兩個問題沒有解決。
問題1、數(shù)組的元素是有順序保證的，但是鍵值對不一定是按照數(shù)組的元素的順序排列，有可能會出現(xiàn)二者錯位的現(xiàn)象，只是剛好的舉的例子沒有錯位。為了保證類型（type）一致，需要再加一層操作，將上述的 SQL 返回的臨時表進行自聯(lián)結(jié)。參考 SQL 如下：
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
-- ,REGEXP_COUNT(level,':') as level_cnt
from t1
)
,temp as(
select t1.user_id
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].type')) as string) as "type1"
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].content')) as string) as "content"
,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as string) as "type2"
,cast(map_values(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as int) as "level"
from t1_json t1
-- 一行變成四行，發(fā)散4倍
lateral view explode(array(0,1,2,3)) index_tbl as index_no
-- 限制發(fā)散的值 index_no 小于等于 t1.cnt 的行，把大于 t1.cnt 的行去掉
where index_tbl.index_no<=t1.cnt
)
select t2.user_id,t2.type1 as "type",t2.content,t3.level
from temp t2, temp t3
where t2.user_id=t3.user_id and t2.type1=t3.type2
最終結(jié)果如下：
問題2、要發(fā)散多少行呢？ 可能前面的時候你會覺得納悶，為什么是array(0,1,2,3)，而不是其他？使用array(0,1,2,3)是方便理解，先把數(shù)據(jù)跑通，實際上這樣的處理存在很大的風險，一旦元素的個數(shù)超過了 4 個就會有數(shù)據(jù)丟失，所以如果使用該方法，可能需要把數(shù)組的元素加的足夠長，以規(guī)避該風險。但是將元素加得足夠大之后，原有的行記錄都放大對應的倍數(shù)，會極大消耗資源，是否有更好的方法呢？
帶著這個問題求助下 GPT，得到了一個反饋，可以通過sequence(start, stop)函數(shù)來動態(tài)生成數(shù)組，將start設(shè)置為 0，而·stop設(shè)置為元素的個數(shù)cnt便可實現(xiàn)動態(tài)發(fā)散。參考 SQL 如下：
參考：阿里云 sequence 函數(shù)文檔
with t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select user_id
,level
,json_parse(content) as content_json
,regexp_count(content,'}\\s*,\\s*{') as cnt
,json_parse(level) as level_json
-- ,REGEXP_COUNT(level,':') as level_cnt
from t1
)
,temp as(
select t1.user_id
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].type')) as string) as "type1"
,cast(json_extract(t1.content_json, concat('$[',index_tbl.index_no,'].content')) as string) as "content"
,cast(map_keys(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as string) as "type2"
,cast(map_values(str_to_map(regexp_extract(replace(t1.level,'"',''),'{(.*?)}'),',',':'))[index_tbl.index_no] as int) as "level"
from t1_json t1
-- 使用 sequence() 動態(tài)發(fā)散
lateral view explode(sequence(0, t1.cnt)) index_tbl as index_no
)
select t2.user_id,t2.type1 as "type",t2.content,t3.level
from temp t2, temp t3
where t2.user_id=t3.user_id and t2.type1=t3.type2
至此，大功告成！
五、使用 MySQL 實現(xiàn)
本來到這里就結(jié)束了，突然心血來潮，試試 MySQL 是否有方便的處理方法。
參考：MySQL8 官方 json 函數(shù)介紹
實踐之后發(fā)現(xiàn)，并沒有！流程和 ODPS SQL 實現(xiàn)過程差不多，不過函數(shù)有好些差異。下面提供一份 SQL 參考：
with
-- 遞歸創(chuàng)建數(shù)字序列
RECURSIVE index_tbl AS (
SELECT 0 AS index_no
UNION ALL
SELECT index_no + 1 FROM index_tbl WHERE index_no < 10
)
,t1 as(
select 101 as user_id,'[{"type":"a","content":"abc"}]' as "content",'{"a":1}' as "level" union all
select 102 as user_id,'[{"type":"a","content":"ad"},{"type":"b","content":"ab"}]','{"a":1,"b":2}'
)
,t1_json as(
select
user_id
,cast(content as json) "content_json"
,cast(level AS json) "level_json"
,json_length(cast(content as json)) "cnt"
from t1
)
,temp as(
select
t1.user_id
,json_unquote(json_extract(content_json, concat('$[',index_tbl.index_no,'].type'))) as "type1"
,json_unquote(json_extract(content_json, concat('$[',index_tbl.index_no,'].content'))) as "content"
,json_unquote(json_extract(json_keys(level_json) , concat('$[',index_tbl.index_no,']'))) as "type2"
,json_unquote(json_extract(level_json, concat('$.',json_unquote(json_extract(json_keys(level_json) , concat('$[',index_tbl.index_no,']')))))) as "level"
from t1_json t1
-- 發(fā)散
join index_tbl on index_tbl.index_no<cnt
)
select t2.user_id,t2.type1 as "type",t2.content,t3.level
from temp t2, temp t3
where t2.user_id=t3.user_id and t2.type1=t3.type2
;
MySQL 沒有像 ODPS SQL 的lateral view和explode()函數(shù)，所以不能直接展開，MySQL 通過RECURSIVE實現(xiàn)遞歸創(chuàng)建一個數(shù)字序列，然后直接和元素個數(shù)的字段進行join并設(shè)置好邊界值實現(xiàn)相同的效果。
使用RECURSIVE創(chuàng)建數(shù)字序列表時，可以把index_no的上限設(shè)置稍微大一些，后續(xù)關(guān)聯(lián)直接動態(tài)限制發(fā)散的行數(shù)，而不是直接放大倍數(shù)，數(shù)據(jù)不會全部暴漲到index_no上限值的倍數(shù)，和lateral view直接發(fā)散關(guān)聯(lián)有一定區(qū)別。
MySQL 有json_length()函數(shù)，可以直接計算元素個數(shù)，相對 ODPS SQL 比較便利；也支持json_keys()直接取鍵，這個和 pgsql 類似，只不過 pgsql 直接進行了發(fā)散，將鍵拆分為一行一個，而 MySQL 還需要結(jié)合index_tbl數(shù)字序列表手動發(fā)散；另外，MySQL 還提供了一個json_unquote()函數(shù)，可以直接將json_extract()返回的 json 類型轉(zhuǎn)為 字符串。
六、總結(jié)
本文分別通過 pgsql、ODPS SQL 和 MySQL 三種 SQL 語法進行 json 類型的處理。其中，使用 pgsql 處理方式最簡單且簡潔，而 ODPS SQL 最復雜，中間進行了多次數(shù)據(jù)類型的變更，甚至還需要使用一種更少見的數(shù)據(jù)類型 map 類型來輔助處理；而 MySQL 則處于二者之間。
下面通過一張表格對比下三者實現(xiàn)同樣功能需要使用到的函數(shù)：
| Postgres SQL | ODPS SQL | MySQL |
|---|---|---|
| ::json | json_parse() | cast() 或隱式轉(zhuǎn)換 |
| json_array_elements() | regexp_count()、lateral view explode()、sequence()、json_extract()、cast() | RECURSIVE、json_extract()、json_unquote() |
| json_object_keys() | str_to_map()、map_values()、regexp_extract()、replace()、regexp_count()、lateral view explode()、sequence()、json_extract()、cast() | RECURSIVE、json_extract()、json_unquote()、json_keys() |
| -> | [] 或 json_extract() | json_extract() |
| ->> | json_extract() | json_extract() |
往期回顧：文章來源：http://www.zghlxwxcb.cn/news/detail-691437.html
阿里云大數(shù)據(jù)實戰(zhàn)記錄7：如何處理生產(chǎn)環(huán)境表單的重復數(shù)據(jù)
阿里云大數(shù)據(jù)實戰(zhàn)記錄6：修改生產(chǎn)環(huán)境表單字段數(shù)據(jù)類型
阿里云大數(shù)據(jù)實戰(zhàn)記錄5：修改生產(chǎn)環(huán)境表單字段名稱文章來源地址http://www.zghlxwxcb.cn/news/detail-691437.html
到了這里，關(guān)于阿里云大數(shù)據(jù)實戰(zhàn)記錄8：拆開 json 的每一個元素，一行一個的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
