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

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

まずは結果から

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

PS C:\> .\Get-PsqlJsonData.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に作ってもらいました。

SELECT SEQ, SEQ + 1 AS SEQ2 
FROM generate_series(1,10) AS SEQ;

PS C:\>  @"
>> SELECT SEQ, SEQ + 1 AS SEQ2
>> FROM generate_series(1,10) AS SEQ;
>> "@ | psql -h localhost -U noobow -d noobow
 seq | seq2
-----+------
   1 |    2
   2 |    3
   3 |    4
   4 |    5
   5 |    6
   6 |    7
   7 |    8
   8 |    9
   9 |   10
  10 |   11
(10 行)

psqlの出力をJSONにする

PostgreSQL(psqlコマンド)では元のSQLを生かしたままXMLを出力することはできないようなので、JSONで出力します。以下のSQLで結果をJSONにすることができます。

SELECT json_agg(t) FROM (元のSQL) t;

オプション無しでpsqlコマンドで実行してみると以下のような結果になります。

PS C:\Users\skywi> @"
>> SELECT json_agg(t) FROM (SELECT SEQ, SEQ + 1 AS SEQ2 FROM generate_series(1,10) AS SEQ) t;
>> "@ | psql -h server -U user -d database
        json_agg
------------------------
 [{"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}]
(1 行)

列名、行数、JSONの改行が+が表現されているという問題があるので、このままではPowerShellでパースしてJSON型のPowerShellにすることができません。
そこで-t -Aの2つのオプションをつけます。意味は以下の通りです。
-t:行数と列名が出力されなくなる
-A:改行の+が出力されなくなる

PS C:\Users\skywi> @"
>> SELECT json_agg(t) FROM (SELECT SEQ, SEQ + 1 AS SEQ2 FROM generate_series(1,10) AS SEQ) t;
>> "@ | psql -h server -U user -d database -t -A
[{"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の変数にする

SQLite版の記事を参照

JSON変数の扱い方

SQLite版の記事を参照