materialized_phenotype_jsonb_table

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

observationunit_stock_id

int4

√

null

stock.stock_id

Implied Constraint

observationunit_uniquename

text

2147483647

√

null

observationunit_type_name

varchar

1024

√

null

germplasm_uniquename

text

2147483647

√

null

germplasm_stock_id

int4

√

null

stock.stock_id

Implied Constraint

rep

text

2147483647

√

null

block

text

2147483647

√

null

plot_number

text

2147483647

√

null

row_number

text

2147483647

√

null

col_number

text

2147483647

√

null

plant_number

text

2147483647

√

null

is_a_control

text

2147483647

√

null

notes

text

2147483647

√

null

trial_id

int4

√

null

trial_name

varchar

255

√

null

trial_description

text

2147483647

√

null

plot_width

text

2147483647

√

null

plot_length

text

2147483647

√

null

field_size

text

2147483647

√

null

field_trial_is_planned_to_be_genotyped

text

2147483647

√

null

field_trial_is_planned_to_cross

text

2147483647

√

null

breeding_program_id

int4

√

null

breeding_program_name

varchar

255

√

null

breeding_program_description

text

2147483647

√

null

year

text

2147483647

√

null

design

text

2147483647

√

null

location_id

text

2147483647

√

null

planting_date

text

2147483647

√

null

harvest_date

text

2147483647

√

null

folder_id

int4

√

null

folder_name

varchar

255

√

null

folder_description

text

2147483647

√

null

seedlot_transaction

text

2147483647

√

null

seedlot_stock_id

int4

√

null

stock.stock_id

Implied Constraint

seedlot_uniquename

text

2147483647

√

null

seedlot_current_weight_gram

text

2147483647

√

null

seedlot_current_count

text

2147483647

√

null

seedlot_box_name

text

2147483647

√

null

treatments

jsonb

2147483647

√

null

observations

jsonb

2147483647

√

null

available_germplasm_seedlots

jsonb

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT observationunit.stock_id AS observationunit_stock_id,
    observationunit.uniquename AS observationunit_uniquename,
    observationunit_cvterm.name AS observationunit_type_name,
    germplasm.uniquename AS germplasm_uniquename,
    germplasm.stock_id AS germplasm_stock_id,
    rep.value AS rep,
    block_number.value AS block,
    plot_number.value AS plot_number,
    row_number.value AS row_number,
    col_number.value AS col_number,
    plant_number.value AS plant_number,
    is_a_control.value AS is_a_control,
    string_agg(DISTINCT notes.value, ', '::text) AS notes,
    project.project_id AS trial_id,
    project.name AS trial_name,
    project.description AS trial_description,
    plot_width.value AS plot_width,
    plot_length.value AS plot_length,
    field_size.value AS field_size,
    field_trial_is_planned_to_be_genotyped.value AS field_trial_is_planned_to_be_genotyped,
    field_trial_is_planned_to_cross.value AS field_trial_is_planned_to_cross,
    breeding_program.project_id AS breeding_program_id,
    breeding_program.name AS breeding_program_name,
    breeding_program.description AS breeding_program_description,
    year.value AS year,
    design.value AS design,
    location_id.value AS location_id,
    planting_date.value AS planting_date,
    harvest_date.value AS harvest_date,
    folder.project_id AS folder_id,
    folder.name AS folder_name,
    folder.description AS folder_description,
    seedplot_planted.value AS seedlot_transaction,
    seedlot.stock_id AS seedlot_stock_id,
    seedlot.uniquename AS seedlot_uniquename,
    seedlot_current_weight.value AS seedlot_current_weight_gram,
    seedlot_current_count.value AS seedlot_current_count,
    seedlot_seedlot_box.value AS seedlot_box_name,
    COALESCE(jsonb_object_agg(treatment.name, treatment.description) FILTER (WHERE (treatment.name IS NOT NULL)), '{"No ManagementFactor": null}'::jsonb) AS treatments,
    COALESCE(jsonb_agg(jsonb_build_object('trait_id', phenotype.cvalue_id, 'trait_name', (((((cvterm.name)::text || '|'::text) || (db.name)::text) || ':'::text) || (dbxref.accession)::text), 'value', phenotype.value, 'phenotype_id', phenotype.phenotype_id, 'outlier', outlier.value, 'create_date', phenotype.create_date, 'uniquename', phenotype.uniquename, 'phenotype_location_id', nd_geolocation.nd_geolocation_id, 'phenotype_location_name', nd_geolocation.description, 'collect_date', phenotype.collect_date, 'operator', phenotype.operator, 'associated_image_id', md_image.image_id, 'associated_image_type', project_md_image_type.name, 'associated_image_project_id', drone_image_project.project_id, 'associated_image_project_name', drone_image_project.name, 'associated_image_project_time_json', drone_image_project_time_json.value)) FILTER (WHERE (phenotype.value IS NOT NULL)), '[]'::jsonb) AS observations,
    COALESCE(jsonb_agg(jsonb_build_object('stock_id', available_seelot.stock_id, 'stock_uniquename', available_seelot.uniquename, 'current_weight_gram', current_weight.value, 'current_count', current_count.value, 'box_name', seedlot_box.value)) FILTER (WHERE (available_seelot.stock_id IS NOT NULL)), '[]'::jsonb) AS available_germplasm_seedlots
   FROM ((((((((((((((((((((((((((((((((((((((((((((((((((((((((stock observationunit
     JOIN nd_experiment_stock ON ((observationunit.stock_id = nd_experiment_stock.stock_id)))
     JOIN nd_experiment ON ((nd_experiment_stock.nd_experiment_id = nd_experiment.nd_experiment_id)))
     JOIN nd_geolocation USING (nd_geolocation_id))
     LEFT JOIN stock_relationship seedplot_planted ON (((seedplot_planted.subject_id = observationunit.stock_id) AND (seedplot_planted.type_id = 77562))))
     LEFT JOIN stock seedlot ON (((seedplot_planted.object_id = seedlot.stock_id) AND (seedlot.type_id = 77563))))
     LEFT JOIN stockprop seedlot_current_count ON (((seedlot.stock_id = seedlot_current_count.stock_id) AND (seedlot_current_count.type_id = 77580))))
     LEFT JOIN stockprop seedlot_current_weight ON (((seedlot.stock_id = seedlot_current_weight.stock_id) AND (seedlot_current_weight.type_id = 77621))))
     LEFT JOIN stockprop seedlot_seedlot_box ON (((seedlot.stock_id = seedlot_seedlot_box.stock_id) AND (seedlot_seedlot_box.type_id = 76520))))
     LEFT JOIN nd_experiment_phenotype ON ((nd_experiment_phenotype.nd_experiment_id = nd_experiment.nd_experiment_id)))
     LEFT JOIN phenotype USING (phenotype_id))
     JOIN cvterm observationunit_cvterm ON ((observationunit.type_id = observationunit_cvterm.cvterm_id)))
     JOIN stock_relationship ON (((observationunit.stock_id = stock_relationship.subject_id) AND (stock_relationship.type_id = ANY (ARRAY[76394, 76506, 77578, 78145])))))
     JOIN stock germplasm ON (((stock_relationship.object_id = germplasm.stock_id) AND (germplasm.type_id = ANY (ARRAY[76392, 76446, 77647])))))
     LEFT JOIN phenome.nd_experiment_md_images nd_experiment_md_images ON ((nd_experiment.nd_experiment_id = nd_experiment_md_images.nd_experiment_id)))
     LEFT JOIN metadata.md_image md_image ON ((nd_experiment_md_images.image_id = md_image.image_id)))
     LEFT JOIN phenome.project_md_image project_md_image ON ((md_image.image_id = project_md_image.image_id)))
     LEFT JOIN cvterm project_md_image_type ON ((project_md_image.type_id = project_md_image_type.cvterm_id)))
     LEFT JOIN project drone_image_project ON ((project_md_image.project_id = drone_image_project.project_id)))
     LEFT JOIN projectprop drone_image_project_time_json ON (((drone_image_project.project_id = drone_image_project_time_json.project_id) AND (drone_image_project_time_json.type_id = 78125))))
     LEFT JOIN stock_relationship available_seedlot_rel ON (((available_seedlot_rel.subject_id = germplasm.stock_id) AND (available_seedlot_rel.type_id = 77561))))
     LEFT JOIN stock available_seelot ON (((available_seedlot_rel.object_id = available_seelot.stock_id) AND (seedlot.type_id = 77563))))
     LEFT JOIN stockprop current_count ON (((available_seelot.stock_id = current_count.stock_id) AND (current_count.type_id = 77580))))
     LEFT JOIN stockprop current_weight ON (((available_seelot.stock_id = current_weight.stock_id) AND (current_weight.type_id = 77621))))
     LEFT JOIN stockprop seedlot_box ON (((available_seelot.stock_id = seedlot_box.stock_id) AND (seedlot_box.type_id = 76520))))
     LEFT JOIN stockprop rep ON (((observationunit.stock_id = rep.stock_id) AND (rep.type_id = 76459))))
     LEFT JOIN stockprop block_number ON (((observationunit.stock_id = block_number.stock_id) AND (block_number.type_id = 76460))))
     LEFT JOIN stockprop plot_number ON (((observationunit.stock_id = plot_number.stock_id) AND (plot_number.type_id = 76461))))
     LEFT JOIN stockprop row_number ON (((observationunit.stock_id = row_number.stock_id) AND (row_number.type_id = 76523) AND (row_number.rank = 0))))
     LEFT JOIN stockprop col_number ON (((observationunit.stock_id = col_number.stock_id) AND (col_number.type_id = 76517) AND (col_number.rank = 0))))
     LEFT JOIN stockprop plant_number ON (((observationunit.stock_id = plant_number.stock_id) AND (plant_number.type_id = 77104))))
     LEFT JOIN stockprop is_a_control ON (((observationunit.stock_id = is_a_control.stock_id) AND (is_a_control.type_id = 76519))))
     LEFT JOIN stockprop notes ON (((observationunit.stock_id = notes.stock_id) AND (notes.type_id = 77600))))
     LEFT JOIN phenotypeprop outlier ON (((phenotype.phenotype_id = outlier.phenotype_id) AND (outlier.type_id = 77605))))
     LEFT JOIN cvterm ON ((phenotype.cvalue_id = cvterm.cvterm_id)))
     LEFT JOIN dbxref ON ((cvterm.dbxref_id = dbxref.dbxref_id)))
     LEFT JOIN db USING (db_id))
     JOIN nd_experiment_project ON ((nd_experiment_project.nd_experiment_id = nd_experiment.nd_experiment_id)))
     JOIN project ON ((nd_experiment_project.project_id = project.project_id)))
     JOIN project_relationship ON (((project.project_id = project_relationship.subject_project_id) AND (project_relationship.type_id = 76448))))
     JOIN project breeding_program ON ((breeding_program.project_id = project_relationship.object_project_id)))
     LEFT JOIN projectprop year ON (((project.project_id = year.project_id) AND (year.type_id = 76395))))
     LEFT JOIN projectprop design ON (((project.project_id = design.project_id) AND (design.type_id = 76458))))
     LEFT JOIN projectprop location_id ON (((project.project_id = location_id.project_id) AND (location_id.type_id = 76462))))
     LEFT JOIN projectprop planting_date ON (((project.project_id = planting_date.project_id) AND (planting_date.type_id = 76496))))
     LEFT JOIN projectprop harvest_date ON (((project.project_id = harvest_date.project_id) AND (harvest_date.type_id = 76495))))
     LEFT JOIN projectprop plot_width ON (((project.project_id = plot_width.project_id) AND (plot_width.type_id = 77634))))
     LEFT JOIN projectprop plot_length ON (((project.project_id = plot_length.project_id) AND (plot_length.type_id = 77635))))
     LEFT JOIN projectprop field_size ON (((project.project_id = field_size.project_id) AND (field_size.type_id = 77636))))
     LEFT JOIN projectprop field_trial_is_planned_to_be_genotyped ON (((project.project_id = field_trial_is_planned_to_be_genotyped.project_id) AND (field_trial_is_planned_to_be_genotyped.type_id = 77637))))
     LEFT JOIN projectprop field_trial_is_planned_to_cross ON (((project.project_id = field_trial_is_planned_to_cross.project_id) AND (field_trial_is_planned_to_cross.type_id = 77638))))
     LEFT JOIN nd_experiment_stock treatment_nds ON (((treatment_nds.type_id = 77567) AND (treatment_nds.stock_id = observationunit.stock_id))))
     LEFT JOIN nd_experiment_project treatment_ndp ON ((treatment_ndp.nd_experiment_id = treatment_nds.nd_experiment_id)))
     LEFT JOIN project_relationship treatment_rel ON (((project.project_id = treatment_rel.object_project_id) AND (treatment_rel.type_id = 77568))))
     LEFT JOIN project treatment ON (((treatment.project_id = treatment_rel.subject_project_id) AND (treatment.project_id = treatment_ndp.project_id))))
     LEFT JOIN project_relationship folder_rel ON (((project.project_id = folder_rel.subject_project_id) AND (folder_rel.type_id = 76504))))
     LEFT JOIN project folder ON ((folder.project_id = folder_rel.object_project_id)))
  WHERE ((nd_experiment.type_id = ANY (ARRAY[76441, 76477, 76391, 78141])) AND (design.value <> 'genotype_data_project'::text) AND (design.value <> 'treatment'::text))
  GROUP BY observationunit.stock_id, observationunit.uniquename, observationunit_cvterm.name, germplasm.uniquename, germplasm.stock_id, rep.value, block_number.value, plot_number.value, row_number.value, col_number.value, plant_number.value, is_a_control.value, project.project_id, project.name, project.description, breeding_program.project_id, breeding_program.name, breeding_program.description, year.value, design.value, location_id.value, planting_date.value, harvest_date.value, plot_width.value, plot_length.value, field_size.value, field_trial_is_planned_to_be_genotyped.value, field_trial_is_planned_to_cross.value, folder.project_id, folder.name, folder.description, seedplot_planted.value, seedlot.stock_id, seedlot.uniquename, seedlot_current_weight.value, seedlot_current_count.value, seedlot_seedlot_box.value
  ORDER BY project.project_id, observationunit.uniquename;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views