テーブルの全レコードをJSON配列で取得したい

Question

前提・実現したいこと

PostgresSQLにて、テーブル内容をJSON配列として1つのオブジェクトで取得したいです。

該当のソースコード

【テーブルその1】
CREATE TABLE table_number_of_people (
  position_id integer
  , info text         -- 日付と訪問回数を有するJSONがカンマ区切りで格納されている。
);

position_id	info
1	{"{"date":"2021/10/10", "count":4}", "{"go_date":"2021/11/11", "count":10}"}
2	{"{"date":"2021/10/10", "count":6}", "{"go_date":"2021/11/11", "count":2}"}
```
【テーブルその2】
CREATE TABLE table_position_master (
position_id integer
, position_name text
);
```
position_id	position_name
:--	--:
1	position_A
2	position_B
```
【クエリ】
SELECT
json_build_object(t.*)
FROM (
SELECT

table_position_master.position_name
, table_number_of_people.info

FROM
table_position_master
INNER JOIN
table_number_of_people
ON
table_position_master.position_id = table_number_of_people.position_id
) AS t;

### 発生している問題・エラーメッセージ
テーブル内容の1行目だけ取得されてしまいます。

【理想】全レコードが1つのJSON配列に格納される。
[
{
"position":"position_A"
, "info":[
{
"go_date":"2021/10/10"
, "count":4
}
, {
"go_date":"2021/11/11"
, "count":10
}
]
}
, {
"position":"position_B"
, "info":[
{
"go_date":"2021/11/10"
, "count":6
}
, {
"go_date":"2021/11/11"
, "count":2
}
]
}
]

【結果】1レコードのみJSON配列に格納される。
[
{
"position":"position_A"
, "info":[
{
"go_date":"2021/10/10"
, "count":4
}
, {
"go_date":"2021/11/11"
, "count":10
}
]
}
]

### 補足情報

PostgreSQLはVer.14です。
table_number_of_people.infoにカンマ区切りのJSONで持っていますが、最悪テーブル設計レベルで以下のように変更することも考えています。しかし、こちらの場合も取得する方法がわからず手が止まっています。

CREATE TABLE table_number_of_people (
position_id integer
, go_date date
, count integer
);

|position_id|go_date|count|
|:--|:--:|--:|
|1|2021/11/10|4|
|1|2021/11/11|10|
|2|2021/11/10|6|
|2|2021/11/11|2|

どちらのテーブル構造でも構いません。よろしくお願いいたします。

Comments

[hoshi-takanori]

こんな感じ？
https://qiita.com/kumazo/items/483f47360f8b61a9fbb9

[Fur0]

紹介いただいた文献を参考にしたらできました！ありがとうございます！

Answer 1

以下のサイトで解決できました！
リンク内容