-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathndbdata.sql
More file actions
143 lines (140 loc) · 6.51 KB
/
ndbdata.sql
File metadata and controls
143 lines (140 loc) · 6.51 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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
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,
'taxonid', tx.taxonid,
'taxongroup', txg.taxagroup,
'ecologicalgroup', ecg.ecolgroupid,
'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.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.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$;
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,
'taxonid', tx.taxonid,
'taxongroup', txg.taxagroup,
'ecologicalgroup', ecg.ecolgroupid,
'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.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.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 = ANY(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 = ANY(dsid)
GROUP BY ds.datasetid, dsinfo.dataset
$function$