materialized_phenotype_jsonb_table

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;