2

I am using postgresql version 9.6. I am trying to make a JSON file from my database. I can worry about how to turn the SQL query into a file, but I am getting caught up on how to set up the query. Here are my tables and a few inserts:

create table careers 
(
  career_id integer PRIMARY KEY,
  career_name text
);

create table career_types
(
  career_type_id integer PRIMARY KEY,
  career_id integer REFERENCES careers,
  career_type_name text
);

create table career_type_properties
(
  career_type_property_id serial PRIMARY KEY,
  career_id integer REFERENCES careers,
  career_type_id integer REFERENCES career_types,
  property_name character varying(255),
  property_value character varying(255)
);

INSERT INTO careers (career_id, career_name)
VALUES
  (1, 'medical'),
  (2, 'hospitality'),
  (3, 'education');

INSERT INTO career_types(career_type_id, career_id, career_type_name)
VALUES
  (1, '1', 'surgeon'),
  (2, '1', 'nurse'),
  (3, '2', 'hotel_manager'),
  (4, '2', 'bartendar'),
  (5, '3', 'Middle_school_teacher'),
  (6, '3', 'Professor');

INSERT INTO career_type_properties(career_id, career_type_id, property_name, property_value)
VALUES
  ('1', '1', 'pay', 'salary'),
  ('1', '1', 'days_per_week', '5'),
  ('1', '1', 'on_call', 'yes'),
  ('1', '1', 'doctorate_degree', 'yes'),
  ('1', '2', 'pay', 'salary'),
  ('1', '2', 'days_per_week', '4'),
  ('1', '2', 'on_call', 'no'),
  ('1', '2', 'doctorate_degree', 'no'),
  ('2', '3', 'pay', 'salary'),
  ('2', '3', 'days_per_week', '5-6'),
  ('2', '3', 'on_call', 'no'),
  ('2', '4', 'pay', 'hourly'),
  ('2', '4', 'days_per_week', '3-6'),
  ('2', '4', 'on_call', 'no'),
  ('3', '5', 'pay', 'salary'),
  ('3', '5', 'year_round', 'yes'),
  ('3', '5', 'does_research', 'no'),
  ('3', '5', 'set_hours', 'yes'),
  ('3', '6', 'pay', 'salary'),
  ('3', '6', 'year_round', 'optional'),
  ('3', '6', 'does_research', 'yes'),
  ('3', '6', 'set_hours', 'no');

Here is an example of what JSON I am trying produce.

  {
   "medical":{
       "surgeon":{
         "pay": "salary",
         "days_per_week": "5",
         "on_call": "yes",
         "doctorate_degree": "yes"
        },
       "nurse": {
         "pay": "salary",
         "days_per_week": "4",
         "on_call": "no",
         "doctorate_degree": "no"
        }
    },
    "hospitality": {
       "hotel_manager":{
          "pay": "salary",
          "days_per_week": "5-6",
          "on_call": "no"
       },
       "bartender": {
          "pay": "hourly",
          "days_per_week": "3-6",
          "on_call": "no"
       }
    },
     "education": {
        "middle_school_teacher":{
          "pay":"salary",
          "year_round": "yes",
          "does_research": "no",
          "set_hours": "yes"
      },
        "professor":{
          "pay":"salary",
          "year_round": "optional",
          "does_research": "yes",
          "set_hours": "no"
      }
   }
 }

I have been able to get close using queries like:

    select json_object_agg(property_name, property_value) from 
    career_type_properties group by career_type_id

I am pretty new to using SQL so any help would be appreciated.

1 Answer 1

2

You need to nest the grouping for this:

select json_object_agg(career_name, c)
from (
  select c.career_name, json_object_agg(ct.career_type_name,  ctp.props) c
  from careers c 
    join career_types ct ON c.career_id = ct.career_id 
    join (
      select career_type_id, json_object_agg(property_name, property_value) as props
      from career_type_properties
      group by career_type_id
    ) ctp on ctp.career_type_id = ct.career_type_id
  group by c.career_name
) t;

Online example: http://rextester.com/LLNQ72750

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.