CREATE OR REPLACE FUNCTION ap.sample(_datasetid integer)
 RETURNS TABLE(datasetid integer, sample jsonb)
 LANGUAGE sql
AS $function$
 SELECT
   ds.datasetid,
   jsonb_build_object('sampleid', dsd.sampleid,
         'keywords', array_agg(DISTINCT ky.keyword),
      'depth', anu.depth,
        'datum', jsonb_agg(DISTINCT 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_build_object('contactid', cnt.contactid,
                       'contactname', cnt.contactname,
                       'familyname', cnt.familyname,
                       'firstname', cnt.givennames,
                       'initials', cnt.leadinginitials)),
        'ages', jsonb_agg(
         DISTINCT 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
        LEFT OUTER JOIN ndb.samplekeywords AS smpky ON smpky.sampleid = smp.sampleid
              LEFT OUTER JOIN ndb.keywords AS ky    ON ky.keywordid = smpky.keywordid
 WHERE
   ds.datasetid = _datasetid
 GROUP BY ds.datasetid,
     dsd.sampleid,
     anu.depth,
     anu.thickness
    ORDER BY anu.depth ASC;
$function$