Working With Json 使用各種程式語言玩轉 JSON 資料格式

1. 說明
2. Coding
   1. Python
   2. JavaScript
   3. PowerShell
   4. SQL Server
   5. C#
   6. jq

---

說明讀取、使用、分析與寫入 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);

jq

jq