Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (id bigint PRIMARY KEY);

CREATE TABLE user_photo (
  id      bigint PRIMARY KEY
, url     text NOT NULL
, user_id bigint REFERENCES users(id)
);

INSERT INTO users VALUES
  (100)
, (101)
, (102)  -- no photo!
;

INSERT INTO user_photo VALUES
  (1, 'https://1.com', 100)
, (3, 'https://3.com', 100)
, (4, 'https://4.com', 101)
, (2, 'https://2.com', 100)
;

CREATE INDEX ON user_photo (user_id, id DESC) INCLUDE (url);

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 4



CREATE INDEX


-- 1 row per user
SELECT json_build_object('id', u.id, 'url', p.url) AS "user"
FROM   users u
LEFT   JOIN (
   SELECT DISTINCT ON (user_id)
          user_id, url
   FROM   user_photo
   ORDER  BY user_id, id DESC
   ) p ON p.user_id = u.id;


user


{"id" : 100, "url" : "https://3.com"}

{"id" : 101, "url" : "https://4.com"}

{"id" : 102, "url" : null}



SELECT 3


-- summary array
SELECT json_agg(sub) AS "users"
FROM  (
   SELECT u.id, p.url
   FROM   users u
   LEFT   JOIN (
      SELECT DISTINCT ON (user_id)
             user_id, url
      FROM   user_photo
      ORDER  BY user_id, id DESC
      ) p ON p.user_id = u.id
   ) sub;


users


[{"id":100,"url":"https://3.com"}, 
 {"id":101,"url":"https://4.com"}, 
 {"id":102,"url":null}]



SELECT 1


-- also skip null values
SELECT json_strip_nulls(json_agg(sub)) AS "users"
FROM  (
   SELECT u.id, p.url
   FROM   users u
   LEFT   JOIN (
      SELECT DISTINCT ON (user_id)
             user_id, url
      FROM   user_photo
      ORDER  BY user_id, id DESC
      ) p ON p.user_id = u.id
   ) sub;


users


[{"id":100,"url":"https://3.com"},{"id":101,"url":"https://4.com"},{"id":102}]



SELECT 1


-- for a small selection
SELECT json_build_object('id', u.id, 'url', p.url) AS "user"
FROM   users u
LEFT   JOIN LATERAL (
   SELECT url
   FROM   user_photo up
   WHERE  up.user_id = u.id
   ORDER  BY id DESC
   LIMIT  1
   ) p ON true
WHERE  u.id IN (100, 101, 102);  -- small selection


user


{"id" : 100, "url" : "https://3.com"}

{"id" : 101, "url" : "https://4.com"}

{"id" : 102, "url" : null}



SELECT 3


-- summary array for a small selection, skipping null values
SELECT json_strip_nulls(json_agg(sub)) AS "users"
FROM  (
   SELECT u.id, p.url
   FROM   users u
   LEFT   JOIN LATERAL (
      SELECT url
      FROM   user_photo up
      WHERE  up.user_id = u.id
      ORDER  BY id DESC
      LIMIT  1
      ) p ON true
   WHERE  u.id IN (100, 101, 102)  -- small selection
   ) sub;


users


[{"id":100,"url":"https://3.com"},{"id":101,"url":"https://4.com"},{"id":102}]



SELECT 1