-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathndbdata.sql
More file actions
65 lines (63 loc) · 2.95 KB
/
ndbdata.sql
File metadata and controls
65 lines (63 loc) · 2.95 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CREATE OR REPLACE FUNCTION doi.ndbdata(dsid integer)
RETURNS TABLE(datasetid integer, data jsonb)
LANGUAGE sql
AS $function$
WITH dssamples AS (
SELECT
ds.datasetid,
jsonb_strip_nulls(jsonb_build_object('sampleid', dsd.sampleid,
'depth', anu.depth,
'datum', jsonb_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('value', dt.value,
'variablename', tx.taxonname,
'element', ve.variableelement,
'elementtype', vt.elementtype,
'symmetry', vs.symmetry,
'context', vc.variablecontext,
'units', vru.variableunits))),
'sampleanalyst', json_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('contactid', cnt.contactid,
'contactname', cnt.contactname,
'familyname', cnt.familyname,
'firstname', cnt.givennames,
'initials', cnt.leadinginitials))),
'ages', jsonb_agg(
DISTINCT jsonb_strip_nulls(jsonb_build_object('chronologyid', ch.chronologyid,
'chronologyname', ch.chronologyname,
'agetype', cht.agetype,
'age', sma.age,
'ageyounger', sma.ageyounger,
'ageolder', sma.ageolder))))) AS sampledata
FROM
ndb.datasets AS ds
LEFT OUTER JOIN ndb.dsdatasample AS dsd ON dsd.datasetid = ds.datasetid
LEFT OUTER JOIN ndb.data AS dt ON dt.dataid = dsd.dataid
LEFT OUTER JOIN ndb.variables as var ON var.variableid = dsd.variableid
LEFT OUTER JOIN ndb.taxa AS tx ON tx.taxonid = var.taxonid
LEFT OUTER JOIN ndb.variableunits AS vru ON vru.variableunitsid = var.variableunitsid
LEFT OUTER JOIN ndb.samples AS smp ON smp.sampleid = dsd.sampleid
LEFT OUTER JOIN ndb.sampleanalysts AS san ON san.sampleid = smp.sampleid
LEFT JOIN ndb.contacts AS cnt ON cnt.contactid = san.contactid
LEFT OUTER JOIN ndb.analysisunits AS anu ON anu.analysisunitid = smp.analysisunitid
LEFT JOIN ndb.variableelements AS ve ON ve.variableelementid = var.variableelementid
LEFT OUTER JOIN ndb.elementsymmetries AS vs ON vs.symmetryid = ve.symmetryid
LEFT OUTER JOIN ndb.elementtypes AS vt ON vt.elementtypeid = ve.elementtypeid
LEFT JOIN ndb.variablecontexts AS vc ON vc.variablecontextid = var.variablecontextid
LEFT OUTER JOIN ndb.sampleages AS sma ON sma.sampleid = smp.sampleid
LEFT OUTER JOIN ndb.chronologies AS ch ON sma.chronologyid = ch.chronologyid
LEFT OUTER JOIN ndb.agetypes AS cht ON cht.agetypeid = ch.agetypeid
WHERE
ds.datasetid = dsid
GROUP BY ds.datasetid,
dsd.sampleid,
anu.depth
)
SELECT
ds.datasetid,
jsonb_build_object('dataset', dsinfo.dataset,
'samples', json_agg(dss.sampledata)) AS data
FROM
ndb.datasets AS ds
JOIN dssamples AS dss ON ds.datasetid = dss.datasetid
JOIN (SELECT datasetid, dataset::jsonb FROM doi.datasetinfo(dsid)) AS dsinfo ON dsinfo.datasetid = ds.datasetid
WHERE ds.datasetid = dsid
GROUP BY ds.datasetid, dsinfo.dataset
$function$