[解決済み] row_to_json()と入れ子結合の併用

質問

クエリの結果をJSONにマッピングするために row_to_json() 関数を使用してクエリの結果をJSONにマップしようとしています。

結合された行を入れ子オブジェクト（1対1の関係）として表現する最適な方法を見つけるのに苦労しています。

以下は私が試したことです (セットアップコード: テーブル、サンプルデータ、クエリの順)。

-- some test tables to start out with:
create table role_duties (
    id serial primary key,
    name varchar
);

create table user_roles (
    id serial primary key,
    name varchar,
    description varchar,
    duty_id int, foreign key (duty_id) references role_duties(id)
);

create table users (
    id serial primary key,
    name varchar,
    email varchar,
    user_role_id int, foreign key (user_role_id) references user_roles(id)
);

DO $$
DECLARE duty_id int;
DECLARE role_id int;
begin
insert into role_duties (name) values ('Script Execution') returning id into duty_id;
insert into user_roles (name, description, duty_id) values ('admin', 'Administrative duties in the system', duty_id) returning id into role_id;
insert into users (name, email, user_role_id) values ('Dan', '[email protected]', role_id);
END$$;

クエリそのもの。

select row_to_json(row)
from (
    select u.*, ROW(ur.*::user_roles, ROW(d.*::role_duties)) as user_role 
    from users u
    inner join user_roles ur on ur.id = u.user_role_id
    inner join role_duties d on d.id = ur.duty_id
) row;

を使用すると ROW() を使用すると、結果のフィールドを子オブジェクトに分離できることがわかりましたが、1つのレベルに制限されているようです。これ以上 AS XXX ステートメントを挿入することができません。

で適切なレコードタイプにキャストするため、カラム名が与えられます。 ::user_roles のように、適切なレコードタイプにキャストするからです。

そのクエリの返り値がこちらです。

{
   "id":1,
   "name":"Dan",
   "email":"[email protected]",
   "user_role_id":1,
   "user_role":{
      "f1":{
         "id":1,
         "name":"admin",
         "description":"Administrative duties in the system",
         "duty_id":1
      },
      "f2":{
         "f1":{
            "id":1,
            "name":"Script Execution"
         }
      }
   }
}

私がやりたいことは、結合を追加し、結合する親の子オブジェクトとして表現できるような方法で、結合用のJSONを生成することです（繰り返しますが1対1でも構いません）。

{
   "id":1,
   "name":"Dan",
   "email":"[email protected]",
   "user_role_id":1,
   "user_role":{
         "id":1,
         "name":"admin",
         "description":"Administrative duties in the system",
         "duty_id":1
         "duty":{
            "id":1,
            "name":"Script Execution"
         }
      }
   }
}

どのように解決するのですか？

更新: PostgreSQL 9.4では、これはかなり改善されました。 の導入により to_json , json_build_object , json_object と json_build_array のように、すべてのフィールドに明示的に名前を付ける必要があるため、冗長になってしまいますが。

select
        json_build_object(
                'id', u.id,
                'name', u.name,
                'email', u.email,
                'user_role_id', u.user_role_id,
                'user_role', json_build_object(
                        'id', ur.id,
                        'name', ur.name,
                        'description', ur.description,
                        'duty_id', ur.duty_id,
                        'duty', json_build_object(
                                'id', d.id,
                                'name', d.name
                        )
                )
    )
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;

古いバージョンについては、こちらをお読みください。

1 つの行に限定されるわけではなく、ちょっと辛いだけです。複合行型のエイリアスは AS を使用することはできませんので、エイリアスされたサブクエリ式またはCTEを使用して、その効果を得る必要があります。

select row_to_json(row)
from (
    select u.*, urd AS user_role
    from users u
    inner join (
        select ur.*, d
        from user_roles ur
        inner join role_duties d on d.id = ur.duty_id
    ) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id
) row;

は http://jsonprettyprint.com/ :

{
  "id": 1,
  "name": "Dan",
  "email": "[email protected]",
  "user_role_id": 1,
  "user_role": {
    "id": 1,
    "name": "admin",
    "description": "Administrative duties in the system",
    "duty_id": 1,
    "duty": {
      "id": 1,
      "name": "Script Execution"
    }
  }
}

を使うことになります。 array_to_json(array_agg(...)) を使いたいところです。

上記のクエリは、理想的には次のように書けるはずです。

select row_to_json(
    ROW(u.*, ROW(ur.*, d AS duty) AS user_role)
)
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;

... しかし、PostgreSQLの ROW のコンストラクタは AS カラムのエイリアスを受け付けません。悲しいかな。

ありがたいことに、それらは同じように最適化されます。プランを比較してみてください。

• は ネストされたサブクエリのバージョン 対
• 後者は をネストした ROW コンストラクタ版 を実行するようにエイリアスを削除したものです。

CTEは最適化フェンスであるため、入れ子になったサブクエリのバージョンを、連鎖したCTEを使用するように言い換えると ( WITH 式) を使用するようにネストされたサブクエリのバージョンを言い換えると、パフォーマンスが低下し、同じプランにならない可能性があります。この場合、以下の点が改善されるまで、醜いネストされたサブクエリから抜け出せなくなります。 row_to_json のカラム名をオーバーライドする方法が確立されるまで、醜いネストサブクエリから抜け出せないでしょう。 ROW のコンストラクタでより直接的にカラム名を上書きする方法もあります。

とにかく一般的には、カラムを持つjsonオブジェクトを作成したい場合、原則は a, b, c というカラムを持つ json オブジェクトを作成したい場合、不正な構文を書くことができればよいということです。

ROW(a, b, c) AS outername(name1, name2, name3)

の場合、代わりに行型の値を返すスカラサブクエリを使用することができます。

(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername

または

(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername

さらに、以下のような構成が可能であることに留意してください。 json の出力は引用符で囲む必要がないことに注意してください。 json_agg の出力を row_to_json の中で、内側の json_agg の結果は文字列として引用されず、直接jsonとして取り込まれます。

例：任意の例の場合。

SELECT row_to_json(
        (SELECT x FROM (SELECT
                1 AS k1,
                2 AS k2,
                (SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) )
                 FROM generate_series(1,2) ) AS k3
        ) x),
        true
);

が出力されます。

{"k1":1,
 "k2":2,
 "k3":[{"a":1,"b":2}, 
 {"a":1,"b":2}]}

なお json_agg の製品です。 [{"a":1,"b":2}, {"a":1,"b":2}] として、再びエスケープされていません。 text のように再びエスケープされていません。

これはつまり を合成する json操作を組み合わせて行を構築できることを意味します。常に非常に複雑なPostgreSQL複合型を作成して row_to_json を呼び出す必要はありません。