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);