@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