Pulling Repeat group data from CouchDb to PostGres

Technical Support
4

@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 separate view for your repeat section that has different columns for all the fields in the repeat.

So in your example, you might have one view for the report itself, then a second view for the repeat section, and that second view would have three columns… a uuid column that links back to the report view, and columns for choice_lab1 and dur_012

It’s been a while since I’ve worked with these, but to get you started, try selecting something like…

SELECT
	doc->>'_id' AS uuid,
	jsonb_array_elements(doc#>'{fields,repeat2}')->'choice_lab1' AS lab,
	jsonb_array_elements(doc#>'{fields,repeat2}')->'dur_012' AS dur
	

FROM
	couchdb
	
WHERE
	doc->>'_id' = '<example_doc_id>'
1 Like