

CREATE TABLE FlightCodes (
  code TEXT PRIMARY KEY,
  airlineName TEXT NOT NULL);

CREATE TABLE Flights (
  code TEXT PRIMARY KEY REFERENCES FlightCodes,
  departureAirport CHAR(3) NOT NULL REFERENCES Airports,
  destinationAirport CHAR(3) NOT NULL REFERENCES Airports); 


INSERT INTO Airports VALUES ('GOT', 'Gothenburg');
INSERT INTO Airports VALUES ('FRA', 'Frankfurt');
INSERT INTO Airports VALUES ('ORY', 'Paris');
INSERT INTO Airports VALUES ('MLA', 'Malta');
INSERT INTO Airports VALUES ('MUC', 'Munich');

INSERT INTO FlightCodes VALUES ('SK111', 'SAS');
INSERT INTO FlightCodes VALUES ('AF222', 'Air France');
INSERT INTO FlightCodes VALUES ('AB222', 'Air Berlin');
INSERT INTO FlightCodes VALUES ('KM111', 'Air Malta');

INSERT INTO Flights VALUES ('SK111', 'GOT', 'FRA');
INSERT INTO Flights VALUES ('AF222', 'ORY', 'MLA');
INSERT INTO Flights VALUES ('AB222', 'FRA', 'MUC');
INSERT INTO Flights VALUES ('KM111', 'MUC', 'MLA');


-- Extracts all the data from the tables
WITH
    Ap AS (SELECT json_object_agg(code, city) AS jsondata
           FROM Airports),
    Fc AS (SELECT json_object_agg(code, airlineName) AS jsondata
           FROM FlightCodes),
    F AS (SELECT
          json_object_agg(code,
                  jsonb_build_object('dep', departureAirport,
                                     'dest', destinationAirport)
                         ) AS jsondata FROM Flights)
   SELECT jsonb_pretty(                        -- to visualise the output better
              jsonb_build_object('Airports', (SELECT jsondata FROM Ap),
                                 'FlightCodes',(SELECT jsondata FROM Fc),
                                 'Flights',(SELECT jsondata FROM F)));


/* Schema

{"type": "object",
 "properties": {
   "Airports": {
     "type":"object",
     "additionalProperties":{"type":"string"}
   },
   "FlightCodes": {
      "type":"object",
      "additionalProperties":{"type":"string"}
   },
   "Flights": {
      "type":"object",
      "additionalProperties":{
         "type":"object",
         "properties":{
                "dep":{"type":"string"},
                "dest":{"type":"string"}},
         "required": ["dep","dest"],
         "additionalProperties": false
      }
   }},
 "required": ["Airports","FlightCodes","Flights"],
 "additionalProperties": false
}
*/


-- The same data from a JSON object from the start
WITH FlightValue AS
  (SELECT '{ "Airports": {"GOT": "Gothenburg",
                          "FRA": "Frankfurt",
                          "ORY": "Paris",
                          "MUC": "Munich",
                          "MLA": "Malta"},
             "FlightCodes": {"SK111": "SAS",
                             "AF222": "Air France",
                             "AB222": "Air Berlin",
                             "KM111": "Air Malta"} ,
             "Flights": {"SK111" : {"dep" : "GOT", "dest": "FRA"},
                         "AF222" : {"dep" : "ORY", "dest": "MLA"},
                         "AB222" : {"dep" : "FRA", "dest": "MUC"},
                         "KM111" : {"dep" : "MUC", "dest": "MLA"}}
           }' :: JSONB AS val)
SELECT jsonb_path_query (val,
                   'strict $.Flights.keyvalue()?(@.value.dest == "MLA").key')
		   -- flights to Malta
FROM FlightValue;


