encode_submission/entex.sql

   1 \pset format unaligned
   2 \pset fieldsep '\t'
   3 with
   4   gtex as (
   5   select distinct uri as Donor,
   6          substring(jsonb_array_elements_text(payload->'aliases'), '([a-zA-Z0-9]+):') as AliasPrefix
   7   from item
   8   where object_type = 'HumanDonor'
   9   ),
  10   biosample as (
  11      select uri as Biosample,
  12             payload->>'accession' as Biosample_Accession,
  13             payload->>'date_created' as Biosample_Created,
  14             payload->>'lab' as Biosample_Lab,
  15             payload->>'biosample_term_name' as Biosample_Term,
  16             payload->>'summary' as Description,
  17             jsonb_array_elements_text(payload->'parent_of') as child_biosample,
  18             payload->>'donor' as Donor
  19         from item
  20         where object_type = 'Biosample'
  21   ),
  22   experiment as (
  23   select uri as Experiment,
  24          payload->>'accession' as Experiment_Accession,
  25          payload->>'description' as Experiment_Description,
  26          payload->>'status' as Experiment_Status,
  27          payload->>'date_released' as Experiment_Released,
  28          payload->>'lab' as Experiment_Lab,
  29          jsonb_array_elements_text(payload->'replicates') as Replicate
  30   from item
  31   where object_type = 'Experiment'
  32   )
  33 select gtex.Donor, Biosample, Biosample_Lab, Biosample_Term, Description, child_biosample
  34 from gtex
  35      LEFT JOIN biosample ON gtex.Donor = biosample.Donor
  36 where gtex.AliasPrefix = 'gtex'
  37 ;