說明讀取、使用、分析與寫入 JSON 的各種程式語言方式以及 CLI 工具的使用,讓這個資訊科技圈風靡的資料格式,助你一臂之力 😀
說明
本次使用的測試資料 JSON 來自於 OpenData.gov.tw 上的 110年大專院校校別學生數,資料 SAMPLE 如下:
student.csv
學校代碼,學校名稱,日間∕進修別,等級別,總計,男生計,女生計,一年級男生,一年級女生,二年級男生,二年級女生,三年級男生,三年級女生,四年級男生,四年級女生,五年級男生,五年級女生,六年級男生,六年級女生,七年級男生,七年級女生,延修生男生,延修生女生,縣市名稱,體系別
0001,國立政治大學,D 日,B 學士,"9,556","3,920","5,636",900,"1,303",920,"1,270",900,"1,313",911,"1,327",-,-,-,-,-,-,289,423,30 臺北市,1 一般
0002,國立清華大學,D 日,B 學士,"8,888","4,883","4,005","1,267",999,"1,153","1,016","1,108",942,"1,074",865,-,-,-,-,-,-,281,183,18 新竹市,1 一般
0003,國立臺灣大學,D 日,B 學士,"16,906","9,525","7,381","2,198","1,688","2,093","1,737","2,088","1,656","2,052","1,499",197,149,173,90,-,-,724,562,30 臺北市,1 一般
使用 csvjson 服務,轉換 JSON 資料格式,提供本次各種程式語言處理所使用的測試資料。
sutdent.json
[
{
"學校代碼": "0002",
"學校名稱": "國立清華大學",
"日間∕進修別": "D 日",
"等級別": "B 學士",
"總計": "8,888",
"男生計": "4,883",
"女生計": "4,005",
"一年級男生": "1,267",
"一年級女生": 999,
"二年級男生": "1,153",
"二年級女生": "1,016",
"三年級男生": "1,108",
"三年級女生": 942,
"四年級男生": "1,074",
"四年級女生": 865,
"五年級男生": "-",
"五年級女生": "-",
"六年級男生": "-",
"六年級女生": "-",
"七年級男生": "-",
"七年級女生": "-",
"延修生男生": 281,
"延修生女生": 183,
"縣市名稱": "18 新竹市",
"體系別": "1 一般"
},
...
]
Coding
Python
List / Dict Comprehension 用的流暢,資料處理起來就順暢,還可以再搭配 Pands 給你一對翅膀 🦅
with open(r'.\student.json', 'r', encoding='utf8') as f:
datas = json.load(f)
Select Data
[(r['學校代碼'], r['學校名稱'], r['等級別'], r['總計']) for r in datas]
Select All Property Names
datas[0].keys()
Filter Data
set(r['學校名稱'] for r in datas if '臺灣' in r['學校名稱'])
Sort Data
sorted(set(r['學校名稱'] for r in datas), reverse = True)
Group By Data
def cleanData(val):
return val if str(val).isdigit() else int(val.replace(',', ''))
res = {}
for school in set(r['學校名稱'] for r in datas):
res[school] = sum(cleanData(s['總計']) for s in datas if s['學校名稱'] == school)
或者還是交給 pandas 吧..
Write Data
with open('dumps.json', 'w', encoding='utf8') as f:
json.dump(res, f, indent=2, ensure_ascii=False)
JavaScript
本是同根生,相煎何太急?JSON 之母在處理 JSON 意外的沒有太大的優勢,但卻發掘出許多值得學習的地方。
datas = require('./student.json')
Select Data
意外的沒有簡單的處理方式,在 ES6 可以使用 Object Destructuring 或者透過 lodash 的 pick
省時省力些。最大的收穫就是掌握 Object Destructuring 物件解構的技術 😂
datas.map(({學校代碼, 學校名稱, 等級別, 總計}) => ({學校代碼, 學校名稱, 等級別, 總計}))
Select All Property Names
Object.getOwnPropertyNames(datas[0])
Filter Data
new Set(datas.filter(e => e['學校名稱'].includes('臺灣')).map(e => e['學校名稱']))
Sort Data
Array.from(new Set(datas.map(e => e['學校名稱']))).sort().reverse()
Group By Data
還是交給 lodash 吧 😥 但 lodash 還是有不少的工作要做 😖
_ = require('lodash')
gb = _.groupBy(datas, e => e['學校名稱'])
cleanData = val => parseInt(('' + val).replace(',', ''))
sum = (prev, curr) => prev + curr
stats = {}
Object.keys(gb).forEach(function(e){
stats[e] = [...gb[e].map(g => cleanData(g['總計']))].reduce(sum, 0);
})
Write Data
const fs = require('fs');
fs.writeFileSync('student-stats.json', JSON.stringify(stats));
PowerShell
隨手可用,中規中矩,但對於 PowerShell 的資料結構認識有限,使用起來還是有一點卡卡 😥
$datas = Get-Content .\student.json -Encoding UTF8 | ConvertFrom-Json
Select Data
$datas | select 學校代碼,學校名稱, 等級別, 總計
Filter Data
$datas | ? {$_.學校名稱.contains("臺灣")} | % {$_.學校名稱} | Get-Unique
Sort Data
$datas | % {$_.學校名稱} | Get-Unique | Sort-Object
Write Data
SQL Server
使用 SQL 在查詢資料、處理資料完全順風順水,很好處理,用起來得心應手,舒服。但一開始載入資料略麻煩,需要定義資料型別,失去 JSON 的 Free 感,同時要匯出、寫入資料不太容易 😮
SQL Server 無法讀取 File System 的檔案來源,必須將 JSON 字串化後,搭配 OPENJSON
來讀取。
DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
{
"學校代碼": "0001",
"學校名稱": "國立政治大學",
"日間∕進修別": "D 日",
"等級別": "D 博士",
"總計": 948,
"男生計": 537,
"女生計": 411,
"一年級男生": 88,
"一年級女生": 67,
"二年級男生": 81,
"二年級女生": 62,
"三年級男生": 86,
"三年級女生": 71,
"四年級男生": 79,
"四年級女生": 62,
"五年級男生": 71,
"五年級女生": 57,
"六年級男生": 71,
"六年級女生": 40,
"七年級男生": 61,
"七年級女生": 52,
"延修生男生": "-",
"延修生女生": "-",
"縣市名稱": "30 臺北市",
"體系別": "1 一般"
},
...
'
使用 OPENJSON
需要搭配 WITH
來明確 JSON 對照的欄位以及資料型別,JSON 的路徑規則可以參考 JSON Path Expressions 😎
將結果存在 Local Temp Table (#) 相較於 Declare Temp Table (@) 不需要重複再宣告資料型別,連線結束會自動刪除。
DROP TABLE IF EXISTS #datas
SELECT * INTO #datas FROM OPENJSON(@json)
WITH (
[學校代碼] char(4) '$."學校代碼"',
[學校名稱] nvarchar(20) '$."學校名稱"',
[日間進修別] nvarchar(4) '$."日間∕進修別"',
[等級別] nvarchar(4) '$."等級別"',
[總計] char(10) '$."總計"',
[男生計] char(10) '$."男生計"',
[女生計] char(10) '$."女生計"',
[一年級男生] char(10) '$."一年級男生"',
[一年級女生] char(10) '$."一年級女生"',
[二年級男生] char(10) '$."二年級男生"',
[二年級女生] char(10) '$."二年級女生"',
[三年級男生] char(10) '$."三年級男生"',
[三年級女生] char(10) '$."三年級女生"',
[四年級男生] char(10) '$."四年級男生"',
[四年級女生] char(10) '$."四年級女生"',
[五年級男生] char(10) '$."五年級男生"',
[五年級女生] char(10) '$."五年級女生"',
[六年級男生] char(10) '$."六年級男生"',
[六年級女生] char(10) '$."六年級女生"',
[七年級男生] char(10) '$."七年級男生"',
[七年級女生] char(10) '$."七年級女生"',
[延修生男生] char(10) '$."延修生男生"',
[延修生女生] char(10) '$."延修生女生"',
[縣市名稱] nvarchar(100) '$."縣市名稱"',
[體系別] nvarchar(100) '$."體系別"'
)
Select Data
SELECT 學校代碼,學校名稱, 等級別, 總計 FROM datas
Select All Property Names
SELECT name
FROM tempdb.sys.all_columns
WHERE object_id = (
SELECT object_id FROM tempdb.sys.objects WHERE name LIKE N'#datas%')
Filter Data
SELECT DISTINCT 學校名稱 FROM #datas WHERE 學校名稱 LIKE N'%臺灣%'
Sort Data
SELECT DISTINCT 學校名稱 FROM #datas ORDER BY 學校名稱 ASC
Group By Data
SELECT 學校名稱, SUM(CAST(REPLACE(總計, ',', '') AS INT)) '總計' FROM #datas
GROUP BY 學校名稱
Write Data
方式一:啟用 SQL CMD Mode 來輸出資料,但會有討厭的 Header 暫時無解 🙄
:OUT C:\temp\dumps.json
SELECT 學校名稱, SUM(CAST(REPLACE(總計, ',', '') AS INT)) '總計'
FROM #datas
GROUP BY 學校名稱
ORDER BY 總計 DESC
FOR JSON AUTO;
方式二:直接使用 SSMS GUI,對結果剪貼並另存新檔
C#
使用熱門的套件 JSON.NET 來處理,使用到的 Library。
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
主要的執行程式
string json = "";
using (StreamReader r = new StreamReader(@"C:\temp\110_sutdents.json"))
{
json = r.ReadToEnd();
}
var settings = new JsonSerializerSettings();
settings.Converters = new List<JsonConverter> { new DecimalConverter() };
List<SchoolData> datas = JsonConvert.DeserializeObject<List<SchoolData>>(json, settings);
讀取 JSON 會對照轉換為 C# 的類別,所以需要自行定義類別,這邊藉由 json2csharp 的協助,只要貼上 JSON,就可以自動對照產生類別,有效減少敲碼的時間 😄
public class SchoolData
{
public string 學校代碼 { get; set; }
public string 學校名稱 { get; set; }
[JsonProperty("日間∕進修別")]
public string 日間進修別 { get; set; }
public string 等級別 { get; set; }
public decimal 總計 { get; set; }
public decimal 男生計 { get; set; }
public decimal 女生計 { get; set; }
public decimal 一年級男生 { get; set; }
public decimal 一年級女生 { get; set; }
public decimal 二年級男生 { get; set; }
public decimal 二年級女生 { get; set; }
public decimal 三年級男生 { get; set; }
public decimal 三年級女生 { get; set; }
public decimal 四年級男生 { get; set; }
public decimal 四年級女生 { get; set; }
public decimal 五年級男生 { get; set; }
public decimal 五年級女生 { get; set; }
public decimal 六年級男生 { get; set; }
public decimal 六年級女生 { get; set; }
public decimal 七年級男生 { get; set; }
public decimal 七年級女生 { get; set; }
public string 延修生男生 { get; set; }
public string 延修生女生 { get; set; }
public string 縣市名稱 { get; set; }
public string 體系別 { get; set; }
}
實際在轉換上碰上一個問題,資料為數字包含逗點 (string number with comma),要轉換為數字 (decimal) 的問題,無法自然使用 JsonConvert.DeserializeObject
來處理。好在 JSON.NET 提供客製 Converter 的能力,所以利用客製 Converter - DecimalConverter
來進行資料的轉換處理。
需要處理的情況包含數字包含逗點 (例如 “3,974”) 以及數值為 “-“ 的情況,在 Converter 中,讀入當下的數值為 token,可以藉由判別型別 (Data Type) 的方式來決定如何處理。
class DecimalConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return (objectType == typeof(decimal) || objectType == typeof(decimal?));
}
public override object ReadJson(
JsonReader reader, Type objectType,
object existingValue, JsonSerializer serializer)
{
JToken token = JToken.Load(reader);
if (token.Type == JTokenType.Float || token.Type == JTokenType.Integer)
{
return token.ToObject<decimal>();
}
if (token.Type == JTokenType.String)
{
if (token.ToString() == "-")
{
return new Decimal(0.0);
}
return Decimal.Parse(token.ToString().Replace(",", ""));
}
if (token.Type == JTokenType.Null && objectType == typeof(decimal?))
{
return new Decimal(0.0);
}
throw new JsonSerializationException(
$"Unexpected token type: {token.Type.ToString()}");
}
public override void WriteJson(
JsonWriter writer, object value, JsonSerializer serializer)
{
throw new NotImplementedException();
}
}
Select Data
datas.Select(
e => new { e.學校名稱, e.學校代碼, e.等級別, e.總計 }).ToList()
Select All Property Names
typeof(SchoolData).GetProperties().Select(e => e.Name).ToList()
Filter Data
datas.Where(e => e.學校名稱.Contains("臺灣"))
.Select(e => e.學校名稱).Distinct().ToList()
Sort Data
datas.OrderBy(e => e.學校名稱)
.Select(e => e.學校名稱).Distinct().ToList()
Group By Data
foreach (IGrouping<string, SchoolData> group in datas.GroupBy(i => i.學校名稱))
{
Console.WriteLine($"{group.Key} {datas.Where(i => i.學校名稱 == group.Key).Sum(i => i.總計)}");
}
Write Data
var jsonString = JsonConvert.SerializeObject(
datas.FirstOrDefault(),
new JsonSerializerSettings { Formatting = Formatting.Indented }
)
File.WriteAllText(@"C:\temp\data.json", jsonString);