Make a JSON file from postgres database

Question

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.

user330315 · Accepted Answer · 2017-06-09 06:10:19Z

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;

Collectives™ on Stack Overflow

Make a JSON file from postgres database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related