SQLite(sqlite3コマンド)の実行結果をJSONにしてPowerShellで扱う

この記事は、先日書いた以下の記事のSQLite(sqlite3コマンド)版です。

まずは結果から

サンプルスクリプトは以下のGistに格納しました。
https://gist.github.com/noobow34/e3e98b60e33336dd7b62765ab06e4bbc

PS C:\> .\Get-SQLiteJsonData.ps1
-----------行を指定して使用する-----------

SEQ SEQ2
--- ----
  2    3
-----------行と列を指定して使用する-----------
3
-----------ループで処理する場合-----------
1:2
2:3
3:4
4:5
5:6
6:7
7:8
8:9
9:10
10:11

やりかた

扱うデータ

Oracle版で使ったのと同じ結果になるSQLをChatGPTに作ってもらいました。

WITH RECURSIVE Numbers AS (
    SELECT 1 AS SEQ
    UNION ALL
    SELECT SEQ + 1 FROM Numbers WHERE SEQ < 10
)
SELECT SEQ, SEQ + 1 AS SEQ2 FROM Numbers;

PS C:\>  @"
>> WITH RECURSIVE Numbers AS (
>>     SELECT 1 AS SEQ
>>     UNION ALL
>>     SELECT SEQ + 1 FROM Numbers WHERE SEQ < 10
>> )
>> SELECT SEQ, SEQ + 1 AS SEQ2 FROM Numbers;
>> "@ | sqlite3
1|2
2|3
3|4
4|5
5|6
6|7
7|8
8|9
9|10
10|11

sqlite3の出力をJSONにする

Oracle版では出力をXMLとしていましたが、SQLiteでは普段使っているSQLをそのまま使いつつ（XMLを出力するように書き換えをせずに）XMLを出力することはできないようだったので、代わりにJSONを出力することにしました。JSONであれば簡単に出力することができ、かつPowerShellでオブジェクト化することもできます。
sqlite3コマンドで出力をJSONにするのは.monde jsonとするだけです。また、sqlite3コマンドではSQL*Plusの様に余計なメッセージは出ないので、特にオプションを加える必要はありません。

PS C:\> @"
>> .mode json
>> WITH RECURSIVE Numbers AS (
>>      SELECT 1 AS SEQ
>>      UNION ALL
>>      SELECT SEQ + 1 FROM Numbers WHERE SEQ < 10
>>  )
>>  SELECT SEQ, SEQ + 1 AS SEQ2 FROM Numbers;
>> "@ | sqlite3
[{"SEQ":1,"SEQ2":2},
{"SEQ":2,"SEQ2":3},
{"SEQ":3,"SEQ2":4},
{"SEQ":4,"SEQ2":5},
{"SEQ":5,"SEQ2":6},
{"SEQ":6,"SEQ2":7},
{"SEQ":7,"SEQ2":8},
{"SEQ":8,"SEQ2":9},
{"SEQ":9,"SEQ2":10},
{"SEQ":10,"SEQ2":11}]

JSONをPowerShellの変数にする

PowerShellではConvertFrom-JsonコマンドでJSONをオブジェクト化することができますが、今回のsqlite3コマンドの結果の改行が含まれるJSONではエラーになってしまうようだったのでOut-String -NoNewlineで改行なしにしてからConvertFrom-Jsonコマンドに渡しました。
結果、以下の様にすれば$json変数にjsonがオブジェクト化されて格納され、属性名でアクセスできるようになります。

$json = $execSql | sqlite3 databasefile.db | Out-String -NoNewline | ConvertFrom-Json

JSON変数の扱い方

$json自体が配列になっているので$json[0]の様にインデックスを指定して扱います。あとは、$json[0].SEQの様に属性名をそのままプロパティとして扱えます。