jq 練習： geojson (或一般的 json) 轉 csv

手上有 json 檔想轉成 csv 嗎? 讓我用一個 (幾乎是) geojson 格式的檔案 stops-151.geojson 來作示範。 這是一個 "已拆掉最外層" 的 geojson 檔， 只剩 Feature 陣列， 每個圖徵是一個 Point， 代表台中市 (舊的) 151 號公車路線上的一個站牌。 對啦， 像這種很明確的小事， 現在都交給 AI 就好了。 人類只需要知道有 json 轉檔萬用瑞士刀 jq 這樣的工具， 跟 AI 的對話效率就已經可以很高。 但是我還是喜歡用部落格留下記錄， 將來用搜尋取回參考， 會比再問一次 AI 節省能源， 而且可以順便做做頭腦體操。

一、 攤平

這裡不是買股票想加碼攤平成本， 而是要把一層又一層很深的 nested object 結構攤開成淺淺一層 object。 如果你的 json 檔本來就只是一個扁平物件陣列 (array of flat objects)， 也就是只有簡單的兩層結構， 則可以直接跳到下一節。

jq '.[] | .properties + .geometry' stops-151.geojson
jq '.[] | .properties + (.geometry.coordinates as $c | .)' stops-151.geojson
jq '.[] | .properties + (.geometry | .coordinates as $c | del(.coordinates) + {longitude: $c[0], latitude: $c[1]} )' stops-151.geojson | jq -s . > flat-151.json

從這幾句學到：

1. 在一個陣列 (例如此處的 .) 的後面加 "[]"， 可將陣列展開為獨立的 sequence/stream (of objects) (jq 手冊用語)。 這讓後續步驟的思考變簡單了， 聚焦於一個元素即可。
2. 兩個 objects 可以用 "+" 合併成一個；
3. "as" 可以設定變數；
4. "del(...)" 可以刪除 object 的欄位。

最後一句的前半部 (pipe 之前)， 會產生 a sequence/stream of objects， 也就是一長串沒有用逗點分開的 objects。 後半部的 jq -s . 目的是要把它們重新包裝成一個標準的 json 陣列， 成為 an array of objects。

二、 扁平的 object 陣列轉成 csv

注意： 本節假設輸入資料裡每個 object 的 key 結構與順序完全一致!! 上節已產生一個 "扁平" 的 object 陣列， 長這樣：

[
  {
    "id": "0019",
    "name": "市警局",
    "seq": 1,
    "type": "Point",
    "longitude": 120.64759,
    "latitude": 24.15935
  },
  {
    "id": "0017",
    "name": "臺中市政府",
    "seq": 3,
    "type": "Point",
    "longitude": 120.64692,
    "latitude": 24.16435
  },
  ...
  {
    "id": "3246",
    "name": "朝陽科技大學",
    "seq": 51,
    "type": "Point",
    "longitude": 120.71551,
    "latitude": 24.07041
  }
]

首先產生 csv 的標題列： jq -r '.[0] | keys_unsorted | @csv' flat.json > stops-151.csv 首先以最前面那個元素 ".[0]" (可能是路線的起站吧) 的所有 key 名稱來產生標題列： "keys_unsorted" 會產出一個陣列， 內容為這個 object 的所有的鍵， 按照原來的順序出現。 後面的 @csv 會把它接收到的每一個陣列當成 csv 的一列來輸出， 於是產生了一列標題列。 使用 @csv 輸出時， jq 的命令列上一律加上 "-r" 選項， 避免它試圖透過加上倒斜線來 "閃躲" (正式用語： "跳脫"、 escape) 引號。

再來產生 csv 的實質內容： jq -r '.[] | [ .[] ] | @csv' flat.json >> stops-151.csv 先用 ".[]" 把 an array of objects 拆成一個 a sequence/stream of objects， 於是接下來的思考只需要以 (每) 一個 object (一個站牌) 為單位。 當 '.' 是一個 object 時， '.[]' 會取得所有欄位的值， 成為一個 sequence/stream。 外面包上 '[ ... ]'， 把此一站牌的所有欄位值建構成一個陣列、 餵給 @csv， 就成為描述此一站牌的一列資料。

最後， 把第二節的步驟寫成一個 bash script， 叫做 flatjson2csv.sh 好了：

#!/bin/bash
jq -r '.[0] | keys_unsorted | @csv' $1
for json_file in "$@" ; do
    jq -r '.[] | [ .[] ] | @csv' "$json_file"
done

這可以批次處理多個 「欄位完全相同」 的 json 檔， 把它們合併成一個 csv： flatjson2csv.sh flat-151.json flat-200.json flat-50.json

三、 結語

以上是從 這篇 以及 ChatGPT 的解說學到的。 對照： zq 版的 json 轉 csv， 你偏好哪一個呢?

最後， 如果你沒有把握 json 檔內是否每個 object 內含的欄位都跟第 0 個 object 一樣、 不多不少而且順序完全相同， 又或者如果你有很大的 json 檔或很多個 json 檔， 那或許比較實際的做法是改用 python 版： flatjson2csv.py 。 呼叫方式： python3 flatjson2csv.py -n route flat-151.json flat-132.json flat-131.json flat-158.json 以這個例子而言， 它會先把所有檔案內的 objects 巡過一遍， 根據它們的欄位聯集建立 csv 檔， 並且會在最前面加上一個名為 route 的欄位， 其值則為每列資料來源的檔案名稱 (不含 .json)。 遇有欠缺欄位的 object， 則會正確地留空。