全部產品
Search

搜尋本產品

    MaxCompute：歸檔檔案函數

    文件中心

    MaxCompute：歸檔檔案函數

    更新時間：Sep 02, 2026

    MaxCompute 新增內建純量涵式，用於直接在 SQL 中解析歸檔檔案（archive）的位元據。無需先將歸檔檔案解壓到磁碟，即可讀取zip、jar 、tar、tar.gz、tar.bz2、tar.xz、tar.zst 等格式內的檔案中繼資料與內容。

    本文所有 SQL 樣本均為可直接在 MaxCompute 中執行的完整語句，二進位入參需使用完整的 x'...' hex 字面量。

    函數總覽

    以下兩個函數為純量涵式（Scalar Function），非資料表值函式，傳回值為複雜類型（ARRAY / MAP），可直接在 SELECT 子句中使用。

    函數名

    輸入

    傳回型別

    用途

    LIST_ARCHIVE_METADATA

    BINARY

    ARRAY<STRUCT<file_path:STRING, size:BIGINT, mtime:BIGINT>>

    列出歸檔內所有條目的元資訊

    GET_ARCHIVE_CONTENT

    BINARY [, STRING]

    MAP<STRING, STRING>

    提取歸檔內檔案名稱到檔案內容的映射

    注意事項

    • 輸入類型

      兩個函數僅接受 BINARY 輸入；若資料為 STRING，需先做類型轉換。

    • 內容解碼：

      • GET_ARCHIVE_CONTENT 的 value 以 STRING 返回，二進位內容按文本解碼；

      • 非文字檔（圖片、class 等）可能產生亂碼。

    • 記憶體佔用

      歸檔內容會被完整讀入記憶體解析，超大歸檔可能佔用較多資源，建議控制單條記錄大小。

    • 格式自動識別

      無需手動指定格式，函數依據內容位元組自動判斷。

    • pattern 僅作用於檔案名稱

      萬用字元匹配的是歸檔內條目的 file_path，不是檔案內容。

    • 目錄條目

      jar / zip 中的目錄條目（如 META-INF/）在 LIST_ARCHIVE_METADATA 中 size 為 0，在 GET_ARCHIVE_CONTENT 中 value 為空白字串。

    • mtime 可能缺失

      tar.gz 格式的 mtime 可能為 0。

    支援的歸檔和壓縮格式

    函數會根據二進位內容自動識別格式，無需指定。

    支援格式

    格式

    魔數（檔案頭）

    容器

    壓縮演算法

    zip (stored)

    50 4b 03 04

    ZIP

    stored (method 0)

    zip (deflate)

    50 4b 03 04

    ZIP

    deflate (method 8)

    jar

    50 4b 03 04

    ZIP

    通常 stored

    tar

    無統一魔數

    TAR

    無壓縮

    tar.gz

    1f 8b 08

    TAR

    gzip

    tar.bz2

    42 5a 68 (BZh)

    TAR

    bzip2

    tar.xz

    fd 37 7a 58 5a

    TAR

    xz

    tar.zst

    28 b5 2f fd

    TAR

    zstd

    格式識別原理

    • zip / jar：檢測 PK 頭（0x504b0304）。支援 zip 條目的 stored（method 0）和 deflate（method 8）兩種壓縮方式。jar 本質是 zip，會額外包含 META-INF/ 目錄條目。

    • tar.gz / tar.bz2 / tar.xz / tar.zst：先檢測外層壓縮頭（gzip 1f8b、bzip2 425a68、xz fd377a58、zstd 28b52ffd），解壓後再解析內層 tar。

    • tar：無外層壓縮，按 tar 的 512 位元組塊結構直接解析。

    LIST_ARCHIVE_METADATA

    該函數用於列出歸檔內所有條目的元資訊。

    文法格式

    LIST_ARCHIVE_METADATA(<binary_input>)

    參數說明

    binary_input參數說明：必填。BINARY類型。歸檔檔案的原始位元組內容。

    傳回值說明

    返回一個ARRAY數組ARRAY<STRUCT<file_path:STRING, size:BIGINT, mtime:BIGINT>>，數組每個元素對應歸檔內的一個條目（含目錄條目），欄位含義：

    欄位

    類型

    說明

    file_path

    STRING

    條目在歸檔內的相對路徑（如 hello.txt、META-INF/MANIFEST.MF）。

    size

    BIGINT

    條目未壓縮的位元組大小；目錄條目為 0。

    mtime

    BIGINT

    條目的修改時間，Unix 時間戳記（秒）；部分格式可能為 0。

    數組順序：按歸檔內條目的儲存順序返回。

    使用樣本

    樣本一：讀Object Table表時使用歸檔函數

    1. 上傳測試資料到OSS上test_01.zip。

    2. 建立Object Table讀取元資訊。

      -- 建立Object Table
SET odps.namespace.schema=true; 

CREATE OBJECT TABLE ot_test_01 
WITH serdeproperties ( 
 'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole') 
LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss-path>/'
;

-- 重新整理中繼資料
ALTER TABLE ot_test_01 REFRESH METADATA;

-- 查詢Object Table
SET odps.namespace.schema=true;
SELECT * FROM ot_test_01;

-- 返回結果如下：
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| key  | size       | type | last_modified | storage_class | etag | restore_info | owner_id   | owner_display_name |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| test_01.zip | 7246 | Normal | 2026-08-31 06:40:38 | Standard | 337E*****3101C02F1 | NONE| 122***014 | 122***014  |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+

    3. 使用歸檔函數：列出歸檔內所有條目的元資訊。

      SET odps.namespace.schema=true;

SELECT 
    LIST_ARCHIVE_METADATA(data) FROM 
(
    SELECT 
        GET_DATA_FROM_OSS('<project>.<schema>.ot_test_01', key) as data
    FROM ot_test_01
);

-- 返回結果如下：
+------+
| _c0  |
+------+
| [{file_path:test_01/, size:0, mtime:1786418145}, {file_path:test_01/hello.txt, size:13, mtime:1786418039}, {file_path:__MACOSX/test_01/._hello.txt, size:5106, mtime:1786418039}, {file_path:test_01/data.txt, size:13, mtime:1786418061}, {file_path:__MACOSX/test_01/._data.txt, size:5122, mtime:1786418061}] |
+------+

    樣本二：其他格式結果樣本

    zip格式

    zip格式（含 hello.txt = "hello world"、data.txt = "test data 123"）。

    SELECT LIST_ARCHIVE_METADATA(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000');

-- 返回結果如下：
[{file_path:hello.txt, size:11, mtime:1699977600}, {file_path:data.txt, size:13, mtime:1699977600}]

    jar / stored格式

    jar / stored格式（含 META-INF/、META-INF/MANIFEST.MF、hello.txt = "hello jar"）。

    SELECT LIST_ARCHIVE_METADATA(x'504b03040a0000080000f4a1bc5c000000000000000000000000090004004d4554412d494e462ffeca0000504b03040a0000080000f4a1bc5c6388d7f13a0000003a000000140000004d4554412d494e462f4d414e49464553542e4d464d616e69666573742d56657273696f6e3a20312e300d0a437265617465642d42793a20312e382e305f3334352028416c6962616261290d0a0d0a504b03040a000008000014a0bc5cdf3b649609000000090000000900000068656c6c6f2e74787468656c6c6f206a6172504b01020a000a0000080000f4a1bc5c0000000000000000000000000900040000000000000000000000000000004d4554412d494e462ffeca0000504b01020a000a0000080000f4a1bc5c6388d7f13a0000003a00000014000000000000000000000000002b0000004d4554412d494e462f4d414e49464553542e4d46504b01020a000a000008000014a0bc5cdf3b6496090000000900000009000000000000000000000000009700000068656c6c6f2e747874504b05060000000003000300b4000000c70000000000');

-- 返回結果如下：
[{file_path:META-INF/, size:0, mtime:1779970540}, {file_path:META-INF/MANIFEST.MF, size:58, mtime:1779970540}, {file_path:hello.txt, size:9, mtime:1779969640}]

-- 注意：jar 包含 META-INF/ 目錄條目，其 size 為 0。

    tar.gz格式

    tar.gz格式（含 hello.txt = "hello world"、data.csv = "test data 123"）。

    SELECT LIST_ARCHIVE_METADATA(x'1f8b08000000000002ffedd3410a83400c85e1394a4e20998edaf30c5570312038b1edf1eb74d3e2da5ac4ffdb2464179237f4298d953dcdfd8e2edaba7ed7c5baaafaf0e9cbdc7bd5e044dd0ee66c7112d964c9efe50e6228f797c738a5cee17cba68b1bae5bbfb6bfe9b55feb569afe47f0fd66793f204e22f81380000000000000000000000001cce0b293b365100280000');

-- 返回結果如下。
[{file_path:hello.txt, size:11, mtime:0}, {file_path:data.csv, size:13, mtime:0}]

-- 注意：tar.gz 的 mtime 可能為 0（取決於內層 TAR header 的時間欄位）。

    tar格式

    tar格式（含 hello.txt = "hello world"）。

    SELECT LIST_ARCHIVE_METADATA(x'68656c6c6f2e747874000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003030303036343400303030303030300030303030303030003030303030303030303133003134353234373730343030003030373634350020300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000075737461720030300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000068656c6c6f20776f726c64000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');

-- 返回結果如下。
[{file_path:hello.txt, size:11, mtime:1700000000}]

    GET_ARCHIVE_CONTENT

    該函數用於提取歸檔內檔案名稱到檔案內容的映射。

    文法格式

    -- 不帶 pattern：返迴歸檔內所有檔案的內容
GET_ARCHIVE_CONTENT(<binary_input>)

-- 帶 pattern：僅返迴文件名匹配的檔案內容
GET_ARCHIVE_CONTENT(<binary_input>, <pattern>)

    參數說明

    參數

    類型

    必填

    說明

    binary_input

    BINARY

    是

    歸檔檔案的原始位元組內容。

    pattern

    STRING

    否

    glob 風格的檔案名稱匹配模式；省略則返回全部。

    傳回值說明

    返回MAP類型MAP<STRING, STRING>，key 為檔案名稱（file_path），value 為檔案內容（以 STRING 解碼）。

    • 目錄條目（如 META-INF/）的 value 為空白字串。

    • Map 按檔案名稱字母序排列。

    Pattern 匹配規則（glob 萬用字元）

    萬用字元

    含義

    樣本

    *

    匹配任意數量字元（含空）

    *.txt → 所有 .txt 檔案

    ?

    匹配單個字元

    h?llo.txt → hello.txt

    .

    普通字面字元

    hello.* → hello.txt、hello.csv

    其他字元

    字面匹配

    data.csv → 僅 data.csv

    pattern 只匹配檔案名稱，不匹配檔案內容。匹配不到任何檔案時返回空 Map {}。

    使用樣本

    樣本一：讀Object Table表時使用歸檔函數

    1. 上傳測試資料到OSS上test_01.zip。

    2. 建立Object Table讀取元資訊。

      -- 建立Object Table
SET odps.namespace.schema=true; 

CREATE OBJECT TABLE ot_test_01 
WITH serdeproperties ( 
 'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole') 
LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss-path>/'
;

-- 重新整理中繼資料
ALTER TABLE ot_test_01 REFRESH METADATA;

-- 查詢Object Table
SET odps.namespace.schema=true;
SELECT * FROM ot_test_01;

-- 返回結果如下：
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| key  | size       | type | last_modified | storage_class | etag | restore_info | owner_id   | owner_display_name |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| test_01.zip | 7246 | Normal | 2026-08-31 06:40:38 | Standard | 337E*****3101C02F1 | NONE| 122***014 | 122***014  |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+

    3. 使用歸檔函數：提取歸檔內檔案名稱到檔案內容的映射。

      SET odps.namespace.schema=true;

SELECT 
    GET_ARCHIVE_CONTENT(data,'test_01/hello.*') FROM 
(
    SELECT 
        GET_DATA_FROM_OSS('yyy_test_dev.default.ot_test_01', key) as data
    FROM ot_test_01
);

-- 返回結果如下：
+------+
| _c0  |
+------+
| {test_01/hello.txt:hello=20world=0D=0A} |
+------+

    樣本二：其他格式結果樣本

    不帶 pattern（zip，全部檔案）

    SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000');

-- 預期返回結果。
{data.txt:test data 123, hello.txt:hello world}

-- 實際返回結果。
{
hello.txt:"hello=20world"
data.txt:"test=20data=20123"
}

    帶 *.txt（zip，只取 .txt）

    SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', '*.txt');

-- 預期返回結果。
{data.txt:test data 123, hello.txt:hello world}

-- 實際返回結果
{
data.txt:"test=20data=20123"
hello.txt:"hello=20world"
}

    帶 hello.*（zip，只取 hello 開頭）

    SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', 'hello.*');

-- 預期返回結果。
{hello.txt:hello world}

-- 實際返回結果
{
hello.txt:"hello=20world"
}

    jar格式帶 *.txt

    jar格式帶 *.txt（過濾掉 MANIFEST.MF 和目錄）

    SELECT GET_ARCHIVE_CONTENT(x'504b03040a0000080000f4a1bc5c000000000000000000000000090004004d4554412d494e462ffeca0000504b03040a0000080000f4a1bc5c6388d7f13a0000003a000000140000004d4554412d494e462f4d414e49464553542e4d464d616e69666573742d56657273696f6e3a20312e300d0a437265617465642d42793a20312e382e305f3334352028416c6962616261290d0a0d0a504b03040a000008000014a0bc5cdf3b649609000000090000000900000068656c6c6f2e74787468656c6c6f206a6172504b01020a000a0000080000f4a1bc5c0000000000000000000000000900040000000000000000000000000000004d4554412d494e462ffeca0000504b01020a000a0000080000f4a1bc5c6388d7f13a0000003a00000014000000000000000000000000002b0000004d4554412d494e462f4d414e49464553542e4d46504b01020a000a000008000014a0bc5cdf3b6496090000000900000009000000000000000000000000009700000068656c6c6f2e747874504b05060000000003000300b4000000c70000000000', '*.txt');

-- 預期返回結果。
{hello.txt:hello jar}

-- 實際返回結果。
{
hello.txt:"hello=20jar"
}

    tar.gz 帶 *.csv

    tar.gz 帶 *.csv（按副檔名過濾）。

    SELECT GET_ARCHIVE_CONTENT(x'1f8b08000000000002ffedd3410a83400c85e1394a4e20998edaf30c5570312038b1edf1eb74d3e2da5ac4ffdb2464179237f4298d953dcdfd8e2edaba7ed7c5baaafaf0e9cbdc7bd5e044dd0ee66c7112d964c9efe50e6228f797c738a5cee17cba68b1bae5bbfb6bfe9b55feb569afe47f0fd66793f204e22f81380000000000000000000000001cce0b293b365100280000', '*.csv');

-- 預期返回結果。
{data.csv:test data 123}

-- 實際返回結果。
{
data.csv:"test=20data=20123"
}

    tar 不帶 pattern

    SELECT GET_ARCHIVE_CONTENT(x'68656c6c6f2e747874000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003030303036343400303030303030300030303030303030003030303030303030303133003134353234373730343030003030373634350020300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000075737461720030300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000068656c6c6f20776f726c64000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');

-- 預期返回結果。
{hello.txt:hello world}

-- 實際返回結果。
{
hello.txt:"hello=20world"
}

    異常與邊界處理

    LIST_ARCHIVE_METADATA和GET_ARCHIVE_CONTENT兩個函數不會拋出異常，而是以優雅降級方式返回 NULL 或空集合：

    情境

    輸入

    LIST_ARCHIVE_METADATA 返回

    GET_ARCHIVE_CONTENT 返回

    非法歸檔資料

    x'deadbeef'

    NULL

    NULL

    NULL 輸入

    NULL

    NULL

    NULL

    空二進位

    x''

    []（空數組）

    {}（空 Map）

    pattern 不匹配任何檔案

    '<archive>', 'nonexistent.*'

    —

    {}

    pattern 為 NULL

    '<archive>', NULL

    —

    {}

    區別：

    • 非法資料（無法解析）返回 NULL；

    • 空輸入（x''）返回空集合。原因是空二進位是“合法但無內容”，而 deadbeef 是“格式無法識別”。

    可直接執行的邊界樣本

    -- 非法資料 → NULL
SELECT LIST_ARCHIVE_METADATA(x'deadbeef');        -- \N
SELECT GET_ARCHIVE_CONTENT(x'deadbeef');          -- \N

-- NULL 輸入 → NULL
SELECT LIST_ARCHIVE_METADATA(NULL);               -- \N
SELECT GET_ARCHIVE_CONTENT(NULL);                 -- \N

-- 空二進位 → 空集合
SELECT LIST_ARCHIVE_METADATA(x'');                -- []
SELECT GET_ARCHIVE_CONTENT(x'');                  -- {}

-- pattern 不匹配 → 空 Map
SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', 'nonexistent.*');  -- {}

-- pattern 為 NULL → 空 Map
SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', NULL);  -- {}

    複雜類型結果的展開用法

    由於傳回值類型是 ARRAY<STRUCT> 和 MAP<STRING,STRING>，可用 LATERAL VIEW EXPLODE 展開成多行。以下樣本假設存在表 archives(id BIGINT, content BINARY)，請按需替換為實際表名與列名。

    展開 LIST_ARCHIVE_METADATA 結果（逐行展示檔案中繼資料）

    -- 假設表 archives(id BIGINT, content BINARY) 儲存歸檔資料
SELECT id, meta.file_path, meta.size, meta.mtime
FROM archives
LATERAL VIEW EXPLODE(LIST_ARCHIVE_METADATA(content)) t AS meta
WHERE id = 1;

    展開 GET_ARCHIVE_CONTENT 結果（逐行展示檔案名稱與內容）

    SELECT id, k AS file_name, v AS file_content
FROM archives
LATERAL VIEW EXPLODE(GET_ARCHIVE_CONTENT(content, '*.txt')) t AS k, v
WHERE id = 1;

    按檔案大小過濾

    SELECT id, meta.file_path, meta.size
FROM archives
LATERAL VIEW EXPLODE(LIST_ARCHIVE_METADATA(content)) t AS meta
WHERE id = 1 AND meta.size > 10;

    完整樣本速查表

    以 zip 包（含 hello.txt = "hello world"、data.txt = "test data 123"）為例，hex 為 x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000'：

    目標

    SQL（省略 hex，完整版見上）

    返回

    列出檔案中繼資料

    SELECT LIST_ARCHIVE_METADATA(<zip_bin>);

    [{file_path:hello.txt, size:11, mtime:1699977600}, {file_path:data.txt, size:13, mtime:1699977600}]

    取出全部檔案內容

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>);

    {data.txt:test data 123, hello.txt:hello world}

    只取 .txt 檔案

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>, '*.txt');

    {data.txt:test data 123, hello.txt:hello world}

    只取 hello 開頭的檔案

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>, 'hello.*');

    {hello.txt:hello world}

    取不到匹配

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>, 'nonexistent.*');

    {}