Format and Reporting of Data in Postgres Populated from couch2pg

Hi
We managed to replicate the data from couchDb to Postgres using couch2pg, the format of the data is different from data exported from medic portal report, thus difficult to analyze at the moment. We already have data from the case investigation form, which is organized into columns with questions and rows with the data values. We expect data from couch2pg to have both the contact registration data from the contact registration form and case investigation data from the case investigation form.

The couchdb table has one column, doc. Trying to figure out how to convert the data into CSV with clear columns similar to data exported from medic portal report. I have tried reading the file into Python but getting reading errors, will appreciate any insights

couch2p Migration Errors_3

couch2p Migration Errors

Great progress @oyierphil!

The data in the couchdb table is organized in rows of JSON documents. This includes all documents in the medic database in CouchDB. Fields within the documents can be queried using postgres’ JSON operators detailed here.

Views are convenient for accessing form data, where a view for each form type can be defined as

CREATE VIEW formview_myform AS 
(
	SELECT
		doc#>>'{fields,subfield1}' AS field1,
		doc#>>'{contact,subfield2,subsubfield1}' AS field2,
		<other fields>
		to_timestamp((NULLIF(doc->>'reported_date', '')::bigint / 1000)::double precision) AS reported_date
	FROM 
		couchdb	
	WHERE 
		doc->>'form' = 'myform'
);

Data from the view can be queried as

SELECT * FROM formview_myform

Contact data can be found in a default view called contact_metadata, See the view definition here.

To export CSV data from postgres, use this query:

COPY (

	SELECT * FROM formview_myform
	
) TO '/tmp/myform.csv' WITH (FORMAT CSV, HEADER);

@elijah, I tried to read the json file into Python and convert into CSV and got into an error.
I have described the table couchDb and I see the Type as jsonb with column doc, will have to figure out the columns before writing the views as above, some homework for me today…