Populate nested associations using PostgreSQL

Question

I have three tables in my PostgreSQL database:

• user
• address
• city

My tables have self-associations like user -[ address -[ city where -[ represents a 1:n association.

I have a simple query:

SELECT
    *,
    ST_Distance_Sphere(ST_MakePoint(-22.4149023, -47.56513940000002), "geolocation") AS distance,
    row_to_json(addr.*) as address 
FROM "user" as u 
LEFT JOIN
(
    SELECT *, row_to_json(cty.*) as city 
    FROM "address" 
    LEFT JOIN "city" ON address.city_id = city.city_id 
    WHERE user_id = u.user_id
) as addr 
WHERE u.user_name LIKE '%Tod%' 
ORDER BY distance ASC

How can I retrieve a nested object in one query, populating all columns of all tables to obtain a result like:

  {
    "userId": 148,
    "userType": 1,
    "userName": "Jimmy's Restaurante",
    "email": "[email protected]",
    "phoneNumber": "588559944",
    "password": "$2a$10$q9j2O.240kXTxa91KgocD.FPZyUC847S8B9GcJ5fNqPE9jntZuH8y",
    "oppId": "1cd1f75f99c6606061996a2e5561a86b9b043210",
    "accessToken": null,
    "deviceToken": null,
    "addressId": 66,
    "dni": null,
    "birthDate": null,
    "status": false,
    "owner": "Jeremy Hopkins",
    "description": "This is the Jeremy Hopkins Restaurant, a.k.a         Jimmy's",
    ,
    "address": {
      "addressId": 66,
      "addressLine1": "Rua Dona Alexandrina 2450",
      "addressLine2": "Piso2 Dto.4",
      "postalCode": "13566-290",
      "city": {
        "cityId": 1,
        "provinceId": 1,
        "name": "La Plata"
      }
    },
    "distance": 263.67136658372544,
    "imageUrl": "http://cdn2.colorir.com/desenhos/pintar/loja-com-toldo_2.png"
  }

My database SQL structure:

User table

CREATE TABLE public."user"
(
  user_id integer NOT NULL DEFAULT nextval('user_user_id_seq'::regclass),
  user_name character varying(255) NOT NULL,
  email character varying(255) NOT NULL,
  user_type integer,
  phone_number character varying(255),
  password character varying(255) NOT NULL,
  opp_id integer,
  birth_date date,
  dni character varying(255),
  access_token character varying(255),
  device_token character varying(255),
  status boolean,
  owner character varying(255),
  cuit character varying(255),
  description character varying(255),
  geolocation geometry(Point),
  image_url character varying(255),
  pin character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_email_key UNIQUE (email),
  CONSTRAINT user_phone_number_key UNIQUE (phone_number),
  CONSTRAINT user_user_name_key UNIQUE (user_name)
)

Address table

CREATE TABLE public.address
(
  id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
  description character varying(255),
  address_line_1 character varying(255),
  address_line_2 character varying(255),
  postal_code character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  city_id integer,
  user_id integer,
  CONSTRAINT address_pkey PRIMARY KEY (id),
  CONSTRAINT address_city_id_fkey FOREIGN KEY (city_id)
      REFERENCES public.city (city_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT address_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES public."user" (user_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL
)

City table

CREATE TABLE public.city
(
  city_id integer NOT NULL DEFAULT nextval('city_city_id_seq'::regclass),
  name character varying(255),
  "createdAt" timestamp with time zone NOT NULL,
  "updatedAt" timestamp with time zone NOT NULL,
  CONSTRAINT city_pkey PRIMARY KEY (city_id)
)

Answer 1

PostgreSQL 9.6+

PostgreSQL 9.6 adds jsonb_insert

You need to know that when you SELECT tablename FROM tablename you get a composite type that represents the row. You can feed this to the to_jsonb like such,

SELECT address.id AS address_id, to_jsonb(address)
FROM address;

SELECT user_id, to_jsonb(user)
FROM user;

SELECT city_id, to_jsonb(city)
FROM city;

Then realizing that the user is your outermost part of the JSON object, work inward by inserting the row type with jsonb_insert

SELECT jsonb_insert(
  to_jsonb(u),
  'address',
  a
)
FROM user AS u
JOIN address AS a
  ON u.id = a.user_id;

This should display the basic user with address, now you need the city too.

SELECT jsonb_insert(
  to_jsonb(u),
  'address',
  jsonb_insert(to_jsonb(a), 'city', to_jsonb(c))
)
FROM user AS u
JOIN address AS a
  ON u.id = a.user_id
JOIN city AS c
  USING (city_id);

And, that should work for you.