encode_submission/snyder-chip-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/michael-snyder/"'::jsonb
  15   ),
  16   replicate as (
  17     select uri as Replicate,
  18            payload->>'library' as Library,
  19            payload->>'antibody' as Antibody
  20     from item
  21     where object_type = 'Replicate'
  22   ),
  23   antibody_lot as (
  24     select uri as AntibodyLot,
  25            payload->>'antigen_description' as Antigen_Description,
  26            payload->>'clonality' as clonality,
  27            jsonb_array_elements_text(payload->'targets') as Target,
  28            jsonb_array_elements_text(payload->'characterizations') as Characterization
  29     from item
  30     where object_type = 'AntibodyLot'
  31   ),
  32   antibody_characterization as (
  33     select uri as AntibodyCharacterization,
  34            payload->>'status' as Status,
  35            payload->>'characterization_method' as Characterization_Method
  36     from item
  37     where object_type = 'AntibodyCharacterization'
  38   ),
  39   library as (
  40     select uri as Library,
  41            payload->>'accession' as Library_Accession,
  42            payload->>'date_created' as Library_Created,
  43            payload->>'biosample' as Biosample
  44     from item
  45     where object_type = 'Library'
  46   ),
  47   biosample as (
  48     select uri as Biosample,
  49            payload->>'summary' as Summary,
  50            payload->>'donor' as Donor
  51     from item
  52     where object_type = 'Biosample'
  53   ),
  54   target as (
  55     select uri as Target,
  56            payload->>'title' as title,
  57            payload->>'status' as status,
  58            jsonb_array_elements_text(payload->'investigated_as') as investigated_as
  59     from item
  60     where object_type = 'Target'
  61   ),
  62   donor as (
  63      select uri as Donor,
  64             payload->>'organism' as Organism
  65       from item
  66   )
  67 select distinct
  68                 Experiment_Accession,
  69                 Experiment_Type,
  70                 Experiment_Description,
  71                 Experiment_Status,
  72                 Experiment_Released,
  73                 Library_Accession,
  74                 AntibodyLot,
  75                 Antigen_Description,
  76                 Clonality,
  77                 antibody_characterization.Status as Characterization_Status,
  78                 antibody_characterization.characterization_method as Characterization_Method,
  79                 target.Target,
  80                 target.title as Target_Title,
  81                 target.status as Target_Status,
  82                 biosample.Biosample,
  83                 biosample.summary as Biosample_Description
  84 from experiment
  85      JOIN replicate ON experiment.Replicate = replicate.Replicate
  86      JOIN antibody_lot on replicate.Antibody = antibody_lot.AntibodyLot
  87      JOIN target ON antibody_lot.Target = target.Target
  88      JOIN library on replicate.Library = library.Library
  89      JOIN biosample on library.Biosample = biosample.Biosample
  90      JOIN antibody_characterization on antibody_lot.Characterization = antibody_characterization.AntibodyCharacterization
  91 -- where
  92 --      Experiment_Status = 'started'
  93 order by experiment_accession, AntibodyLot
  94 -- limit 10
  95 ;