D-Net

--WITH RECURSIVE fundingtree(id, parent_id, payload, depth) 

--AS (

--	SELECT  f.id, 

--		ff.funding1, 

--		'{"funding_level_0":{"class":"' || f.semanticclass || '", "id":"' || f.id || '", "description":"' || f.description || '", "name":"' || f.name || '", "parent":{}}}', 

--		1

--		FROM fundings f LEFT OUTER JOIN funding_funding ff ON f.id = ff.funding1 

--		WHERE ff.funding2 IS NULL

--	UNION 

--	SELECT  f1.id, 

--		ff.funding2,

--		'{"funding_level_'||depth||'":{"class":"' || f1.semanticclass || '", "id":"' || f1.id || '", "description":"' || f1.description || '", "name":"' || f1.name || '", "parent":'|| ft.payload ||'}}',

--		ft.depth + 1

--		FROM funding_funding ff, fundingtree ft, fundings f2, fundings f1

--		WHERE ft.id = ff.funding2 AND f2.id = ft.id AND ff.funding1 = f1.id AND depth <= 10)		

SELECT 	p.id 					                    as projectid,

		p.code										as code,

		p.websiteurl               					as websiteurl,		

		p.acronym                  					as acronym,

		p.title                    					as title,

		p.startdate									as startdate,

		p.enddate                 					as enddate,

		p.call_identifier          					as callidentifier,

		p.keywords									as keywords,		

		p.duration									as duration,

		p.ec_sc39				                    as ecsc39,

		p.oa_mandate_for_publications               as oamandatepublications,

		p.ec_article29_3                            as ecarticle29_3,

		p.dateofcollection							as dateofcollection,

		p.inferred									as inferred,

		p.deletedbyinference						as deletedbyinference,

		p.trust										as trust,

		p.inferenceprovenance						as inferenceprovenance,

		p.optional1										as optional1,

		p.optional2										as optional2,

		p.jsonextrainfo         as jsonextrainfo,

		dc.id										as collectedfromid,

		dc.officialname								as collectedfromname,

		cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name	as contracttype,

		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name	as provenanceaction,

		array_agg(DISTINCT i.pid || '###' || i.issuertypeclass)	as pid,

		array_agg(DISTINCT s.name || '###' || sc.code  || '@@@' || sc.name  || '@@@' || ss.code  || '@@@' || ss.name)   as subjects,

		array_agg(fp.path)		                as fundingtree

FROM projects p

	left outer join class cc on (cc.code = p.contracttypeclass)	

	left outer join scheme cs on (cs.code = p.contracttypescheme)

	left outer join class pac on (pac.code = p.provenanceactionclass)	

	left outer join scheme pas on (pas.code = p.provenanceactionscheme)		

	left outer join projectpids pp on (pp.project = p.id)

	left outer join identities i on (i.pid = pp.pid)

	left outer join datasources dc on (dc.id = p.collectedfrom)

	left outer join project_fundingpath pf on (pf.project = p.id)

	left outer join fundingpaths fp on (fp.id = pf.funding)

	left outer join project_subject ps on (ps.project = p.id)

	left outer join subjects s on (s.id = ps.subject)

	left outer join class sc on (sc.code = s.semanticclass)

	left outer join scheme ss on (ss.code = s.semanticscheme)

GROUP BY 	

		p.id,

		p.code,

		p.websiteurl,		

		p.acronym,

		p.title,

		p.startdate,

		p.enddate,

		p.call_identifier,

		p.keywords,		

		p.duration,

		p.ec_sc39,

		p.oa_mandate_for_publications,

		p.ec_article29_3,

		p.dateofcollection,

		p.inferred,

		p.deletedbyinference,

		p.trust,

		p.inferenceprovenance,

		dc.id,

		dc.officialname,		

		cc.code, cc.name, cs.code, cs.name,

		pac.code, pac.name, pas.code, pas.name