DuckDB 解析 JSON 資料：深入技巧與效能提升

– 7 分鐘閱讀

在資料科學的浪潮下，JSON 已經成為最普遍的資料交換格式之一。DuckDB 作為新一代的分析型資料函式庫，天生就對 JSON 提供了良好的支援。不過，很多人只停留在使用 read_json() 讀取檔案的階段，並沒有真正發揮 DuckDB 的效能優勢。今天，玄貓就來分享一些 DuckDB 處理 JSON 的進階技巧，讓你體驗真正的速度與效率。

很多時候，我們遇到的 JSON 資料並不是那麼「乾淨」。例如，資料可能巢狀在多層結構中，或者關鍵欄位分散在不同的 JSON 物件裡。用傳統的方法處理這種資料，往往需要寫一堆積繁瑣的程式碼，效能也差強人意。但 DuckDB 提供了一套優雅的解決方案，那就是結合 SQL 和內建函式，直接在資料函式庫層級進行 JSON 解析和資料重組。

舉個例子，假設你的 JSON 資料結構長這樣：

[
  {"id": 1, "name": "Alice", "address": {"city": "Taipei", "zip": "106"}},
  {"id": 2, "name": "Bob", "address": {"city": "Tainan", "zip": "700"}}
]

想要取出 city 和 zip 資訊，一般做法是用程式語言讀取 JSON 後再逐層解析。但在 DuckDB 中，你只需要一行 SQL 就能搞定：

SELECT id, name, address['city'] AS city, address['zip'] AS zip FROM read_json('data.json');

是不是簡潔又高效？DuckDB 直接把 JSON 解析的過程放到了資料函式庫層級，省去了應用程式層級的處理，效能自然提升不少。

更進一步，如果你的 JSON 資料更複雜，例如包含陣列或多層巢狀結構，DuckDB 也能輕鬆應對。利用 unnest() 函式，可以將陣列展開成多行，再結合 SQL 的篩選和聚合功能，就能快速提取你想要的資訊。

除了 read_json()，DuckDB 還提供了 read_json_objects() 函式，可以直接將 JSON 物件轉換成表格，更方便後續的分析。而與，DuckDB 還支援一次讀取多個 JSON 檔案，對於處理大量資料的場景非常實用。

總之，DuckDB 的 JSON 處理能力遠比你想像的強大。善用 SQL 和內建函式，你就能把複雜的 JSON 資料轉換成易於分析的表格，大幅提升資料處理的效率。

DuckDB 玩轉 JSON：資料讀取的進階技巧

在資料分析的領域中，JSON 格式的檔案無處不在。DuckDB 作為一個高效能的分析型資料函式庫，提供了強大的 JSON 處理能力。本文將探討如何利用 DuckDB 讀取和操作 JSON 資料，讓你輕鬆駕馭各種複雜的 JSON 結構。

妙用 records 引數：控制 JSON 載入方式

DuckDB 的 read_json_auto() 函式能自動偵測 JSON 檔案的格式並載入資料。預設情況下，它會將 JSON 物件解封裝成獨立的欄位。但有時，我們可能需要將整個 JSON 物件作為單一欄位載入。這時，records 引數就派上用場了。

例如，我們有一個名為 json1.json 的檔案，內容如下：

[
    {"id": 1, "name": "Sarah Johnson", "address": "4321 Oak Street", "email": "sarah@example.com", "weight": 140.5},
    {"id": 2, "name": "David Martinez", "address": "789 Maple Avenue", "email": "david@example.com", "weight": 155.0},
    {"id": 3, "name": "Emily Wilson", "address": "567 Pine Road", "email": "emily@example.com", "weight": 200.1}
]

若要將每個 JSON 物件作為一行，並將所有鍵/值對放入單一欄位，可以這樣做：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT *
    FROM read_json_auto('json1.json', records = false)
""").df()

print(df)

這段程式碼會將 json1.json 中的每個 JSON 物件轉換為 DuckDB 表格中的一行，所有資料都儲存在名為 json 的單一欄位中。

精準選取：載入 JSON 檔案的特定欄位

有時候，我們只需要 JSON 檔案中的特定欄位。read_json_auto() 函式允許我們指定要載入的欄位名稱。

例如，若只想載入 name 和 email 欄位，可以這樣做：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT name, email
    FROM read_json_auto('json1.json')
""").df()

print(df)

這段程式碼只會從 json1.json 載入 name 和 email 欄位，並將它們作為 DuckDB 表格中的兩個欄位。

read_json() 函式：手動控制 JSON 載入

read_json_auto() 實際上是 read_json() 函式的別名，並預設開啟自動偵測功能。在大多數情況下，read_json_auto() 就能滿足需求。然而，在某些特殊情況下，自動偵測可能失效，這時就需要使用 read_json() 函式手動指定格式和 schema。

JSON 陣列：指定欄位型別

假設 json1.json 包含 JSON 物件的陣列。使用 read_json() 函式時，可以將 format 設為 auto，並指定要載入的欄位及其型別：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT *
    FROM read_json('json1.json',
        format = 'auto',
        columns = {
            id:'INTEGER',
            name:'STRING',
            weight:'FLOAT'
        })
""").df()

print(df)

在這個例子中，我們明確指定了 id 欄位為整數、name 欄位為字串、weight 欄位為浮點數。

format 引數支援以下幾種值：

  • array
  • newline_delimited 或 nd
  • unstructured
  • auto

若要讀取所有鍵/值對，只需省略 columns 引數即可：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT *
    FROM read_json('json1.json', format = 'auto')
""").df()

print(df)

NDJSON：換行符分隔的 JSON

另一種常見的 JSON 格式是換行符分隔的 JSON (Newline-Delimited JSON, NDJSON)。在 NDJSON 檔案中，每個 JSON 物件佔據一行，並以換行符分隔。

假設我們有一個名為 json1_a.json 的檔案，內容如下：

{"id": 1, "name": "Sarah Johnson", "address": {"line1":"4321 Oak Street Apartment","line2":"304 Los Angeles", "state":"CA", "zip":90001}, "email":"sarah_johnson478@gmail.com", "weight": 140.50}
{"id": 2, "name": "David Martinez", "address": {"line1":"789 Maple Avenue ","line2":"Suite 102 New York", "state":"NY","zip":10001}, "email":"david_martinez431@gmail.com", "weight": 155.0}
{"id": 3, "name": "Emily Wilson", "address": {"line1":"567 Pine Road Unit 5B Chicago", "state":"IL", "zip":60601}, "email":"emily_wilson998@gmail.com", "weight": 200.1}

要使用 read_json() 函式載入 NDJSON 檔案，需要將 format 設為 newline_delimited：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT *
    FROM read_json('json1_a.json',
        format = 'newline_delimited',
        columns = {
            id:'INTEGER',
            name:'STRING',
            weight:'FLOAT'
        })
""").df()

print(df)

除了 newline_delimited 之外，還可以使用 nd 或 unstructured 格式。unstructured 格式可以處理任何型別的 JSON，包括 NDJSON 以及更自由或不規則的 JSON。

另一種讀取 NDJSON 檔案的方式是使用 read_ndjson_auto() 函式，它不需要指定 format 引數：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT *
    FROM read_ndjson_auto('json1_a.json',
        columns = {
            id:'INTEGER',
            name:'STRING',
            weight:'FLOAT'
        })
""").df()

print(df)

巢狀 JSON：處理複雜結構

JSON 資料可能包含巢狀結構，也就是說，JSON 物件的值可以是另一個 JSON 物件。

考慮以下名為 json2.json 的檔案：

[
    {
        "id": 1,
        "name": "Sarah Johnson",
        "address": {
            "line1": "4321 Oak Street Apartment",
            "line2": "304 Los Angeles",
            "state": "CA",
            "zip": 90001
        },
        "email": "sarah_johnson478@gmail.com",
        "weight": 140.50
    },
    {
        "id": 2,
        "name": "David Martinez",
        "address": {
            "line1": "789 Maple Avenue ",
            "line2": "Suite 102 New York",
            "state": "NY",
            "zip": 10001
        },
        "email": "david_martinez431@gmail.com",
        "weight": 155.0
    },
    {
        "id": 3,
        "name": "Emily Wilson",
        "address": {
            "line1": "567 Pine Road Unit 5B Chicago",
            "state": "IL",
            "zip": 60601
        },
        "email": "emily_wilson998@gmail.com",
        "weight": 200.1
    }
]

在這個例子中，address 鍵的值是一個 JSON 物件，包含 line1、line2、state 和 zip 等鍵。

要載入這個檔案，可以使用 read_json() 函式：

import duckdb

conn = duckdb.connect(':memory:')
df = conn.execute("""
    SELECT *
    FROM read_json('json2.json')
""").df()

print(df)

玄貓結語

DuckDB 提供了多種方式來讀取和處理 JSON 資料，無論是簡單的 JSON 陣列還是複雜的巢狀結構，都能輕鬆應對。透過靈活運用 read_json_auto() 和 read_json() 函式，你可以充分利用 DuckDB 的 JSON 處理能力，加速資料分析的流程。

DuckDB 讀取 JSON 檔案的進階技巧：玄貓的實戰經驗分享

在資料分析的世界裡，JSON 格式的檔案無處不在。DuckDB 作為一個高效能的分析型資料函式庫，自然也提供了強大的 JSON 支援。但如何靈活運用 DuckDB 讀取各種複雜的 JSON 檔案，將資料轉換成易於分析的表格形式，就是一門學問了。玄貓將分享一些在實際專案中積累的經驗，讓你輕鬆駕馭 DuckDB 的 JSON 讀取功能。

單一欄位到多欄位：JSON 資料的解構藝術

有時候，JSON 檔案中的資料會被塞在同一個欄位裡，就像這樣：

[
  {
    "id": 1,
    "name": "玄貓",
    "address": "台灣台北市信義區",
    "email": "blackcat@example.com"
  }
]

如果想要把 address 欄位拆分成 city、region 等多個欄位，該怎麼做呢？DuckDB 提供了簡單的方法，可以在 SQL 語法中直接指定 JSON 結構中的 key：

import duckdb
conn = duckdb.connect()

conn.execute('''
SELECT
    id,
    name,
    address,
    email
FROM read_json_objects('data.json')
''').df()

執行這段程式碼，你會發現 address 欄位的值現在都包含在一個單獨的欄位中。

如果想要將 address 欄位的值顯示在各個單獨的欄位中，可以透過在 SQL 陳述式中指定各個鍵來完成：

conn.execute('''
SELECT
    id,
    name,
    address['city'] as city,
    address['region'] as region,
    email
FROM read_json_objects('data.json')
''').df()

這樣一來，address 欄位裡的資料就會被拆解成多個欄位，方便後續的分析。

JSON 巢狀結構的解析：深入虎穴，取得關鍵資訊

更複雜的情況是，JSON 檔案中可能包含多層巢狀結構，例如：

[
  {
    "id": 1,
    "name": "玄貓",
    "address": {
      "line1": "信義路",
      "location": {
        "city": "台北",
        "zip": "110"
      }
    },
    "email": "blackcat@example.com"
  }
]

要讀取 location 裡的 city 和 zip，只需要在 SQL 語法中用 [] 連續指定 key 就可以了：

conn.execute('''
SELECT
    id,
    name,
    address['line1'] as line1,
    address['location']['city'] as city,
    address['location']['zip'] as zip,
    email
FROM read_json_objects('data.json')
''').df()

這種語法非常直觀，即使面對再複雜的 JSON 結構，也能輕鬆應對。

客製化 JSON 檔案的讀取：解鎖更多可能性

有時候，JSON 檔案的結構並不規律，例如，最外層是一個 people 的 key，裡面的 value 才是 JSON 物件的陣列：

{
  "people": [
    {
      "id": 1,
      "name": "玄貓",
      "address": {
        "city": "台北",
        "zip": "110"
      },
      "email": "blackcat@example.com"
    }
  ]
}

直接用 read_json() 讀取，會發現 people 的 value 被讀到同一個欄位裡。這時候，就需要 unnest() 函式來幫忙了。unnest() 可以把陣列中的每個元素都轉換成一列：

conn.execute('''
SELECT unnest(people) p
FROM read_json_objects('data.json')
''').df()

這樣，每個 JSON 物件都會變成一列。接下來，就可以用前面介紹的方法，把每一列的內容解開：

conn.execute('''
SELECT
    p.id,
    p.name,
    p.address['city'] as city,
    p.address['zip'] as zip,
    p.email
FROM
(
    SELECT unnest(people) p
    FROM read_json_objects('data.json')
)
''').df()

或者，你也可以用更簡潔的寫法：

conn.execute('''
SELECT
    p['id'],
    p['name'],
    p['address']['city'] as city,
    p['address']['zip'] as zip,
    p['email']
FROM
(
    SELECT unnest(people) p
    FROM read_json_objects('data.json')
)
''').df()

這兩種寫法是等價的，選擇你喜歡的就好。

一次讀取多個 JSON 檔案：資料整合的利器

有時候，資料會分散在多個 JSON 檔案中。如果想要一次讀取所有檔案，DuckDB 也提供了簡單的解決方案：

conn.execute('''
SELECT
    *
FROM read_json_objects(['data1.json', 'data2.json'])
''').df()

只需要把所有檔案名放到一個陣列裡，傳給 read_json() 就可以了。

不過，需要注意的是，當讀取多個 JSON 檔案時，DuckDB 會自動推斷每個欄位的資料型別。如果同一個欄位在不同的檔案中有不同的型別（例如，一個檔案是字串，另一個檔案是數字），DuckDB 可能會強制轉換型別，導致資料不一致。為了避免這種情況，建議在讀取檔案時，明確指定每個欄位的資料型別。

玄貓

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。