配列の順番を考慮するSQLの書き方

1対nの関係がある時、IDしか興味が無い場合に中間テーブルを使わずに1側のテーブルにID配列を持たせることがあります。

CREATE TABLE posts (
  id INT
  ,content TEXT
  ,image_id_array INT[]
  ,PRIMARY KEY (id)
);
CREATE TABLE images (
  id INT
  ,width INT
  ,height INT
  ,url TEXT
  ,PRIMARY KEY (id)
);

postとimageを一緒に取得するSQLを書きます。

SELECT
  t1.id
  ,t1.content
  ,jsonb_agg(jsonb_build_object(
    'url'
    ,t2.url
    ,'width'
    ,t2.width
    ,'height'
    ,t2.height
  )) AS image_json
FROM
  posts AS t1
  LEFT OUTER JOIN images AS t2 ON (
    t2.id = ANY(t1.image_id_array)
  )
GROUP BY
  t1.id
  ,t1.content
;

ここで配列の順番に意味があったとします。JSONには配列の順番に生成してほしいです。

SELECT
  t1.id
  ,t1.content
  ,jsonb_agg(jsonb_build_object(
    'url'
    ,t3.url
    ,'width'
    ,t3.width
    ,'height'
    ,t3.height
  ) ORDER BY t2.line_number) AS image_json
FROM
  posts AS t1
  LEFT OUTER JOIN unnest(t1.image_id_array) 
    WITH ORDINALITY AS t2(image_id, line_number) ON TRUE
  LEFT OUTER JOIN images AS t3 ON (
    t2.image_id = t3.id
  )
GROUP BY
  t1.id
  ,t1.content
;

unnestしたテーブルはLEFT OUTER JOINしないとimage_id_arrayが空だったりNULLだったりするとpostのレコードが取得できなくなります。