encode_submission/target.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 Characterization,
  34            payload->>'caption' as Caption,
  35            payload->>'characterization_method' as Characterization_Method,
  36            payload->>'status' as Characterization_Status
  37     from item
  38     where object_type = 'AntibodyCharcterization'
  39   ),
  40   library as (
  41     select uri as Library,
  42            payload->>'accession' as Library_Accession,
  43            payload->>'date_created' as Library_Created,
  44            payload->>'biosample' as Biosample
  45     from item
  46     where object_type = 'Library'
  47   ),
  48   biosample as (
  49      select uri as Biosample,
  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
  68   Replicate,
  69   Library,
  70   AntibodyLot,
  71   Antigen_Description,
  72   Clonality,
  73   antibody_lot.Target,
  74   antibody_lot.Characterization,
  75   Characterization_Method,
  76   Characterization_Status,
  77   target.title,
  78   target.status,
  79   target.investigated_as
  80 --                experiment.Replicate,
  81 --                Library_Accession,
  82 --                library.Biosample,
  83 --                donor.Organism,
  84 --                to_char(library.Library_Created::date, 'YYYY-MM-DD') as Library_Created
  85 from replicate
  86      left join antibody_lot on replicate.Antibody = antibody_lot.AntibodyLot
  87      left join antibody_characterization on
  88                antibody_lot.Characterization = antibody_characterization.Characterization
  89      left join target on antibody_lot.Target = target.Target
  90 limit 10
  91 ;