Variable/multidimensional json record structure OpenFDA

Question

Arguably this is more a general programming question, and I should keep learning; yet, if advice or guidance is forthcoming I wanted to try asking: what is the best way to look for substances that could appear in a variety of OpenFDA fields?

Using PHP, I can extensively construct a variety of query patterns and cash json/text files of records for regex parsing for specific items in specific fields, but am having trouble facing the variable record format of responses to trim out api responses (or parts of responses like npc codes) as they happen (are received) to reduce time/disk space/etc.

I am digging a little deeper into Python, but I suspect all that will give me is more sophisticated tools for working with cached files in a directory (which may not be anything to sneeze at). Not able to work with BI tools at the moment but I did start to look at open source options here, but that seems overkill for the data "discovery" tasks I have yet to do/try.

My apologies is this has been addressed in elasticsearch or other introductory kinds of queries (questions/3244/ or questions/3321/), but nothing seemed to address a specific strategy for the variable record structure issue on the receiving end (me) -- maybe that means my approach is right and my skills (and tools?) have to catch up...or maybe a quick look at this from you experts will quickly lead you to say "try this instead..."

At risk of droning on with only a vague example: Looking for Retinol (in drug/events for now) which could come up in any number of name fields it seems I'd have to get & parse ~1900 files/records/safetyreportids and THEN process the possibilities from the cache. Is that the "right" idea?

Thanks to all.

Answer 1

For any of the /drug endpoints, I would use the opendfda.substance_name.exact annotated field. It will not be on all records, it is matched in a variety of ways against each dataset, so we took an approach of being conservative in the matching for the initial phases of this project; however, when it is there, it will have all sorts of wonderful information.

In the case /drug/event.json, the openfda record is under patient.drug.openfda.substance_name.exact. If this fields is not fully populated (which is might not be at this point), you can always fall back on the patient.drug.medicinalproduct.exact field (which is part of the original adverse event submission). An example would be:

https://api.fda.gov/drug/event.json?search=patient.drug.medicinalproduct.exact:RETINOL

or

https://api.fda.gov/drug/event.json?search=patient.drug.openfda.substance_name.exact:RETINOL

As you can see, there are only two hits against the openfda.substance_name field in this case, which is a good sign that there is not good coverage in the annotation process, which means that you stick with the medicinalproduct field in this case.

A note about 'exact'. In this case, it is best to use because substance names can have multiple words in them (exact does phrase style searching); however, exact also is case-sensitive and most substance names in the SPL are uppercase.