Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create schema bv;
create table bv.user(id bigint primary key);
create table bv.user_photo (
  id bigint primary key,
  url varchar(255) not null,
  user_id bigint references bv.user(id)
);

insert into bv.user values (100), (101);
insert into bv.user_photo values
  (1, 'https://1.com', 100),
  (3, 'https://3.com', 100),
  (4, 'https://4.com', 101),
  (2, 'https://2.com', 100);

CREATE SCHEMA

CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 4

select distinct on(u.id)
  json_build_object(
    'id', u.id,
    'url', up.url
  ) user
from bv.user u
left join bv.user_photo up
  on u.id = up.user_id
order by u.id, up.id DESC

user

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

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

SELECT 2

select json_agg("user" order by id) as "user_array"
from(select distinct on(u.id) u.id,
      json_build_object('id', u.id,
                        'url', up.url) as "user"
     from bv.user u 
     left join bv.user_photo up
            on u.id = up.user_id
     order by u.id, up.id DESC) subquery;

user_array

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

SELECT 1