Struggling with ODK to PostGres mapping

Job Writing
1

I’ve got a simple ODK form with 1 repeat group, and am trying to map this to PostGres with little success.
Please could you have a look and see if you can pick up where I am going wrong?

Input (from ODK via JSON publisher):

{ "token": "", "formVersion": "", "formId": "phenology001", "data": [ { "today": "2019-08-26", "start": "2019-08-26T21:09:48.420Z", "observation": [ { "stage": "1", "picture": { "url": "https://<mypictureurl>", "type": "image/jpeg", "filename": "1566853816709.jpg" }, "notes": "Test note" }, { "stage": "2", "picture": { "url": "https://", "type": "image/jpeg", "filename": "1566853849716.jpg" }, "notes": "Test note 2" } ], "name": "Andrew", "location:Longitude": null, "location:Latitude": null, "location:Altitude": null, "location:Accuracy": null, "instanceID": "uuid:f38ecef5-117a-44b3-a443-7d7d3b1ad3a6", "imei": "867440036085345", "end": "2019-08-26T21:11:03.306Z", "date": "2019-08-26", "*meta-ui-version*": null, "*meta-submission-date*": "2019-08-26T21:11:21.838Z", "*meta-model-version*": null, "*meta-is-complete*": true, "*meta-instance-id*": "uuid:f38ecef5-117a-44b3-a443-7d7d3b1ad3a6", "*meta-date-marked-as-complete*": "2019-08-26T21:11:21.838Z" } ], "content": "record", "__query_params": {} }

Job:

each( dataPath("data[*]"), combine( sql( function(state) { return (INSERT INTO log_entry (odk_instance_id, logger_name) VALUES (’`

  • dataValue(“meta-instance-id”)(state) + ', '
  • dataValue(“name”)(state) + ');
    )}) ,

each(
dataPath(“observation[*]”),
sql( function(state) { return (
INSERT INTO log_entry_observation

  • (odk_instance_id, stage, notes) VALUES ('
  • 123 + ',
  • dataValue(“stage”)(state) + , '
  • dataValue(“notes”)(state) + ')
    )})
    )
    )
    );
    `

Job run output:

Executing SQL statement: INSERT INTO log_entry (odk_instance_id, logger_name) VALUES ('uuid:f38ecef5-117a-44b3-a443-7d7d3b1ad3a6', 'Andrew'); Executing SQL statement: INSERT INTO log_entry_observation (odk_instance_id, stage, notes) VALUES ('123', 1, 'Test note') Finished.

2 problems:

  1. Why is there only 1 log_entry_observation insert statement executed?

I expected the second each to trigger twice for the 2 repeats submitted

  1. The result of the job run was no change to my Postgres database (no rows inserted in either table).

Copy-pasting these statements and running them against the DB created both records successfully, so the SQL is correct.
Also, an initial simple job ignoring the repeats sucessfully inserted a row in thelog_entry table, so I know the credentials are setup correctly.
Any ideas?

Is there any issue with using combine and sql together?

  1. Bonus question :o) How do I access/create a unique id for the repeat-group records? e.g. something like <parent_uuid>/observation[1]

Thanks!
Andrew

2

Hi Andrew,

I’ve mocked up a postgres DB with those two tables, and this job is acheiving the desired output:

each(
dataPath(‘data[*]’),
sql(state => {
return (
INSERT INTO log_entry (odk_instance_id, logger_name) VALUES (' +
state.data[‘meta-instance-id’] +
', ' +
state.data.name +
');
);
})
);

each(
dataPath(‘observation[*]’),
sql(state => {
return (
INSERT INTO log_entry_observation +
(odk_instance_id, stage, notes) VALUES (' +
123 +
', +
state.data.stage +
, ' +
state.data.notes +
')
);
})
);

For your questions:
(1) Is this the same format you use with other language-packages? I don’t see people use each(path, combine(operation(…), each(path, operation(…))); very often.

(2) See job above and the documentation for language-common (https://github.com/OpenFn/language-common/blob/master/src/index.js#L247-L270) to understand combine. Maybe it’s not behaving how you expect?
(3) How about using alterState before your other operations. You could iterate through that array of observations, assigning a new key to each object with the parent ID number, concatenated with something. (A row count? Not ideal, but I’ve seen clients in the past do that to output: “abc123-1”, “abc123-2”, “abc123-3”. Maybe there’s something truly unique about each record?)

3

Hi Taylor,

Thanks, managed to get the INSERTs running modelled on your suggestion.
Also eventually managed to get the alterState going - posting here below for anyone else wanting to do something similar.

Question: Does data always just have a single object, or can it be an array? (Particularly when receiving from ODK Aggregate)
Reason I ask is that

`

each(
dataPath(‘data[*]’),

`
seems to suggest an array, and I would then need to tweak alterState for that eventuality.

4

Hey Andrew, great! And good question.

When OpenFn.org spins up a Node.js runtime to execute your job, we pass in state. State is a JSON object and contains, at least, two keys that are, themselves, objects. It looks like this:

{
"configuration": { ... },
"data": { ... }
}

“configuration” contains login and authentication information from an associated “credential”, and “data” contains the body of the associated message (which triggered the run) if applicable. When runs are triggered by a timer they inherit the final state of the last run, when they are triggered by another run, they get the final state of that triggering run.

The tricky bit here is that ODK sends a JSON payload to OpenFn which itself includes a “data” key. And that data key, no matter whether the payload contains 1 or 1,000 form submissions, is always an array! This has nothing to do with OpenFn, but it’s the format of ODK’s outbound message.

So, in order to do anything with ODK data you need to iterate over that data array, which now lives at state.data.data. If it’s a single submission, you could access the submission data via state.data.data[0].something. In your case, you’re performing some operation (executing an SQL query) for each item in the state.data.data array as “dataPath(‘data[*]’)” is shorthand for that path.

Using each here, and EACHing over the state.data.data array is just because that’s the shape of the ODK Aggregate data, but not part of how OpenFn sets up your Node environment, per se.