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/barbara-wold/"'::jsonb
15 -- payload->'lab' @> '"/labs/ali-mortazavi/"'::jsonb
18 select uri as Replicate,
19 payload->>'library' as Library
21 where object_type = 'Replicate'
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
31 where object_type = 'Library'
34 select uri as Biosample,
35 payload->>'donor' as Donor
37 where object_type = 'Biosample'
41 payload->>'organism' as Organism
44 select distinct Experiment_Accession,
46 Experiment_Description,
49 -- experiment.Replicate,
53 to_char(library.Library_Created::date, 'YYYY-MM-DD') as Library_Created,
55 -- samples_library.library_name
57 LEFT JOIN replicate ON experiment.Replicate = replicate.Replicate
58 LEFT JOIN library on replicate.Library = library.Library
59 -- LEFT JOIN samples_library on Jumpgate_Library_ID = samples_library.id
60 LEFT JOIN biosample on library.Biosample = biosample.Biosample
61 LEFT JOIN donor on biosample.Donor = donor.Donor
63 -- Experiment_Status = 'started'
64 order by experiment_released, experiment_status, experiment_accession