Extend API to retrieve plain JSON

1. What is the general goal of the feature?
Enhance the API of ODK central to retrieve submissions in plain JSON format, including repetitions (No OData). This could be achieved with a file extion ".json".

2. What are some example use cases for this feature?
We are using python, which has no active library which supports OData v4 (seems to be quite complex to implement as seen here: Add support for V4 · Issue #39 · SAP/python-pyodata · GitHub)
CSV is not suitable for parsing groups/repetitions...

3. What can you contribute to making this feature a reality?
Currently, we are thinking about extending the ODK central API but would like to ask first if you want to have this. Maybe you've decided explicitly against JSON to prefer OData?

Hi @mattelacchiato!

The OData endpoint for submission data should already return a JSON response, for example, something like:

{
  "@odata.context": "https://your.odk.server/v1/projects/7/forms/simple.svc/$metadata#Submissions",
  "value": [
    {
      "__id": "uuid:85cb9aff-005e-4edd-9739-dc9c1a829c44",
      "age": 25,
      "meta": {
        "instanceID": "uuid:85cb9aff-005e-4edd-9739-dc9c1a829c44"
      },
      "name": "Bob"
    },
    {
      "__id": "uuid:297000fd-8eb2-4232-8863-d25f82521b87",
      "age": 30,
      "meta": {
        "instanceID": "uuid:297000fd-8eb2-4232-8863-d25f82521b87"
      },
      "name": "Alice"
    }
  ]
}

Is there a different format that you'd expect for the JSON response? For example, including the data from repeat groups?

Exactly! Sorry for the confusion...

I could definitely see how that would be useful. Probably the way for us to add that functionality would be to support the OData $expand query parameter. We have a GitHub issue for that, but no timeline at the moment. CC also @issa, who's thought a lot about our OData functionality.

2 Likes

For using $expand, we would have to now all tables that should get expanded. This is something we would like to avoid and "just dump all data".

I've had a look into the database and saw that the submission_defs are stored as XML containing the repetitions inside. I guess, an implementation could just stream the submission_defs XML and convert it to JSON, right?

That makes sense, and actually I think OData supports $expand=* to expand all tables. You can find some of the implementation for the OData endpoint in lib/data/json.js. That file parses the submission XML and formats the data values. I think the next step would probably be to see what changes would be needed to lib/data/json.js to support $expand. It might also be worth considering just adding support for $expand=* for the root table if that would be significantly easier than a more general implementation of $expand.

2 Likes

Thanks, I'll have a look.

If I understand the documentation correctly, further development question should be handled in Slack. How can I get access there? I don't find a button to register, only to log in.

1 Like

Awesome! You can sign up for the Slack at https://slack.getodk.org/ (I'll update the link in the contributing guide). You can find us in the #central-code channel there!

1 Like

Hi @mattelacchiato and @Matthew_White,

Will it be possible to filter on submission Date when $expand will be use ?

Yes, of course. We are using both functionality together. Do you have any indication why this shouldn't work?

1 Like

Great. No indication except the filter on SubmissionDate only works on Submissions table.
It will help a lot to get data from nested table without downloading all of it.