2

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": "jimmys6@jimmys.com",
    "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)
)
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.