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.dateofcollection							as dateofcollection,
		p.inferred									as inferred,
		p.deletedbyinference						as deletedbyinference,
		p.trust										as trust,
		p.inferenceprovenance						as inferenceprovenance,
		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 || '###' || idc.code || '@@@' || idc.name || '@@@' || ids.code || '@@@' || ids.name)	as pid,
		
		array_agg(ft.payload)		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 class idc on (idc.code = i.issuertypeclass)
	left outer join scheme ids on (ids.code = i.issuertypescheme)
	
	left outer join datasources dc on (dc.id = p.collectedfrom)
	
	left outer join project_funding pf on (pf.project = p.id)
	left outer join fundingtree ft on (ft.id = pf.funding)
	
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.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