encode_submission/myers-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          payload->>'lab' as Experiment_Lab,
  12          jsonb_array_elements_text(payload->'replicates') as Replicate
  13   from item
  14   where object_type = 'Experiment' and
  15         payload->'lab' @> '"/labs/richard-myers/"'::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            jsonb_array_elements_text(payload->'aliases') as LibraryAlias
  29     from item
  30     where object_type = 'Library'
  31   )
  32 select distinct Experiment_Accession,
  33                 Experiment_Type,
  34                 Experiment_Description,
  35                 Experiment_Status,
  36                 Experiment_Released,
  37                 Experiment_Lab,
  38 --                experiment.Replicate,
  39                 experiment.Replicate,
  40                 Library_Accession,
  41                 to_char(library.Library_Created::date, 'YYYY-MM-DD') as Library_Created,
  42                 LibraryAlias
  43 --                library.Biosample,
  44 from experiment
  45      LEFT JOIN replicate ON experiment.Replicate = replicate.Replicate
  46      LEFT JOIN library on replicate.Library = library.Library
  47 -- where
  48 --      Experiment_Status = 'started'
  49 order by experiment_released, experiment_status, experiment_accession
  50 -- limit 10
  51 ;