Happy to close the week on that 
Calls to Odata API works great.
I have to investigate a more generic way to create views but it works "like a charm too"
For the moment, I created 3 views like it was (tables) in Aggregate.
So one core view based on an api call to Submissions :
SELECT json_array_elements(http.content::json -> 'value'::text) AS data
FROM http((
'GET',
'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions/',
ARRAY[http_header('Authorization','Basic bWF0aGlldS[...]OOGphYi9BQzIw')],
NULL,
NULL
)::http_request)
A second one for the first repeat of the form (emplacements) calling this URL
https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/**Submissions.emplacement/?$wkt=true**
And third one for the second repeat "observation" (nested into emplacement) calling this URL :
https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/**Submissions.emplacement.observation**
Here is the whole SQL (with fake connection parameters) code using json capabilities.
I think it could be more generic, I will try to work on that, but even if it took 2 hours, it will save a lot of time as soon as data will arrive in Central's database.
CREATE OR REPLACE VIEW public.sicen2020_core
AS
WITH submissions AS (
SELECT json_array_elements(http.content::json -> 'value'::text) AS data
FROM http(ROW('GET'::text::http_method, 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions'::character varying, ARRAY[http_header('Authorization'::character varying, 'Basic bASDlldfdsfdf5d6f5ds65f6dsAD5f6ds5fds5f44dsdYi9P7zIw'::character varying)], NULL::character varying, NULL::character varying)::http_request) http(status, content_type, headers, content)
)
SELECT submissions.data ->> '__id'::text AS id,
submissions.data ->> '__Submissions-id'::text AS "__Submissions-id",
CASE
WHEN (submissions.data #> '{phonenumber}'::text[]) IS NOT NULL THEN submissions.data -> 'phonenumber'::text
ELSE NULL::json
END AS phonenumber,
CASE
WHEN (submissions.data #> '{custom_setting}'::text[]) IS NOT NULL THEN submissions.data -> 'custom_setting'::text
ELSE NULL::json
END AS custom_setting,
CASE
WHEN (submissions.data #> '{utilisateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateur'::text
ELSE NULL::json
END AS utilisateur,
CASE
WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'submissionDate'::text
ELSE NULL::text
END AS "submissionDate",
CASE
WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'submitterId'::text
ELSE NULL::text
END AS "submitterId",
CASE
WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'submitterName'::text
ELSE NULL::text
END AS "submitterName",
CASE
WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'attachmentsPresent'::text
ELSE NULL::text
END AS "attachmentsPresent",
CASE
WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'attachmentsExpected'::text
ELSE NULL::text
END AS "attachmentsExpected",
CASE
WHEN (submissions.data #> '{__system}'::text[]) IS NOT NULL THEN (submissions.data -> '__system'::text) ->> 'status'::text
ELSE NULL::text
END AS status,
CASE
WHEN (submissions.data #> '{utilisateurastuce}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurastuce'::text
ELSE NULL::json
END AS utilisateurastuce,
CASE
WHEN (submissions.data #> '{utilisateuremail_utilisateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateuremail_utilisateur'::text
ELSE NULL::json
END AS utilisateuremail_utilisateur,
CASE
WHEN (submissions.data #> '{utilisateurusername}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurusername'::text
ELSE NULL::json
END AS utilisateurusername,
CASE
WHEN (submissions.data #> '{utilisateurnom_observateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurnom_observateur'::text
ELSE NULL::json
END AS utilisateurnom_observateur,
CASE
WHEN (submissions.data #> '{utilisateurmail_observateur}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurmail_observateur'::text
ELSE NULL::json
END AS utilisateurmail_observateur,
CASE
WHEN (submissions.data #> '{utilisateurnow}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurnow'::text
ELSE NULL::json
END AS utilisateurnow,
CASE
WHEN (submissions.data #> '{utilisateurdate_time}'::text[]) IS NOT NULL THEN submissions.data -> 'utilisateurdate_time'::text
ELSE NULL::json
END AS utilisateurdate_time,
CASE
WHEN (submissions.data #> '{protocole_etude}'::text[]) IS NOT NULL THEN submissions.data -> 'protocole_etude'::text
ELSE NULL::json
END AS protocole_etude,
CASE
WHEN (submissions.data #> '{protocole_etudeid_etude}'::text[]) IS NOT NULL THEN submissions.data -> 'protocole_etudeid_etude'::text
ELSE NULL::json
END AS protocole_etudeid_etude,
CASE
WHEN (submissions.data #> '{protocole_etudeid_protocole}'::text[]) IS NOT NULL THEN submissions.data -> 'protocole_etudeid_protocole'::text
ELSE NULL::json
END AS protocole_etudeid_protocole
FROM submissions;
CREATE OR REPLACE VIEW public.localisations
AS
WITH emplacements AS (
SELECT json_array_elements(http.content::json -> 'value'::text) AS data
FROM http(ROW('GET'::text::http_method, 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions.emplacement/?$wkt=true'::character varying, ARRAY[http_header('Authorization'::character varying, 'Basic bASDlldfdsfdf5d6f5ds65f6dsAD5f6ds5fds5f44dsdYi9P7zIw'::character varying)], NULL::character varying, NULL::character varying)::http_request) http(status, content_type, headers, content)
)
SELECT emplacements.data ->> '__id'::text AS id,
emplacements.data ->> '__Submissions-id'::text AS "__Submissions-id",
CASE
WHEN (emplacements.data #> '{heure}'::text[]) IS NOT NULL THEN emplacements.data -> 'heure'::text
ELSE NULL::json
END AS heure,
CASE
WHEN (emplacements.data #> '{localisation,methode_geo}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'methode_geo'::text
ELSE NULL::text
END AS methode_geo,
CASE
WHEN (emplacements.data #> '{localisation,longitude}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'longitude'::text
ELSE NULL::text
END AS longitude,
CASE
WHEN (emplacements.data #> '{localisation,latitude}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'latitude'::text
ELSE NULL::text
END AS latitude,
CASE
WHEN (emplacements.data #> '{localisation,point}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'point'::text
ELSE NULL::text
END AS point,
CASE
WHEN (emplacements.data #> '{localisation,trace}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'trace'::text
ELSE NULL::text
END AS trace,
CASE
WHEN (emplacements.data #> '{localisation,lenght_trace}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'lenght_trace'::text
ELSE NULL::text
END AS lenght_trace,
CASE
WHEN (emplacements.data #> '{localisation,rounded_lenght_trace}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'rounded_lenght_trace'::text
ELSE NULL::text
END AS rounded_lenght_trace,
CASE
WHEN (emplacements.data #> '{localisation,shape}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'shape'::text
ELSE NULL::text
END AS shape,
CASE
WHEN (emplacements.data #> '{localisation,shape_area}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'shape_area'::text
ELSE NULL::text
END AS shape_area,
CASE
WHEN (emplacements.data #> '{localisation,rounded_shape_area}'::text[]) IS NOT NULL THEN (emplacements.data -> 'localisation'::text) ->> 'rounded_shape_area'::text
ELSE NULL::text
END AS rounded_shape_area,
CASE
WHEN (emplacements.data #> '{observation}'::text[]) IS NOT NULL THEN emplacements.data -> 'observation'::text
ELSE NULL::json
END AS observation
FROM emplacements;
CREATE OR REPLACE VIEW public.observations
AS
WITH obs AS (
SELECT json_array_elements(http.content::json -> 'value'::text) AS data
FROM http(ROW('GET'::text::http_method, 'https://central.myserver.fr/v1/projects/1/forms/Sicen2020.svc/Submissions.emplacement.observation'::character varying, ARRAY[http_header('Authorization'::character varying, 'Basic bASDlldfdsfdf5d6f5ds65f6dsAD5f6ds5fds5f44dsdYi9P7zIw'::character varying)], NULL::character varying, NULL::character varying)::http_request) http(status, content_type, headers, content)
)
SELECT obs.data ->> '__id'::text AS id,
obs.data ->> '__Submissions-emplacement-id'::text AS "__Submissions-emplacement-id",
CASE
WHEN (obs.data #> '{heure}'::text[]) IS NOT NULL THEN obs.data -> 'heure'::text
ELSE NULL::json
END AS "heu""re",
CASE
WHEN (obs.data #> '{type_obs,useregne}'::text[]) IS NOT NULL THEN (obs.data -> 'type_obs'::text) ->> 'useregne'::text
ELSE NULL::text
END AS useregne,
CASE
WHEN (obs.data #> '{type_obs,calcul_regne}'::text[]) IS NOT NULL THEN (obs.data -> 'type_obs'::text) ->> 'calcul_regne'::text
ELSE NULL::text
END AS calcul_regne,
CASE
WHEN (obs.data #> '{type_obs,type_observation}'::text[]) IS NOT NULL THEN (obs.data -> 'type_obs'::text) ->> 'type_observation'::text
ELSE NULL::text
END AS type_observation,
CASE
WHEN (obs.data #> '{obs,pression}'::text[]) IS NOT NULL THEN (obs.data -> 'obs'::text) ->> 'pression'::text
ELSE NULL::text
END AS pression,
CASE
WHEN (obs.data #> '{obs,pression,nom_pression}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'nom_pression'::text
ELSE NULL::text
END AS nom_pression,
CASE
WHEN (obs.data #> '{obs,pression,intensite}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'intensite'::text
ELSE NULL::text
END AS intensite,
CASE
WHEN (obs.data #> '{obs,pression,atteinte}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'atteinte'::text
ELSE NULL::text
END AS atteinte,
CASE
WHEN (obs.data #> '{obs,pression,menace}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'pression'::text) ->> 'menace'::text
ELSE NULL::text
END AS menace,
CASE
WHEN (obs.data #> '{obs,observation_generale}'::text[]) IS NOT NULL THEN (obs.data -> 'obs'::text) -> 'observation_generale'::text
ELSE NULL::json
END AS observation_generale,
CASE
WHEN (obs.data #> '{obs,observation_generale,nom_observation_generale}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'observation_generale'::text) ->> 'nom_observation_generale'::text
ELSE NULL::text
END AS nom_observation_generale,
CASE
WHEN (obs.data #> '{obs,habitat,searchtext_habitat}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'habitat'::text) ->> 'searchtext_habitat'::text
ELSE NULL::text
END AS searchtext_habitat,
CASE
WHEN (obs.data #> '{obs,habitat,search_nom_habitat}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'habitat'::text) ->> 'search_nom_habitat'::text
ELSE NULL::text
END AS search_nom_habitat,
CASE
WHEN (obs.data #> '{obs,animalia,recherche_espece_animale}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'animalia'::text) ->> 'recherche_espece_animale'::text
ELSE NULL::text
END AS recherche_espece_animale,
CASE
WHEN (obs.data #> '{obs,animalia,lb_nom_animalia}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'animalia'::text) ->> 'lb_nom_animalia'::text
ELSE NULL::text
END AS lb_nom_animalia,
CASE
WHEN (obs.data #> '{obs,animalia,cd_nom_animalia}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'animalia'::text) ->> 'cd_nom_animalia'::text
ELSE NULL::text
END AS cd_nom_animalia,
CASE
WHEN (obs.data #> '{obs,plantae,recherche_espece_plante}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'plantae'::text) ->> 'recherche_espece_plante'::text
ELSE NULL::text
END AS recherche_espece_plante,
CASE
WHEN (obs.data #> '{obs,plantae,lb_nom_plantae}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'plantae'::text) ->> 'lb_nom_plantae'::text
ELSE NULL::text
END AS lb_nom_plantae,
CASE
WHEN (obs.data #> '{obs,plantae,cd_nom_plantae}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'plantae'::text) ->> 'cd_nom_plantae'::text
ELSE NULL::text
END AS cd_nom_plantae,
CASE
WHEN (obs.data #> '{obs,fungi,recherche_espece_champi}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'fungi'::text) ->> 'recherche_espece_champi'::text
ELSE NULL::text
END AS recherche_espece_champi,
CASE
WHEN (obs.data #> '{obs,fungi,lb_nom_fungi}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'fungi'::text) ->> 'lb_nom_fungi'::text
ELSE NULL::text
END AS lb_nom_fungi,
CASE
WHEN (obs.data #> '{obs,fungi,cd_nom_fungi}'::text[]) IS NOT NULL THEN ((obs.data -> 'obs'::text) -> 'fungi'::text) ->> 'cd_nom_fungi'::text
ELSE NULL::text
END AS cd_nom_fungi,
CASE
WHEN (obs.data #> '{obs,groupe}'::text[]) IS NOT NULL THEN (obs.data -> 'obs'::text) ->> 'groupe'::text
ELSE NULL::text
END AS groupe,
CASE
WHEN (obs.data #> '{detail,adulte_sexe}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_sexe'::text
ELSE NULL::text
END AS adulte_sexe,
CASE
WHEN (obs.data #> '{detail,adulte_male}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_male'::text
ELSE NULL::text
END AS adulte_male,
CASE
WHEN (obs.data #> '{detail,adulte_femelle}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_femelle'::text
ELSE NULL::text
END AS adulte_femelle,
CASE
WHEN (obs.data #> '{detail,adulte_sexe_indet}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte_sexe_indet'::text
ELSE NULL::text
END AS adulte_sexe_indet,
CASE
WHEN (obs.data #> '{detail,juvenile_sexe}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_sexe'::text
ELSE NULL::text
END AS juvenile_sexe,
CASE
WHEN (obs.data #> '{detail,juvenile_male}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_male'::text
ELSE NULL::text
END AS juvenile_male,
CASE
WHEN (obs.data #> '{detail,juvenile_femelle}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_femelle'::text
ELSE NULL::text
END AS juvenile_femelle,
CASE
WHEN (obs.data #> '{detail,juvenile_sexe_indet}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile_sexe_indet'::text
ELSE NULL::text
END AS juvenile_sexe_indet,
CASE
WHEN (obs.data #> '{detail,effectif_textuel}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'effectif_textuel'::text
ELSE NULL::text
END AS effectif_textuel,
CASE
WHEN (obs.data #> '{detail,adulte}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'adulte'::text
ELSE NULL::text
END AS adulte,
CASE
WHEN (obs.data #> '{detail,juvenile}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'juvenile'::text
ELSE NULL::text
END AS juvenile,
CASE
WHEN (obs.data #> '{detail,plantule}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'plantule'::text
ELSE NULL::text
END AS plantule,
CASE
WHEN (obs.data #> '{detail,graine}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'graine'::text
ELSE NULL::text
END AS graine,
CASE
WHEN (obs.data #> '{detail,larve}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'larve'::text
ELSE NULL::text
END AS larve,
CASE
WHEN (obs.data #> '{detail,Oeuf}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'Oeuf'::text
ELSE NULL::text
END AS "Oeuf",
CASE
WHEN (obs.data #> '{detail,age_indet_sexe}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_sexe'::text
ELSE NULL::text
END AS age_indet_sexe,
CASE
WHEN (obs.data #> '{detail,age_indet_male}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_male'::text
ELSE NULL::text
END AS age_indet_male,
CASE
WHEN (obs.data #> '{detail,age_indet_femelle}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_femelle'::text
ELSE NULL::text
END AS age_indet_femelle,
CASE
WHEN (obs.data #> '{detail,age_indet_sexe_indet}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'age_indet_sexe_indet'::text
ELSE NULL::text
END AS age_indet_sexe_indet,
CASE
WHEN (obs.data #> '{detail,no_count}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'no_count'::text
ELSE NULL::text
END AS no_count,
CASE
WHEN (obs.data #> '{detail,total_individu}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'total_individu'::text
ELSE NULL::text
END AS total_individu,
CASE
WHEN (obs.data #> '{detail,note_indiv}'::text[]) IS NOT NULL THEN (obs.data -> 'detail'::text) ->> 'note_indiv'::text
ELSE NULL::text
END AS note_indiv,
CASE
WHEN (obs.data #> '{detail_optionnel,determination}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'determination'::text
ELSE NULL::text
END AS determination,
CASE
WHEN (obs.data #> '{detail_optionnel,comportement}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'comportement'::text
ELSE NULL::text
END AS comportement,
CASE
WHEN (obs.data #> '{detail_optionnel,eff_habitat}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'eff_habitat'::text
ELSE NULL::text
END AS eff_habitat,
CASE
WHEN (obs.data #> '{detail_optionnel,etat_conservation}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'etat_conservation'::text
ELSE NULL::text
END AS etat_conservation,
CASE
WHEN (obs.data #> '{detail_optionnel,gestion}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'gestion'::text
ELSE NULL::text
END AS gestion,
CASE
WHEN (obs.data #> '{detail_optionnel,code_phyto}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'code_phyto'::text
ELSE NULL::text
END AS code_phyto,
CASE
WHEN (obs.data #> '{detail_optionnel,remarque}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'remarque'::text
ELSE NULL::text
END AS remarque,
CASE
WHEN (obs.data #> '{detail_optionnel,surface_estimee}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'surface_estimee'::text
ELSE NULL::text
END AS surface_estimee,
CASE
WHEN (obs.data #> '{detail_optionnel,lineaire_estime}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'lineaire_estime'::text
ELSE NULL::text
END AS lineaire_estime,
CASE
WHEN (obs.data #> '{detail_optionnel,prendre_image}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'prendre_image'::text
ELSE NULL::text
END AS prendre_image,
CASE
WHEN (obs.data #> '{detail_optionnel,prise_image}'::text[]) IS NOT NULL THEN (obs.data -> 'detail_optionnel'::text) ->> 'prise_image'::text
ELSE NULL::text
END AS prise_image
FROM obs;
I can now JOIN those 3 tables to create my complete data (it is an exemple, really not optimized) :
SELECT *
FROM sicen2020_core AS core
JOIN localisations AS loc ON loc."__Submissions-id" = core.id
JOIN observations AS obs ON obs."__Submissions-emplacement-id" = loc.id
Accessing the API from our PostgreSQL database is a great step forward.
It has some temporary limitations but real big advantages.
About the advantages :
- as it was pointed by @seadowg here, Odata API is THE interface to query Central, with the stability that could not be guarantee on the database schema.
- Geometries are exposed in WKT
or geojson
About the limitation, I understood that for the moment we can only query all the data from Central. But maybe I missed some explanations in the documentation I read too quickly. It would be great to query about submissions with, for example, an id higher than the last we get into the database.
We have now the efficiency we had with aggregate SQL direct access, through a stable public interface, and the new features from Central.