Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table restaurants (id int generated by default as identity primary key,
  name text,address text);
insert into restaurants(name,address) select 'restaurant1','180 Prince St, New York, NY 10012, United States';
create table restaurants_social (
  id int references restaurants(id),
  instagram text, 
  facebook text,
  x text);
insert into restaurants_social values 
  (1,'instagram.com/rastaurant1','fb.com/rest_1','x.com/ristoranti1');

CREATE TABLE



INSERT 0 1



CREATE TABLE



INSERT 0 1


select
  e.*,
  c.*
from
  restaurants e
  inner join restaurants_social c on e.id = c.id;


id
name
address
id
instagram
facebook
x


1
restaurant1
180 Prince St, New York, NY 10012, United States
1
instagram.com/rastaurant1
fb.com/rest_1
x.com/ristoranti1



SELECT 1


select
  e.*,
  c
from
  restaurants e
  inner join restaurants_social c on e.id = c.id;


id
name
address
c


1
restaurant1
180 Prince St, New York, NY 10012, United States
(1,instagram.com/rastaurant1,fb.com/rest_1,x.com/ristoranti1)



SELECT 1


select
  to_jsonb(e) || jsonb_build_object('social_media',c)
from
  restaurants e
  inner join restaurants_social c on e.id = c.id;


?column?


{"id": 1, "name": "restaurant1", "address": "180 Prince St, New York, NY 10012, United States", "social_media": {"x": "x.com/ristoranti1", "id": 1, "facebook": "fb.com/rest_1", "instagram": "instagram.com/rastaurant1"}}



SELECT 1


select
  jsonb_pretty(to_jsonb(e) || jsonb_build_object('social_media',c))
from
  restaurants e
  inner join restaurants_social c on e.id = c.id;


jsonb_pretty


{
    "id": 1,
    "name": "restaurant1",
    "address": "180 Prince St, New York, NY 10012, United States",
    "social_media": {
        "x": "x.com/ristoranti1",
        "id": 1,
        "facebook": "fb.com/rest_1",
        "instagram": "instagram.com/rastaurant1"
    }
}



SELECT 1


select distinct on (e.*)
  e.*,
  array_agg(c)over(partition by e.id)
from
  restaurants e
  inner join restaurants_social c on e.id = c.id
order by e.*;


id
name
address
array_agg


1
restaurant1
180 Prince St, New York, NY 10012, United States
{"(1,instagram.com/rastaurant1,fb.com/rest_1,x.com/ristoranti1)"}



SELECT 1