Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table crowd_sourcing_questions
(
    id serial not null,
	question text,
	"answeringOptions" text[] default '{}'::text[],
	"trafficTypes" text[] default '{}'::text[],
	preference text,
	created_at timestamp not null,
	updated_at timestamp not null
)
;

INSERT INTO crowd_sourcing_questions (id, question, "answeringOptions", "trafficTypes", preference, created_at, updated_at) VALUES (1, 'Würden Sie die gefahrene Route anderen Radfahrenden weiterempfehlen?', '{Ja,Nein}', '{ownBike}', 'safety', '2018-10-05 13:11:54.115000', '2018-10-05 13:11:55.685000');
INSERT INTO crowd_sourcing_questions (id, question, "answeringOptions", "trafficTypes", preference, created_at, updated_at) VALUES (2, 'Bitte bewerten Sie die Sicherheit der Radroute!', '{Sehr sicher,Eher sicher,Neutral,Eher unsicher,Sehr unsicher}', '{ownBike}', 'safety', '2018-10-05 13:10:30.247000', '2018-10-05 13:10:33.420000');
INSERT INTO crowd_sourcing_questions (id, question, "answeringOptions", "trafficTypes", preference, created_at, updated_at) VALUES (3, 'Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?', '{Ja,Nein}', '{ownBike,bike}', 'safety', '2018-10-08 10:43:20.892000', '2018-10-08 11:07:56.864000');

create table crowd_sourcing_answers
(
	id serial not null,
	trip_log_id integer,
	crowd_sourcing_question_id integer,
	answer text,
	created_at timestamp not null,
	updated_at timestamp not null
)
;

INSERT INTO crowd_sourcing_answers (id, trip_log_id, crowd_sourcing_question_id, answer, created_at, updated_at) VALUES (1, 90, 3, '0', '2018-10-05 14:11:46.027000', '2018-10-05 14:11:46.027000');
INSERT INTO crowd_sourcing_answers (id, trip_log_id, crowd_sourcing_question_id, answer, created_at, updated_at) VALUES (8, 91, 3, '1', '2018-10-05 14:48:01.692000', '2018-10-05 14:48:01.692000');
INSERT INTO crowd_sourcing_answers (id, trip_log_id, crowd_sourcing_question_id, answer, created_at, updated_at) VALUES (12, 92, 3, '2', '2018-10-05 14:58:23.264000', '2018-10-05 14:58:23.264000');
INSERT INTO crowd_sourcing_answers (id, trip_log_id, crowd_sourcing_question_id, answer, created_at, updated_at) VALUES (15, 93, 4, '2', '2018-10-08 14:45:49.470000', '2018-10-08 14:45:49.470000');

create table data_tracks
(
	id serial not null,
	"accelerationAverage" text,
	"accelerationMaximum" text,
	"accelerationMinimum" text,
	"accelerationStandardDeviation" text,
	accuracy double precision,
	altitude double precision,
	bearing double precision,
	"cellId" text,
	confidence text,
	"detectedActivity" text,
	latitude double precision,
	longitude double precision,
	"locationAreaCode" text,
	"primaryScramblingCode" text,
	provider text,
	speed double precision,
	time bigint,
	created_at timestamp not null,
	updated_at timestamp not null,
	trip_log_id integer
)
;

INSERT INTO data_tracks (id, "accelerationAverage", "accelerationMaximum", "accelerationMinimum", "accelerationStandardDeviation", accuracy, altitude, bearing, "cellId", confidence, "detectedActivity", latitude, longitude, "locationAreaCode", "primaryScramblingCode", provider, speed, time, created_at, updated_at, trip_log_id) VALUES (6, 'n/a', 'n/a', 'n/a', 'n/a', 4, 91.16037638647896, 0, 'n/a', 'n/a', 'n/a', 52.52423708468034, 13.34435585286374, 'n/a', 'n/a', 'gps', 0, 1538489171000, '2018-10-05 13:28:24.452000', '2018-10-05 13:28:24.452000', 90);
INSERT INTO data_tracks (id, "accelerationAverage", "accelerationMaximum", "accelerationMinimum", "accelerationStandardDeviation", accuracy, altitude, bearing, "cellId", confidence, "detectedActivity", latitude, longitude, "locationAreaCode", "primaryScramblingCode", provider, speed, time, created_at, updated_at, trip_log_id) VALUES (7, 'n/a', 'n/a', 'n/a', 'n/a', 4, 91.54087523762024, 0, 'n/a', 'n/a', 'n/a', 52.52423661663932, 13.344355865682758, 'n/a', 'n/a', 'gps', 0, 1538489172000, '2018-10-05 13:28:24.489000', '2018-10-05 13:28:24.489000', 90);
INSERT INTO data_tracks (id, "accelerationAverage", "accelerationMaximum", "accelerationMinimum", "accelerationStandardDeviation", accuracy, altitude, bearing, "cellId", confidence, "detectedActivity", latitude, longitude, "locationAreaCode", "primaryScramblingCode", provider, speed, time, created_at, updated_at, trip_log_id) VALUES (8, 'n/a', 'n/a', 'n/a', 'n/a', 4, 91.16037638647896, 0, 'n/a', 'n/a', 'n/a', 52.52423708468034, 13.34435585286374, 'n/a', 'n/a', 'gps', 0, 1538489171000, '2018-10-05 14:11:44.847000', '2018-10-05 14:11:44.847000', 91);
INSERT INTO data_tracks (id, "accelerationAverage", "accelerationMaximum", "accelerationMinimum", "accelerationStandardDeviation", accuracy, altitude, bearing, "cellId", confidence, "detectedActivity", latitude, longitude, "locationAreaCode", "primaryScramblingCode", provider, speed, time, created_at, updated_at, trip_log_id) VALUES (9, 'n/a', 'n/a', 'n/a', 'n/a', 4, 91.54087523762024, 0, 'n/a', 'n/a', 'n/a', 52.52423661663932, 13.344355865682758, 'n/a', 'n/a', 'gps', 0, 1538489172000, '2018-10-05 14:11:44.911000', '2018-10-05 14:11:44.911000', 91);

SELECT * FROM data_tracks;

id	accelerationAverage	accelerationMaximum	accelerationMinimum	accelerationStandardDeviation	accuracy	altitude	cellId	confidence	detectedActivity	latitude	longitude	locationAreaCode	primaryScramblingCode	provider	time	created_at	updated_at	trip_log_id
6	n/a	n/a	n/a	n/a	4	91.160376386479	n/a	n/a	n/a	52.5242370846803	13.3443558528637	n/a	n/a	gps	1538489171000	2018-10-05 13:28:24.452	2018-10-05 13:28:24.452	90
7	n/a	n/a	n/a	n/a	4	91.5408752376202	n/a	n/a	n/a	52.5242366166393	13.3443558656828	n/a	n/a	gps	1538489172000	2018-10-05 13:28:24.489	2018-10-05 13:28:24.489	90
8	n/a	n/a	n/a	n/a	4	91.160376386479	n/a	n/a	n/a	52.5242370846803	13.3443558528637	n/a	n/a	gps	1538489171000	2018-10-05 14:11:44.847	2018-10-05 14:11:44.847	91
9	n/a	n/a	n/a	n/a	4	91.5408752376202	n/a	n/a	n/a	52.5242366166393	13.3443558656828	n/a	n/a	gps	1538489172000	2018-10-05 14:11:44.911	2018-10-05 14:11:44.911	91

SELECT 4

json_agg
[{"crowdsourcing" : [{"question" : "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?", "answeringOption" : ["Ja","Nein"], "answer" : "1"}], "trip" : [{"recorded_at" : "2018-10-05T14:11:44.847", "latitude" : 52.5242370846803, "longitude" : 13.3443558528637}, {"recorded_at" : "2018-10-05T14:11:44.911", "latitude" : 52.5242366166393, "longitude" : 13.3443558656828}]}, {"crowdsourcing" : [{"question" : "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?", "answeringOption" : ["Ja","Nein"], "answer" : "0"}], "trip" : [{"recorded_at" : "2018-10-05T13:28:24.452", "latitude" : 52.5242370846803, "longitude" : 13.3443558528637}, {"recorded_at" : "2018-10-05T13:28:24.489", "latitude" : 52.5242366166393, "longitude" : 13.3443558656828}]}]

json_agg

[{"crowdsourcing" : [{"question" : "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?", "answeringOption" : ["Ja","Nein"], "answer" : "1"}], "trip" : [{"recorded_at" : "2018-10-05T14:11:44.847", "latitude" : 52.5242370846803, "longitude" : 13.3443558528637}, {"recorded_at" : "2018-10-05T14:11:44.911", "latitude" : 52.5242366166393, "longitude" : 13.3443558656828}]}, {"crowdsourcing" : [{"question" : "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?", "answeringOption" : ["Ja","Nein"], "answer" : "0"}], "trip" : [{"recorded_at" : "2018-10-05T13:28:24.452", "latitude" : 52.5242370846803, "longitude" : 13.3443558528637}, {"recorded_at" : "2018-10-05T13:28:24.489", "latitude" : 52.5242366166393, "longitude" : 13.3443558656828}]}]

SELECT 1