Unable to see all data in postgres db form_field_values table

Support
,
1

Hi Support Team,

I have deployed ODK Central on AWS Ubuntu machine and uploaded one survey form. I was trying access the data from form_field_values table. Here, I am not able to find all data related to one submission from that table but in csv download using odk central portal I can see all data. What could be the reason? And why all data is not appearing inside the table. By default 37 tables has been created in ODK database.

Please guide.

Thanks.

2

  1. Just curious - why are you trying to access ODK Central's Postgres database directly? I'd recommend avoiding this approach if possible, since the internal schema could change with future updates. A more stable and supported way to manage or retrieve data is through the ODK Central's front-end or its REST API (especially if you are developing something, explore pyODK as well) or maybe OData connection.

  2. You won't find submission data in the form_field_values model / table - that table probably stores form choices for all forms across the server. Instead, submission data is stored in the submission_defs table.

  3. Within the submission_defs table, you'll probably need to filter using fields like formDefId and current (maybe more) to get the exact data you're looking for. Unlike ODK Aggregate, ODK Central no longer creates separate tables per form or project. Also, note that the actual submission data is stored as XML (type: text) within a column, rather than as discrete row fields in the table.

Given all that, I'm still wondering - do you really wish to go down with this hard & unstable route to access the data? ODK already offers features / tools like the REST API and OData connections that are much easier to work with (be it for anything) and less prone to breaking with updates.

2 Likes
3

I can't agree more. And even if you do, querying xml fields in SQL is not that fun.
See our own experience here : SQL first try to get Central data into internal PostGIS database

If you want to use SQL to query your data, as I love to, you can check this ODATA integration within pl-python functions : Pl-pyodk : use of pyODK with PL/Python PostgreSQL functions to pull data from Central into your own database

2 Likes