encode_submission/quarter-report.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 select distinct Experiment_Accession,
  34                 Experiment_Type,
  35                 Experiment_Description,
  36                 Experiment_Status,
  37                 Experiment_Released,
  38 --                experiment.Replicate,
  39                 Library_Accession,
  40                 to_char(library.Library_Created::date, 'YYYY-MM-DD') as Library_Created,
  41                 Library_Alias
  42 --                library.Biosample,
  43 from experiment
  44      LEFT JOIN replicate ON experiment.Replicate = replicate.Replicate
  45      LEFT JOIN library on replicate.Library = library.Library
  46 -- where
  47 --      Experiment_Status = 'started'
  48 order by experiment_released, experiment_status, experiment_accession
  49 -- limit 10
  50 ;