@mrjones, I did run Couch2pg again this evening, but realized the fields I would use as primary keys on the materialize views have duplicates, arising from editing of the same record by the Clinicians to update some fields like lab tests and/or any other delayed feedback.
So creating indexes is now tricky, had to delete the indexes for Couch2pg API to run without errors.
Have gone through community posts but didn’t find any clear way of handling materialized views with duplicate values as index, any ideas?
Will setup CHT Sync on local host, once we have all the data from CouchDb to PostgreSQL
Hi @oyierphil - splitting this question off as it’s a bit different than the original topic.
I’m gonna defer to @michael on this one as I think he’s likely already solved it!
A primary key is a unique identifier, so it cannot have duplicates. Also, editing a record should not create two versions of a doc. When a doc is edited in CouchDB, couch2pg will delete the old record and insert the new record. So if you are seeing things like a duplicate _id field in your materialized view, the problem might be with the query that defines your materialized view.
That said… it’s possible to create a multicolumn primary key and/or a multicolumn index, but the combination of those columns still must be unique.
@michael, the issue of duplicates has been worrying, and the challenge during cleaning is identifying the correct record and the one that should be deleted, my queries as below:
Materiazed View 1, userview_cif_final
SELECT couchdb.doc ->> ‘_id’::text AS uuid,
couchdb.doc ->> ‘form’::text AS form,
couchdb.doc #>> ‘{fields,patient_uuid}’::text AS patient_uuid,
couchdb.doc #>> ‘{fields,p_id}’::text AS patient_id,
couchdb.doc #>> ‘{fields,p_sex}’::text AS patient_sex,
couchdb.doc #>> ‘{fields,triage_measurements,temp}’::text AS temparature,
couchdb.doc #>> ‘{fields,triage_measurements,bp_systolic}’::text AS bp_systolic,
couchdb.doc #>> ‘{fields,triage_measurements,bp_diastolic}’::text AS bp_diastolic,
couchdb.doc #>> ‘{fields,triage_measurements,weight}’::text AS weight,
couchdb.doc #>> ‘{fields,triage_measurements,height}’::text AS height,
couchdb.doc #>> ‘{fields,triage_measurements,bmi}’::text AS bmi,
couchdb.doc #>> ‘{fields,triage_measurements,c2}’::text AS c2,
couchdb.doc #>> ‘{fields,triage_measurements,pulse_rate}’::text AS pulse_rate,
couchdb.doc #>> ‘{fields,triage_measurements,respiratory_rate}’::text AS respiratory_rate,
couchdb.doc #>> ‘{fields,triage_measurements,spo2}’::text AS spo2,
couchdb.doc #>> ‘{fields,triage_measurements,muac}’::text AS muac,
couchdb.doc #>> ‘{fields,triage_measurements,symptoms}’::text AS symptoms,
couchdb.doc #>> ‘{fields,triage_measurements,symptoms_other}’::text AS other_symptoms,
couchdb.doc #>> ‘{fields,c1}’::text AS c1_fever,
couchdb.doc #>> ‘{fields,c1.1}’::text AS c1_1,
couchdb.doc #>> ‘{fields,c1.2}’::text AS c1_2,
couchdb.doc #>> ‘{fields,c1.3}’::text AS c1_3,
couchdb.doc #>> ‘{fields,c1.4}’::text AS c1_4,
couchdb.doc #>> ‘{fields,c1.5}’::text AS c1_5,
couchdb.doc #>> ‘{fields,c1.6}’::text AS c1_6,
couchdb.doc #>> ‘{fields,c1.7}’::text AS c1_7,
couchdb.doc #>> ‘{fields,c1.8}’::text AS c1_8,
couchdb.doc #>> ‘{fields,c1.9}’::text AS c1_9,
couchdb.doc #>> ‘{fields,c1.10}’::text AS c1_10,
couchdb.doc #>> ‘{fields,c1.11}’::text AS c1_11,
couchdb.doc #>> ‘{fields,c1.12}’::text AS c1_12,
couchdb.doc #>> ‘{fields,c1.13}’::text AS c1_13,
couchdb.doc #>> ‘{fields,c1.14}’::text AS c1_14,
couchdb.doc #>> ‘{fields,c1.15}’::text AS c1_15,
couchdb.doc #>> ‘{fields,c1.16}’::text AS c1_16,
couchdb.doc #>> ‘{fields,c1.17}’::text AS c1_17,
couchdb.doc #>> ‘{fields,c1.18}’::text AS c1_18,
couchdb.doc #>> ‘{fields,c1.19}’::text AS c1_19,
couchdb.doc #>> ‘{fields,c1.20}’::text AS c1_20,
couchdb.doc #>> ‘{fields,c1.21}’::text AS c1_21,
couchdb.doc #>> ‘{fields,c1.22}’::text AS c1_22,
couchdb.doc #>> ‘{fields,c1.23}’::text AS c1_23,
couchdb.doc #>> ‘{fields,c1.24}’::text AS c1_24,
couchdb.doc #>> ‘{fields,c1.25}’::text AS c1_25,
couchdb.doc #>> ‘{fields,c1.26}’::text AS c1_26,
couchdb.doc #>> ‘{fields,c1.27}’::text AS c1_27,
couchdb.doc #>> ‘{fields,c1.28}’::text AS c1_28,
couchdb.doc #>> ‘{fields,c1.29}’::text AS c1_29,
couchdb.doc #>> ‘{fields,c1.30}’::text AS c1_30,
couchdb.doc #>> ‘{fields,c1.31}’::text AS c1_31,
couchdb.doc #>> ‘{fields,c1.32}’::text AS c1_32,
couchdb.doc #>> ‘{fields,c1.33}’::text AS c1_33,
couchdb.doc #>> ‘{fields,c1.34}’::text AS c1_34,
couchdb.doc #>> ‘{fields,c1.35}’::text AS c1_35,
couchdb.doc #>> ‘{fields,c1.36}’::text AS c1_36,
couchdb.doc #>> ‘{fields,c1.37}’::text AS c1_37,
couchdb.doc #>> ‘{fields,c1.38}’::text AS c1_38,
couchdb.doc #>> ‘{fields,c1.39}’::text AS c1_39,
couchdb.doc #>> ‘{fields,mal_total}’::text AS mal_total,
couchdb.doc #>> ‘{fields,mal_aggregate}’::text AS mal_aggregate,
couchdb.doc #>> ‘{fields,mal_diag1}’::text AS mal_diag1,
couchdb.doc #>> ‘{fields,mal_diag2}’::text AS mal_diag2,
couchdb.doc #>> ‘{fields,mal_diag}’::text AS mal_diag,
couchdb.doc #>> ‘{fields,c2.1}’::text AS c2_1,
couchdb.doc #>> ‘{fields,c2.2}’::text AS c2_2,
couchdb.doc #>> ‘{fields,c2.3}’::text AS c2_3,
couchdb.doc #>> ‘{fields,c2.4}’::text AS c2_4,
couchdb.doc #>> ‘{fields,c2.5}’::text AS c2_5,
couchdb.doc #>> ‘{fields,c2.6}’::text AS c2_6,
couchdb.doc #>> ‘{fields,c2.7}’::text AS c2_7,
couchdb.doc #>> ‘{fields,c2.8}’::text AS c2_8,
couchdb.doc #>> ‘{fields,c2.9}’::text AS c2_9,
couchdb.doc #>> ‘{fields,c2.10}’::text AS c2_10,
couchdb.doc #>> ‘{fields,c2.11}’::text AS c2_11,
couchdb.doc #>> ‘{fields,c2.12}’::text AS c2_12,
couchdb.doc #>> ‘{fields,c2.13}’::text AS c2_13,
couchdb.doc #>> ‘{fields,c2.14}’::text AS c2_14,
couchdb.doc #>> ‘{fields,c2.15}’::text AS c2_15,
couchdb.doc #>> ‘{fields,c2.16}’::text AS c2_16,
couchdb.doc #>> ‘{fields,c2.17}’::text AS c2_17,
couchdb.doc #>> ‘{fields,c2.18}’::text AS c2_18,
couchdb.doc #>> ‘{fields,c2.19}’::text AS c2_19,
couchdb.doc #>> ‘{fields,c2.20}’::text AS c2_20,
couchdb.doc #>> ‘{fields,c2.21}’::text AS c2_21,
couchdb.doc #>> ‘{fields,c2.22}’::text AS c2_22,
couchdb.doc #>> ‘{fields,c2.23}’::text AS c2_23,
couchdb.doc #>> ‘{fields,c2.24}’::text AS c2_24,
couchdb.doc #>> ‘{fields,c2.25}’::text AS c2_25,
couchdb.doc #>> ‘{fields,c2.26}’::text AS c2_26,
couchdb.doc #>> ‘{fields,c2.27}’::text AS c2_27,
couchdb.doc #>> ‘{fields,c2.28}’::text AS c2_28,
couchdb.doc #>> ‘{fields,c2.29}’::text AS c2_29,
couchdb.doc #>> ‘{fields,c2.30}’::text AS c2_30,
couchdb.doc #>> ‘{fields,c2.31}’::text AS c2_31,
couchdb.doc #>> ‘{fields,c2.32}’::text AS c2_32,
couchdb.doc #>> ‘{fields,c2.33}’::text AS c2_33,
couchdb.doc #>> ‘{fields,c2.34}’::text AS c2_34,
couchdb.doc #>> ‘{fields,c2.35}’::text AS c2_35,
couchdb.doc #>> ‘{fields,c2.36}’::text AS c2_36,
couchdb.doc #>> ‘{fields,c2.37}’::text AS c2_37,
couchdb.doc #>> ‘{fields,c2.38}’::text AS c2_38,
couchdb.doc #>> ‘{fields,c2.39}’::text AS c2_39,
couchdb.doc #>> ‘{fields,diar_total}’::text AS diar_total,
couchdb.doc #>> ‘{fields,diar_aggregate}’::text AS diar_aggregate,
couchdb.doc #>> ‘{fields,diar_diag}’::text AS diar_diag,
couchdb.doc #>> ‘{fields,diar_diag1}’::text AS diar_diag1,
couchdb.doc #>> ‘{fields,c3.1}’::text AS c3_1,
couchdb.doc #>> ‘{fields,c3.2}’::text AS c3_2,
couchdb.doc #>> ‘{fields,c3.3}’::text AS c3_3,
couchdb.doc #>> ‘{fields,c3.4}’::text AS c3_4,
couchdb.doc #>> ‘{fields,c3.5}’::text AS c3_5,
couchdb.doc #>> ‘{fields,c3.6}’::text AS c3_6,
couchdb.doc #>> ‘{fields,c3.7}’::text AS c3_7,
couchdb.doc #>> ‘{fields,c3.8}’::text AS c3_8,
couchdb.doc #>> ‘{fields,c3.9}’::text AS c3_9,
couchdb.doc #>> ‘{fields,c3.10}’::text AS c3_10,
couchdb.doc #>> ‘{fields,c3.11}’::text AS c3_11,
couchdb.doc #>> ‘{fields,c3.12}’::text AS c3_12,
couchdb.doc #>> ‘{fields,c3.13}’::text AS c3_13,
couchdb.doc #>> ‘{fields,c3.14}’::text AS c3_14,
couchdb.doc #>> ‘{fields,c3.15}’::text AS c3_15,
couchdb.doc #>> ‘{fields,c3.16}’::text AS c3_16,
couchdb.doc #>> ‘{fields,c3.17}’::text AS c3_17,
couchdb.doc #>> ‘{fields,c3.18}’::text AS c3_18,
couchdb.doc #>> ‘{fields,c3.19}’::text AS c3_19,
couchdb.doc #>> ‘{fields,c3.20}’::text AS c3_20,
couchdb.doc #>> ‘{fields,c3.21}’::text AS c3_21,
couchdb.doc #>> ‘{fields,c3.22}’::text AS c3_22,
couchdb.doc #>> ‘{fields,c3.23}’::text AS c3_23,
couchdb.doc #>> ‘{fields,c3.24}’::text AS c3_24,
couchdb.doc #>> ‘{fields,c3.25}’::text AS c3_25,
couchdb.doc #>> ‘{fields,c3.26}’::text AS c3_26,
couchdb.doc #>> ‘{fields,c3.27}’::text AS c3_27,
couchdb.doc #>> ‘{fields,c3.28}’::text AS c3_28,
couchdb.doc #>> ‘{fields,c3.29}’::text AS c3_29,
couchdb.doc #>> ‘{fields,c3.30}’::text AS c3_30,
couchdb.doc #>> ‘{fields,c3.31}’::text AS c3_31,
couchdb.doc #>> ‘{fields,c3.32}’::text AS c3_32,
couchdb.doc #>> ‘{fields,c3.33}’::text AS c3_33,
couchdb.doc #>> ‘{fields,c3.34}’::text AS c3_34,
couchdb.doc #>> ‘{fields,c3.35}’::text AS c3_35,
couchdb.doc #>> ‘{fields,c3.36}’::text AS c3_36,
couchdb.doc #>> ‘{fields,c3.37}’::text AS c3_37,
couchdb.doc #>> ‘{fields,c3.38}’::text AS c3_38,
couchdb.doc #>> ‘{fields,c3.39}’::text AS c3_39,
couchdb.doc #>> ‘{fields,malnut_total}’::text AS malnut_total,
couchdb.doc #>> ‘{fields,malnut_aggregate}’::text AS malnut_aggregate,
couchdb.doc #>> ‘{fields,malnut_diag1}’::text AS malnut_diag1,
couchdb.doc #>> ‘{fields,malnut_diag2}’::text AS malnut_diag2,
couchdb.doc #>> ‘{fields,malnut_diag}’::text AS malnut_diag,
couchdb.doc #>> ‘{fields,c4.1}’::text AS c4_1,
couchdb.doc #>> ‘{fields,c4.2}’::text AS c4_2,
couchdb.doc #>> ‘{fields,c4.3}’::text AS c4_3,
couchdb.doc #>> ‘{fields,c4.4}’::text AS c4_4,
couchdb.doc #>> ‘{fields,c4.5}’::text AS c4_5,
couchdb.doc #>> ‘{fields,c4.6}’::text AS c4_6,
couchdb.doc #>> ‘{fields,c4.7}’::text AS c4_7,
couchdb.doc #>> ‘{fields,c4.8}’::text AS c4_8,
couchdb.doc #>> ‘{fields,c4.9}’::text AS c4_9,
couchdb.doc #>> ‘{fields,c4.10}’::text AS c4_10,
couchdb.doc #>> ‘{fields,c4.11}’::text AS c4_11,
couchdb.doc #>> ‘{fields,c4.12}’::text AS c4_12,
couchdb.doc #>> ‘{fields,c4.13}’::text AS c4_13,
couchdb.doc #>> ‘{fields,c4.14}’::text AS c4_14,
couchdb.doc #>> ‘{fields,c4.15}’::text AS c4_15,
couchdb.doc #>> ‘{fields,c4.16}’::text AS c4_16,
couchdb.doc #>> ‘{fields,c4.17}’::text AS c4_17,
couchdb.doc #>> ‘{fields,c4.18}’::text AS c4_18,
couchdb.doc #>> ‘{fields,c4.19}’::text AS c4_19,
couchdb.doc #>> ‘{fields,c4.20}’::text AS c4_20,
couchdb.doc #>> ‘{fields,c4.21}’::text AS c4_21,
couchdb.doc #>> ‘{fields,c4.22}’::text AS c4_22,
couchdb.doc #>> ‘{fields,c4.23}’::text AS c4_23,
couchdb.doc #>> ‘{fields,c4.24}’::text AS c4_24,
couchdb.doc #>> ‘{fields,c4.25}’::text AS c4_25,
couchdb.doc #>> ‘{fields,c4.26}’::text AS c4_26,
couchdb.doc #>> ‘{fields,c4.27}’::text AS c4_27,
couchdb.doc #>> ‘{fields,c4.28}’::text AS c4_28,
couchdb.doc #>> ‘{fields,c4.29}’::text AS c4_29,
couchdb.doc #>> ‘{fields,c4.30}’::text AS c4_30,
couchdb.doc #>> ‘{fields,c4.31}’::text AS c4_31,
couchdb.doc #>> ‘{fields,c4.32}’::text AS c4_32,
couchdb.doc #>> ‘{fields,c4.33}’::text AS c4_33,
couchdb.doc #>> ‘{fields,c4.34}’::text AS c4_34,
couchdb.doc #>> ‘{fields,c4.35}’::text AS c4_35,
couchdb.doc #>> ‘{fields,c4.36}’::text AS c4_36,
couchdb.doc #>> ‘{fields,c4.37}’::text AS c4_37,
couchdb.doc #>> ‘{fields,c4.38}’::text AS c4_38,
couchdb.doc #>> ‘{fields,c4.39}’::text AS c4_39,
couchdb.doc #>> ‘{fields,typh_total}’::text AS typh_total,
couchdb.doc #>> ‘{fields,typh_aggregate}’::text AS typh_aggregate,
couchdb.doc #>> ‘{fields,typh_diag1}’::text AS typh_diag1,
couchdb.doc #>> ‘{fields,typh_diag2}’::text AS typh_diag2,
couchdb.doc #>> ‘{fields,typh_diag}’::text AS typh_diag,
couchdb.doc #>> ‘{fields,c5.1}’::text AS c5_1,
couchdb.doc #>> ‘{fields,c5.2}’::text AS c5_2,
couchdb.doc #>> ‘{fields,c5.3}’::text AS c5_3,
couchdb.doc #>> ‘{fields,c5.4}’::text AS c5_4,
couchdb.doc #>> ‘{fields,c5.5}’::text AS c5_5,
couchdb.doc #>> ‘{fields,c5.6}’::text AS c5_6,
couchdb.doc #>> ‘{fields,c5.7}’::text AS c5_7,
couchdb.doc #>> ‘{fields,c5.8}’::text AS c5_8,
couchdb.doc #>> ‘{fields,c5.9}’::text AS c5_9,
couchdb.doc #>> ‘{fields,c5.10}’::text AS c5_10,
couchdb.doc #>> ‘{fields,c5.11}’::text AS c5_11,
couchdb.doc #>> ‘{fields,c5.12}’::text AS c5_12,
couchdb.doc #>> ‘{fields,c5.13}’::text AS c5_13,
couchdb.doc #>> ‘{fields,c5.14}’::text AS c5_14,
couchdb.doc #>> ‘{fields,c5.15}’::text AS c5_15,
couchdb.doc #>> ‘{fields,c5.16}’::text AS c5_16,
couchdb.doc #>> ‘{fields,c5.17}’::text AS c5_17,
couchdb.doc #>> ‘{fields,c5.18}’::text AS c5_18,
couchdb.doc #>> ‘{fields,c5.19}’::text AS c5_19,
couchdb.doc #>> ‘{fields,c5.20}’::text AS c5_20,
couchdb.doc #>> ‘{fields,c5.21}’::text AS c5_21,
couchdb.doc #>> ‘{fields,c5.22}’::text AS c5_22,
couchdb.doc #>> ‘{fields,c5.23}’::text AS c5_23,
couchdb.doc #>> ‘{fields,c5.24}’::text AS c5_24,
couchdb.doc #>> ‘{fields,c5.25}’::text AS c5_25,
couchdb.doc #>> ‘{fields,c5.26}’::text AS c5_26,
couchdb.doc #>> ‘{fields,c5.27}’::text AS c5_27,
couchdb.doc #>> ‘{fields,c5.28}’::text AS c5_28,
couchdb.doc #>> ‘{fields,c5.29}’::text AS c5_29,
couchdb.doc #>> ‘{fields,c5.30}’::text AS c5_30,
couchdb.doc #>> ‘{fields,c5.31}’::text AS c5_31,
couchdb.doc #>> ‘{fields,c5.32}’::text AS c5_32,
couchdb.doc #>> ‘{fields,c5.33}’::text AS c5_33,
couchdb.doc #>> ‘{fields,c5.34}’::text AS c5_34,
couchdb.doc #>> ‘{fields,c5.35}’::text AS c5_35,
couchdb.doc #>> ‘{fields,c5.36}’::text AS c5_36,
couchdb.doc #>> ‘{fields,c5.37}’::text AS c5_37,
couchdb.doc #>> ‘{fields,c5.38}’::text AS c5_38,
couchdb.doc #>> ‘{fields,c5.39}’::text AS c5_39,
couchdb.doc #>> ‘{fields,pneu_total}’::text AS pneu_total,
couchdb.doc #>> ‘{fields,pneu_aggregate}’::text AS pneu_aggregate,
couchdb.doc #>> ‘{fields,pneu_diag}’::text AS pneu_diag,
couchdb.doc #>> ‘{fields,pneu_diag1}’::text AS pneu_diag1,
couchdb.doc #>> ‘{fields,c6.1}’::text AS c6_1,
couchdb.doc #>> ‘{fields,c6.2}’::text AS c6_2,
couchdb.doc #>> ‘{fields,c6.3}’::text AS c6_3,
couchdb.doc #>> ‘{fields,c6.4}’::text AS c6_4,
couchdb.doc #>> ‘{fields,c6.5}’::text AS c6_5,
couchdb.doc #>> ‘{fields,c6.6}’::text AS c6_6,
couchdb.doc #>> ‘{fields,c6.7}’::text AS c6_7,
couchdb.doc #>> ‘{fields,c6.8}’::text AS c6_8,
couchdb.doc #>> ‘{fields,c6.9}’::text AS c6_9,
couchdb.doc #>> ‘{fields,c6.10}’::text AS c6_10,
couchdb.doc #>> ‘{fields,c6.11}’::text AS c6_11,
couchdb.doc #>> ‘{fields,c6.12}’::text AS c6_12,
couchdb.doc #>> ‘{fields,c6.13}’::text AS c6_13,
couchdb.doc #>> ‘{fields,c6.14}’::text AS c6_14,
couchdb.doc #>> ‘{fields,c6.15}’::text AS c6_15,
couchdb.doc #>> ‘{fields,c6.16}’::text AS c6_16,
couchdb.doc #>> ‘{fields,c6.17}’::text AS c6_17,
couchdb.doc #>> ‘{fields,c6.18}’::text AS c6_18,
couchdb.doc #>> ‘{fields,c6.19}’::text AS c6_19,
couchdb.doc #>> ‘{fields,c6.20}’::text AS c6_20,
couchdb.doc #>> ‘{fields,c6.21}’::text AS c6_21,
couchdb.doc #>> ‘{fields,c6.22}’::text AS c6_22,
couchdb.doc #>> ‘{fields,c6.23}’::text AS c6_23,
couchdb.doc #>> ‘{fields,c6.24}’::text AS c6_24,
couchdb.doc #>> ‘{fields,c6.25}’::text AS c6_25,
couchdb.doc #>> ‘{fields,c6.26}’::text AS c6_26,
couchdb.doc #>> ‘{fields,c6.27}’::text AS c6_27,
couchdb.doc #>> ‘{fields,c6.28}’::text AS c6_28,
couchdb.doc #>> ‘{fields,c6.29}’::text AS c6_29,
couchdb.doc #>> ‘{fields,c6.30}’::text AS c6_30,
couchdb.doc #>> ‘{fields,c6.31}’::text AS c6_31,
couchdb.doc #>> ‘{fields,c6.32}’::text AS c6_32,
couchdb.doc #>> ‘{fields,c6.33}’::text AS c6_33,
couchdb.doc #>> ‘{fields,c6.34}’::text AS c6_34,
couchdb.doc #>> ‘{fields,c6.35}’::text AS c6_35,
couchdb.doc #>> ‘{fields,c6.36}’::text AS c6_36,
couchdb.doc #>> ‘{fields,c6.37}’::text AS c6_37,
couchdb.doc #>> ‘{fields,c6.38}’::text AS c6_38,
couchdb.doc #>> ‘{fields,c6.39}’::text AS c6_39,
couchdb.doc #>> ‘{fields,tb_total}’::text AS tb_total,
couchdb.doc #>> ‘{fields,tb_aggregate}’::text AS tb_aggregate,
couchdb.doc #>> ‘{fields,tb_diag1}’::text AS tb_diag1,
couchdb.doc #>> ‘{fields,tb_diag2}’::text AS tb_diag2,
couchdb.doc #>> ‘{fields,tb_diag}’::text AS tb_diag,
couchdb.doc #>> ‘{fields,c7.1}’::text AS c7_1,
couchdb.doc #>> ‘{fields,c7.2}’::text AS c7_2,
couchdb.doc #>> ‘{fields,c7.3}’::text AS c7_3,
couchdb.doc #>> ‘{fields,c7.4}’::text AS c7_4,
couchdb.doc #>> ‘{fields,c7.5}’::text AS c7_5,
couchdb.doc #>> ‘{fields,c7.6}’::text AS c7_6,
couchdb.doc #>> ‘{fields,c7.7}’::text AS c7_7,
couchdb.doc #>> ‘{fields,c7.8}’::text AS c7_8,
couchdb.doc #>> ‘{fields,c7.9}’::text AS c7_9,
couchdb.doc #>> ‘{fields,c7.10}’::text AS c7_10,
couchdb.doc #>> ‘{fields,c7.11}’::text AS c7_11,
couchdb.doc #>> ‘{fields,c7.12}’::text AS c7_12,
couchdb.doc #>> ‘{fields,c7.13}’::text AS c7_13,
couchdb.doc #>> ‘{fields,c7.14}’::text AS c7_14,
couchdb.doc #>> ‘{fields,c7.15}’::text AS c7_15,
couchdb.doc #>> ‘{fields,c7.16}’::text AS c7_16,
couchdb.doc #>> ‘{fields,c7.17}’::text AS c7_17,
couchdb.doc #>> ‘{fields,c7.18}’::text AS c7_18,
couchdb.doc #>> ‘{fields,c7.19}’::text AS c7_19,
couchdb.doc #>> ‘{fields,c7.20}’::text AS c7_20,
couchdb.doc #>> ‘{fields,c7.21}’::text AS c7_21,
couchdb.doc #>> ‘{fields,c7.22}’::text AS c7_22,
couchdb.doc #>> ‘{fields,c7.23}’::text AS c7_23,
couchdb.doc #>> ‘{fields,c7.24}’::text AS c7_24,
couchdb.doc #>> ‘{fields,c7.25}’::text AS c7_25,
couchdb.doc #>> ‘{fields,c7.26}’::text AS c7_26,
couchdb.doc #>> ‘{fields,c7.27}’::text AS c7_27,
couchdb.doc #>> ‘{fields,c7.28}’::text AS c7_28,
couchdb.doc #>> ‘{fields,c7.29}’::text AS c7_29,
couchdb.doc #>> ‘{fields,c7.30}’::text AS c7_30,
couchdb.doc #>> ‘{fields,c7.31}’::text AS c7_31,
couchdb.doc #>> ‘{fields,c7.32}’::text AS c7_32,
couchdb.doc #>> ‘{fields,c7.33}’::text AS c7_33,
couchdb.doc #>> ‘{fields,c7.34}’::text AS c7_34,
couchdb.doc #>> ‘{fields,c7.35}’::text AS c7_35,
couchdb.doc #>> ‘{fields,c7.36}’::text AS c7_36,
couchdb.doc #>> ‘{fields,c7.37}’::text AS c7_37,
couchdb.doc #>> ‘{fields,c7.38}’::text AS c7_38,
couchdb.doc #>> ‘{fields,c7.39}’::text AS c7_39,
couchdb.doc #>> ‘{fields,cov_total}’::text AS cov_total,
couchdb.doc #>> ‘{fields,cov_aggregate}’::text AS cov_aggregate,
couchdb.doc #>> ‘{fields,cov_diag}’::text AS cov_diag,
couchdb.doc #>> ‘{fields,cov_diag1}’::text AS cov_diag1,
couchdb.doc #>> ‘{fields,lab}’::text AS lab,
couchdb.doc #>> ‘{fields,lab1}’::text AS lab1,
jsonb_array_elements(couchdb.doc #> ‘{fields,repeat2}’::text) → ‘choice_lab1’::text AS choice_lab1,
jsonb_array_elements(couchdb.doc #> ‘{fields,repeat2}’::text) → ‘dur_012’::text AS dur_012,
couchdb.doc #>> ‘{fields,clinical}’::text AS clinical
FROM couchdb,
couchdb_medic_users
WHERE (couchdb.doc ->> ‘form’::text) = ‘case_investigation’::text;
Materiazed View 2, userview_clinician_details_final
SELECT raw_contacts.doc ->> ‘_id’::text AS uuid,
raw_contacts.doc ->> ‘name’::text AS name,
raw_contacts.doc ->> ‘type’::text AS type,
raw_contacts.doc ->> ‘contact_type’::text AS contact_type,
raw_contacts.doc #>> ‘{contact,_id}’::text AS contact_uuid,
raw_contacts.doc #>> ‘{parent,_id}’::text AS parent_uuid,
raw_contacts.doc #>> ‘{meta,created_by}’::text AS created_by,
raw_contacts.doc #>> ‘{meta,created_by_place_uuid}’::text AS created_by_place_uuid,
raw_contacts.doc #>> ‘{meta,created_by_person_uuid}’::text AS created_by_person_uuid,
raw_contacts.doc ->> ‘notes’::text AS notes
FROM raw_contacts;
View, picking all fields from the two materialized views
select *
FROM userview_cif_final
JOIN userview_clinician_details_final
ON userview_cif_final.patient_uuid = userview_clinician_details_final.p_uuid;
Would appreciate your review of the code, and your view on repeated fields, whose results is kept as separate rows with same uuid, thus still a challenge to use uuids as primary keys.
Thought of composite primary keys, didn’t get the unique combination that will eliminate duplicates.
So figuring out two challenges, open to suggestions:
- The reason for repeated keys, which affects Couch2pg API since duplicates won’t allow creation of unique indexes on the materialized views
- The reason why Couch2pg API isnt working as expected, suspect 1 being lack of unique indexes on the materialized view
@oyierphil you have a couple of problems with userview_cif_final
The first problem is that you have couchdb_medic_users in your FROM but you are not using any fields from that table. You should remove it.
The second problem is that you have included the repeat field in this view and are causing duplicates by using that jsonb_array_elements function, which will give you one row for every array element. As I suggested here, you should have a completely separate view for your repeat fields.
Here’s an example of what you should do (the syntax might be slightly different but this is the concept you should implement).
FIRST VIEW: → MATERIALIZED VIEW
This will give you one row per case_investigation report and you can set the index to be the uuid field. It will also have one field that contains all array elements for the repeat field, stored AS AN ARRAY.
SELECT
doc ->> ‘_id’ AS uuid,
doc ->> ‘form’ AS form,
doc #>> ‘{fields,patient_uuid}’ AS patient_uuid,
...
...
doc #> ‘{fields,repeat2}’ AS repeat2
FROM
couchdb
WHERE
doc ->> ‘form’ = ‘case_investigation’;
SECOND VIEW → Non-Materialized view
This splits out the array elements into individual rows. This does not need to be a materialized view and therefore does not need an index.
SELECT
uuid,
jsonb_array_elements(repeat2) → ‘choice_lab1’ AS choice_lab1,
jsonb_array_elements(repeat2) → ‘dur_012’ AS dur_012
FROM
userview_cif_final