title: Create nested json from sql query postgres 9.4
body: <p>I need to get as a result from query fully structured JSON. 
  I can see in postgres that there are some built in functions that may be useful.</p>
  
  <p>As an example I created a structure as follows:</p>
  
  <pre><code>    -- Table: person
  
  -- DROP TABLE person;
  
  CREATE TABLE person
  (
    id integer NOT NULL,
    name character varying(30),
    CONSTRAINT person_pk PRIMARY KEY (id)
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE person
    OWNER TO postgres;
  
    -- Table: car
  
  -- DROP TABLE car;
  
  CREATE TABLE car
  (
    id integer NOT NULL,
    type character varying(30),
    personid integer,
    CONSTRAINT car_pk PRIMARY KEY (id)
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE car
    OWNER TO postgres;
  
    -- Table: wheel
  
  -- DROP TABLE wheel;
  
  CREATE TABLE wheel
  (
    id integer NOT NULL,
    whichone character varying(30),
    serialnumber integer,
    carid integer,
    CONSTRAINT "Wheel_PK" PRIMARY KEY (id)
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE wheel
    OWNER TO postgres;
  </code></pre>
  
  <p>And some data:</p>
  
  <pre><code>INSERT INTO person(id, name)
  VALUES (1, 'Johny'),
         (2, 'Freddy');
  
  INSERT INTO car(id, type, personid)
  VALUES (1, 'Toyota', 1),
         (2, 'Fiat', 1),    
         (3, 'Opel', 2);     
  
  INSERT INTO wheel(id, whichone, serialnumber, carid)
  VALUES (1, 'front', '11', 1),
         (2, 'back', '12', 1),
         (3, 'front', '21', 2),
         (4, 'back', '22', 2),
         (5, 'front', '3', 3);
  </code></pre>
  
  <p>As a result I would like to have one JSON object which would contain list of person, each person will have list of cars and each car list of wheels. </p>
  
  <p>I tried something like that but it isnt something that I want:</p>
  
  <pre><code>select json_build_object(
      'Persons', json_build_object(
      'person_name', person.name,
      'cars', json_build_object(
          'carid', car.id,    
          'type', car.type,
          'comment', 'nice car', -- this is constant
          'wheels', json_build_object(
              'which', wheel.whichone,
              'serial number', wheel.serialnumber
          )
  
      ))
  )
  
  from
  person 
  left join car on car.personid = person.id
  left join wheel on wheel.carid = car.id
  </code></pre>
  
  <p>I suppose that I'm missing some group by and json_agg but I'm not sure how to do this.</p>
  
  <p>I would like to have as a result something like this:</p>
  
  <pre><code>{ "persons": [   
      {
        "person_name": "Johny",
        "cars": [
            {
              "carid": 1,
              "type": "Toyota",
              "comment": "nice car",
              "wheels": [{
                "which": "Front",
                "serial number": 11
              },
              {
                "which": "Back",
                "serial number": 12
              }]
            },
            {
              "carid": 2,
              "type": "Fiat",
              "comment": "nice car",
              "wheels": [{
                "which": "Front",
                "serial number": 21
              },{
                "which": "Back",
                "serial number": 22
              }]
            }
          ]
      },
      {
        "person_name": "Freddy",
        "cars": [
            {
              "carid": 3,
              "type": "Opel",
              "comment": "nice car",
              "wheels": [{
                "which": "Front",
                "serial number": 33
              }]
            }]
      }]
  }
  </code></pre>
  
  <p><a href="http://www.jsoneditoronline.org/?id=7792a0a2bf11be724c29bb86c4b14577" rel="noreferrer">http://www.jsoneditoronline.org/?id=7792a0a2bf11be724c29bb86c4b14577</a></p>