Totally Out of My Depth - ODK Central, Redash and OData/JSON

1

Hi, first post. I’m testing an ODK form for gathering weather observations and want to at least start playing with the data I’m gathering. Someone on the ODK forum had success with Redash and so have I, to some degree but I’m stuck with the query.

For example, for a URL query I get the following (this is a snippet):

[{"__id":"uuid:a73ebe56-3b12-43e5-ab96-f107f6f24e9d","datecompleted":"2022-02-16","username":null,"introtext":null,"temprecord":{"tempinstructions":null,"morningtemp":8,"noontemp":4,"enddaytemp":8},

I can then pull out values for specific fields by using:

fields: [datecompleted]
path: value

This returns a table with a list of dates. Great.

Thing is, ODK uses something called ‘groups’ which organises surveys clearly and makes survey forms more user friendly. Reading the raw OData stream from ODK the fields inside groups are nested (see below, ‘temprecord’ field). Is this an array?

[{"__id":"uuid:a73ebe56-3b12-43e5-ab96-f107f6f24e9d","datecompleted":"2022-02-16","username":null,"introtext":null,"temprecord":{"tempinstructions":null,"morningtemp":8,"noontemp":4,"enddaytemp":8},

So, how the heck do I get the values out for those fields?! As per the title, totally out of my depth and funnily enough I’m usually pretty good with my key words for searching for help but in this instance I’m drawing a blank.

Any help would be greatly appreciated - at least somewhere where some basic JSON queries are documented for me to learn!

Thanks.

2

Hi @mrodk

it looks like we both enjoy same tools !

I tried to directly plug redash over ODK Central’s API as described here :

and asked a question here :

In the end, since I have complex forms with repeat groups and “sub-tables”, and because I need the data in other tools like QGIS, I developed a set of function to get Central’s data into a dedicated PostgreSQL database or schema (PostgreSQL is my daily tool) :

Hope that helps…

3

Thanks - looks great, but why do I need it? I can do the basic restitution like in your post which gets all data from the tables, so what can your script/having the data in a second database do that I can’t do with just the odata feed?

As per title - out of my depth!

Thanks again.

4

Not sure you need it !
But if you are more comfortable WITH sql than json queries it could help.
The reason why I developed those functions is I wanted to keep my SQL routines, view s and redash Dashboards running well after I migrate from Aggegate to Central.

You will probably be able to perform a dashboard, even with repeat groups.
One json query per Central table, and a QRDS to join the tables and show the datas in the dashboard.

Please share your work on ODK’s forum too ! It would help a lot of people looking to connect a modern opensource dashboard tool to Central.

5

Aaah got it thanks.

Can honestly say I’m not comfortable in either… Totally new to writing any form of query.

Don’t even know what a QRDS is but I’m sure I’ll find out.

Will definitely share any achievement but I think that may be a while away now!

Thanks again though - some great pointers.

6

How complex is your ODK form ?

It is a “Query Result Data Source” in redash. Yous can then use the result of your OData request as a datasource and perform SQL queries on it.

1 Like