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.

  • I'm afraid this is too much of a general programming question to have a proper home on this website. But if your goal is to just find records with 'retinol', I'd suggest NOT parsing them. Just take the response as one big string and look for 'retinol' anywhere in there, and then if you find it, process it. Commented Mar 9, 2015 at 18:02
  • Hi @ksf, I marked this as off-topic but I think if you reshape your question to focus on the initial "what is the best way to look for substances that could appear in a variety of OpenFDA fields?" then I think it's more appropriate.
    – philshem
    Commented Mar 10, 2015 at 9:24
  • Will flag the OpenFDA team to answer directly here. Commented Mar 11, 2015 at 11:43

1 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:




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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.