Hi I have a repeat condition for some lab tests, where the clinician selects a number of tests, and for each test indicates the results and diagnosis based on the results as below: [Repeat condition_CouchDB to PostGres Migration] I wish to query the Json data and extract the lab test, result and…

@oyierphil data from repeats are stored as an array on the data_record. To work with arrays, you can use the jsonb_array_elements() function . This will give you one row for every item in the array. A common design pattern would be to have all your non-repeat fields in one table/view, and have a s…

@oyierphil - are you able to share the form? this way we can reproduce the reports and see what they’d look like in Postgres

@mrjones , I shared the section with the repeat condition, writing the query as below produces the following results on PostgreSQL: couchdb.doc #>> ‘{fields,lab}’::text AS lab, couchdb.doc #>> ‘{fields,lab1}’::text AS lab1, couchdb.doc #>> ‘{fields,choice_lab1}’::text AS choice_lab1, couchdb.doc…

@michael , thank you. I went through the documentation of jsonb_array_elements() and ran the query, which worked with minor changes, we now have all the repeat lab tests. I picked some UUIDs and checked with Fauxton, we are now good. [Repeat condition_CouchDB Results]

@michael , @mrjones , I noted after creating materialized views with indexes, from where we created a view joining data from the clinicians and CIF, the downloaded data in CSV format didn’t include all submitted cases from the CHT App. I did a manual refresh of the views, and even created a function …

I went through the documentation of jsonb_array_elements()… we are now good. Yay! I’m glad Michael’s tip was able to unbock you. I did a manual refresh of the views, and even created a function and trigger to refresh the materialized views I think you’re asking about keeping materialized fres…

Refreshing materialized views doesn’t bring any new data from CouchDB to PostgreSQL, it only refreshes those materialized views based on whatever data was already in PostgreSQL. Also worth mentioning… if you have materialized views that query other materialized views, the order in which you refresh…

@michael , so we have two materialized views and one view, which picks fields from the two materialized views using an inner join. What would be the best strategy of ensuring that we sync data from CouchDB to PostGres as it comes?

@oyierphil … great, if you have a view that is querying two materialized views, then it should be fine. couch2pg has a configurable refresh rate, which is an environment variable called COUCH2PG_SLEEP_MINS and defaults to 60 minutes. This means that it will wake up every 60 minutes to refresh data…

We recently got CHT Sync to a production ready state and we are working on documentation which should be ready in the next 2 weeks or so, I will ping you when it is ready. Meanwhile, please explore the resources shared by @mrjones .

@michael , I have run the query after at the end of each day, and got the same data size, with recent cases from the CHT App missing in PostgreSQL, indicating Couch2pg doesn’t refresh as it should be Exploring the reason why. Some sites are done data collection and we are under pressure to download …

Community Health Toolkit

Pulling Repeat group data from CouchDb to PostGres

Technical Support

show post in topic

Home
Categories
Guidelines
Terms of Service
Privacy Policy

Powered by Discourse, best viewed with JavaScript enabled