metadata_dimensions

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

species_dimensions

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

One

View Definition

WITH base_epigenomes AS MATERIALIZED (
         SELECT base_epigenomes.species_name,
            base_epigenomes.assembly_name,
            base_epigenomes.id,
            base_epigenomes.material,
            base_epigenomes.epigenome_name,
            base_epigenomes.term,
            base_epigenomes.term_for_label,
            base_epigenomes.term_ontology,
            base_epigenomes.sex,
            base_epigenomes.sex_for_label,
            base_epigenomes.life_stage,
            base_epigenomes.life_stage_for_label,
            base_epigenomes.extended_age,
            base_epigenomes.age,
            base_epigenomes.age_for_label,
            base_epigenomes.organs,
            base_epigenomes.systems,
            base_epigenomes.tissue_group,
            base_epigenomes.biological_group,
            base_epigenomes.sample_type
           FROM public.base_epigenomes
        ), species AS (
         SELECT DISTINCT base_epigenomes.species_name
           FROM base_epigenomes
        ), assemblies AS (
         SELECT base_epigenomes.species_name,
            array_agg(DISTINCT base_epigenomes.assembly_name) AS assemblies
           FROM base_epigenomes
          GROUP BY base_epigenomes.species_name
        ), base_epigenome_terms AS (
         SELECT DISTINCT base_epigenomes.species_name,
            base_epigenomes.term,
            base_epigenomes.term_ontology
           FROM base_epigenomes
        ), base_epigeome_age AS (
         SELECT DISTINCT base_epigenomes.species_name,
            base_epigenomes.age_for_label AS age,
            base_epigenomes.extended_age
           FROM base_epigenomes
          WHERE (base_epigenomes.age IS NOT NULL)
        ), base_epigenome_organ AS (
         SELECT DISTINCT base_epigenomes.species_name,
            unnest(base_epigenomes.organs) AS organ
           FROM base_epigenomes
        ), base_epigenome_system AS (
         SELECT DISTINCT base_epigenomes.species_name,
            unnest(base_epigenomes.systems) AS system
           FROM base_epigenomes
        ), base_epigenome_tissue_group AS (
         SELECT DISTINCT base_epigenomes.species_name,
            base_epigenomes.tissue_group
           FROM base_epigenomes
        ), base_epigenome_biological_group AS (
         SELECT DISTINCT base_epigenomes.species_name,
            base_epigenomes.biological_group
           FROM base_epigenomes
          WHERE (base_epigenomes.biological_group IS NOT NULL)
        ), base_epigenome_sample_type AS (
         SELECT DISTINCT base_epigenomes.species_name,
            base_epigenomes.sample_type
           FROM base_epigenomes
          WHERE (base_epigenomes.sample_type IS NOT NULL)
        ), filter_menu AS (
         SELECT DISTINCT base_epigenomes.species_name,
                CASE
                    WHEN ((base_epigenomes.organs IS NULL) AND (base_epigenomes.systems IS NULL)) THEN (json_build_array(json_build_array('term'), json_build_array('tissue_group'), json_build_array('biological_group'), json_build_array('sample_type'), json_build_array('sex'), json_build_array('life_stage'), json_build_array('material')))::jsonb
                    ELSE (json_build_array(json_build_array('term'), json_build_array('tissue_group'), json_build_array('biological_group'), json_build_array('sample_type'), json_build_array('organs'), json_build_array('systems'), json_build_array('sex'), json_build_array('life_stage'), json_build_array('material')))::jsonb
                END AS filter_layout
           FROM base_epigenomes
        ), table_layout AS (
         SELECT DISTINCT base_epigenomes.species_name,
            (json_build_array('tissue_group', 'term', 'life_stage', 'age', 'sex', 'material'))::jsonb AS layout
           FROM base_epigenomes
        ), optional AS (
         SELECT DISTINCT base_epigenomes.species_name,
                CASE
                    WHEN ((base_epigenomes.organs IS NULL) AND (base_epigenomes.systems IS NULL)) THEN (json_build_array('biological_group', 'sample_type'))::jsonb
                    ELSE (json_build_array('organs', 'systems', 'biological_group', 'sample_type'))::jsonb
                END AS dimensions
           FROM base_epigenomes
        ), collapsible AS (
         SELECT DISTINCT base_epigenomes.species_name,
            (json_build_array('sex', 'life_stage', 'age'))::jsonb AS dimensions
           FROM base_epigenomes
        ), filterable AS (
         SELECT DISTINCT base_epigenomes.species_name,
                CASE
                    WHEN ((base_epigenomes.organs IS NULL) AND (base_epigenomes.systems IS NULL)) THEN (json_build_array('material', 'tissue_group', 'biological_group', 'sample_type', 'sex', 'term', 'life_stage'))::jsonb
                    ELSE (json_build_array('material', 'tissue_group', 'biological_group', 'sample_type', 'sex', 'term', 'life_stage', 'organs', 'systems'))::jsonb
                END AS dimensions
           FROM base_epigenomes
        ), sortable AS (
         SELECT DISTINCT base_epigenomes.species_name,
                CASE
                    WHEN ((base_epigenomes.organs IS NULL) AND (base_epigenomes.systems IS NULL)) THEN (json_build_array('term', 'tissue_group', 'biological_group', 'sample_type', 'life_stage', 'sex', 'material'))::jsonb
                    ELSE (json_build_array('tissue_group', 'biological_group', 'sample_type', 'organs', 'life_stage', 'sex', 'material', 'term', 'systems'))::jsonb
                END AS dimensions
           FROM base_epigenomes
        ), zero_counts_visible AS (
         SELECT DISTINCT base_epigenomes.species_name,
                CASE
                    WHEN ((base_epigenomes.organs IS NULL) AND (base_epigenomes.systems IS NULL)) THEN (json_build_array('material'))::jsonb
                    ELSE (json_build_array('material', 'systems'))::jsonb
                END AS dimensions
           FROM base_epigenomes
        ), species_dimensions AS (
         SELECT json_build_object('species_name', species.species_name, 'assemblies', ( SELECT assemblies.assemblies
                   FROM assemblies
                  WHERE (assemblies.species_name = species.species_name)), 'ui_spec', json_build_object('filter_layout', ( SELECT filter_menu.filter_layout
                   FROM filter_menu
                  WHERE (filter_menu.species_name = species.species_name)), 'table_layout', ( SELECT table_layout.layout
                   FROM table_layout
                  WHERE (table_layout.species_name = species.species_name)), 'optional', ( SELECT optional.dimensions
                   FROM optional
                  WHERE (optional.species_name = species.species_name)), 'collapsible', ( SELECT collapsible.dimensions
                   FROM collapsible
                  WHERE (collapsible.species_name = species.species_name)), 'default_collapsed_by', json_build_array('age', 'sex'), 'filterable', ( SELECT filterable.dimensions
                   FROM filterable
                  WHERE (filterable.species_name = species.species_name)), 'sortable', ( SELECT sortable.dimensions
                   FROM sortable
                  WHERE (sortable.species_name = species.species_name)), 'zero_counts_visible', ( SELECT zero_counts_visible.dimensions
                   FROM zero_counts_visible
                  WHERE (zero_counts_visible.species_name = species.species_name))), 'dimensions', json_build_object('material', json_build_object('name', 'Biosample material', 'type', 'categorical', 'values', ( SELECT json_agg(DISTINCT b.material) AS json_agg
                   FROM base_epigenomes b
                  WHERE (b.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values',
                CASE
                    WHEN (species.species_name = ANY (ARRAY['Homo sapiens'::text, 'Mus musculus'::text])) THEN json_build_array('cell line')
                    ELSE json_build_array()
                END)), 'sex', json_build_object('name', 'Sex', 'type', 'categorical', 'values', ( SELECT json_agg(DISTINCT b.sex) AS json_agg
                   FROM base_epigenomes b
                  WHERE (b.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'term', json_build_object('name', 'Biosample term', 'type', 'categorical_with_ontology', 'values', ( SELECT json_agg(json_build_object('value', t.term, 'ontology', json_build_object('curie', replace(t.term_ontology, '_'::text, ':'::text), 'url', concat('https://purl.obolibrary.org/obo/', t.term_ontology)))) AS json_agg
                   FROM base_epigenome_terms t
                  WHERE (t.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'life_stage', json_build_object('name', 'Life stage', 'type', 'categorical', 'values', ( SELECT json_agg(DISTINCT b.life_stage) AS json_agg
                   FROM base_epigenomes b
                  WHERE (b.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'age', json_build_object('name', 'Age', 'type', 'categorical_with_description', 'values', ( SELECT json_agg(json_build_object('value', a.age, 'description', a.extended_age)) AS json_agg
                   FROM base_epigeome_age a
                  WHERE (a.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'organs', json_build_object('name', 'Organ', 'type', 'categorical_with_ontology', 'values', ( SELECT json_agg(json_build_object('value', o.organ, 'ontology', json_build_object('curie', NULL::unknown, 'url', NULL::unknown))) AS json_agg
                   FROM base_epigenome_organ o
                  WHERE (o.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'systems', json_build_object('name', 'System', 'type', 'categorical_with_ontology', 'values', ( SELECT json_agg(json_build_object('value', s.system, 'ontology', json_build_object('curie', NULL::unknown, 'url', NULL::unknown))) AS json_agg
                   FROM base_epigenome_system s
                  WHERE (s.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'tissue_group', json_build_object('name', 'Tissue group', 'type', 'categorical_with_ontology', 'values', ( SELECT json_agg(json_build_object('value', t.tissue_group, 'ontology', json_build_object('curie', NULL::unknown, 'url', NULL::unknown))) AS json_agg
                   FROM base_epigenome_tissue_group t
                  WHERE (t.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'biological_group', json_build_object('name', 'Biological group', 'type', 'categorical_with_ontology', 'values', ( SELECT json_agg(json_build_object('value', c.biological_group, 'ontology', json_build_object('curie', NULL::text, 'url', NULL::text)) ORDER BY c.biological_group) AS json_agg
                   FROM base_epigenome_biological_group c
                  WHERE (c.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())), 'sample_type', json_build_object('name', 'Sample type', 'type', 'categorical', 'values', ( SELECT json_agg(c.sample_type ORDER BY c.sample_type) AS json_agg
                   FROM base_epigenome_sample_type c
                  WHERE (c.species_name = species.species_name)), 'default_set', json_build_object('type', 'exclude', 'values', json_build_array())))) AS payload
           FROM species
        )
 SELECT jsonb_pretty((json_agg(species_dimensions.payload))::jsonb) AS species_dimensions
   FROM species_dimensions;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views