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
13 where object_type = 'Experiment' and
14 payload->'lab' @> '"/labs/michael-snyder/"'::jsonb
17 select uri as Replicate,
18 payload->>'library' as Library,
19 payload->>'antibody' as Antibody
21 where object_type = 'Replicate'
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
30 where object_type = 'AntibodyLot'
32 antibody_characterization as (
33 select uri as AntibodyCharacterization,
34 payload->>'status' as Status,
35 payload->>'characterization_method' as Characterization_Method
37 where object_type = 'AntibodyCharacterization'
40 select uri as Library,
41 payload->>'accession' as Library_Accession,
42 payload->>'date_created' as Library_Created,
43 payload->>'biosample' as Biosample
45 where object_type = 'Library'
48 select uri as Biosample,
49 payload->>'summary' as Summary,
50 payload->>'donor' as Donor
52 where object_type = 'Biosample'
56 payload->>'title' as title,
57 payload->>'status' as status,
58 jsonb_array_elements_text(payload->'investigated_as') as investigated_as
60 where object_type = 'Target'
64 payload->>'organism' as Organism
70 Experiment_Description,
77 antibody_characterization.Status as Characterization_Status,
78 antibody_characterization.characterization_method as Characterization_Method,
80 target.title as Target_Title,
81 target.status as Target_Status,
83 biosample.summary as Biosample_Description
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
92 -- Experiment_Status = 'started'
93 order by experiment_accession, AntibodyLot