encode_submission/entex-recursive.sql

   1 -- \pset format unaligned
   2 -- \pset fieldsep '\t'
   3 with recursive
   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(uri, payload, parent_of, part_of, d) as (
  11        select uri, payload,
  12               jsonb_array_elements_text(payload->'parent_of') as parent_of,
  13               payload->>'part_of' as part_of,
  14               1
  15        from item
  16        where object_type = 'Biosample'
  17    union all
  18        select uri, payload,
  19               jsonb_array_elements_text(payload->'parent_of') as parent_of,
  20               payload->>'part_of' as part_of,
  21               d + 1
  22        from biosample
  23        where parent_of = uri
  24   ),
  25   experiment as (
  26   select uri as Experiment,
  27          payload->>'accession' as Experiment_Accession,
  28          payload->>'description' as Experiment_Description,
  29          payload->>'status' as Experiment_Status,
  30          payload->>'date_released' as Experiment_Released,
  31          payload->>'lab' as Experiment_Lab,
  32          jsonb_array_elements_text(payload->'replicates') as Replicate
  33   from item
  34   where object_type = 'Experiment'
  35   )
  36 select gtex.Donor, biosample.uri, biosample.parent_of
  37 from gtex
  38      JOIN biosample ON gtex.Donor = biosample.payload->>'donor'
  39 where gtex.AliasPrefix = 'gtex'
  40 ;