encode_submission/wold-quarter-report-detail.sql

   1 \pset format unaligned
   2 \pset fieldsep '\t'
   3 with
   4   experiment as (
   5   select uri as Experiment,
   6          payload->>'accession' as Experiment_Accession,
   7          payload->>'description' as Experiment_Description,
   8          payload->>'status' as Experiment_Status,
   9          payload->>'date_released' as Experiment_Released,
  10          payload->>'assay_title' as Experiment_Type,
  11          jsonb_array_elements_text(payload->'replicates') as Replicate
  12   from item
  13   where object_type = 'Experiment' and
  14         payload->'lab' @> '"/labs/barbara-wold/"'::jsonb
  15   --      payload->'lab' @> '"/labs/ali-mortazavi/"'::jsonb
  16   ),
  17   replicate as (
  18     select uri as Replicate,
  19            payload->>'library' as Library
  20     from item
  21     where object_type = 'Replicate'
  22   ),
  23   library as (
  24     select uri as Library,
  25            payload->>'accession' as Library_Accession,
  26            payload->>'date_created' as Library_Created,
  27            payload->>'biosample' as Biosample,
  28            substring(jsonb_array_elements_text(payload->'aliases') from 'barbara-wold:([0-9]+)')
  29                as Jumpgate_Library_ID
  30     from item
  31     where object_type = 'Library'
  32   ),
  33   biosample as (
  34      select uri as Biosample,
  35             payload->>'donor' as Donor
  36         from item
  37         where object_type = 'Biosample'
  38   ),
  39   donor as (
  40      select uri as Donor,
  41             payload->>'organism' as Organism
  42       from item
  43   )
  44 select distinct Experiment_Accession,
  45                 Experiment_Type,
  46                 Experiment_Description,
  47                 Experiment_Status,
  48                 Experiment_Released,
  49 --                experiment.Replicate,
  50                 Library_Accession,
  51                 library.Biosample,
  52                 donor.Organism,
  53                 to_char(library.Library_Created::date, 'YYYY-MM-DD') as Library_Created,
  54                 Jumpgate_Library_ID
  55 --                samples_library.library_name
  56 from experiment
  57      LEFT JOIN replicate ON experiment.Replicate = replicate.Replicate
  58      LEFT JOIN library on replicate.Library = library.Library
  59 --     LEFT JOIN samples_library on Jumpgate_Library_ID = samples_library.id
  60      LEFT JOIN biosample on library.Biosample = biosample.Biosample
  61      LEFT JOIN donor on biosample.Donor = donor.Donor
  62 -- where
  63 --      Experiment_Status = 'started'
  64 order by experiment_released, experiment_status, experiment_accession
  65 -- limit 10
  66 ;