Split FDA strengths into their own rows

[jrlegrand]

Problem Statement

FDA has better human-understandable strengths than RxNorm at an NDC level.

example:
FDA infant's ibuprofen = 50 mg / 1.25 mL
RxNorm for same thing = 40 mg / 1 mL

Criteria for Success

Split the 3 columns below by semicolon and put them in their own table(s) that can link back to the original NDC product/package. Ideally, it would be one table with a column for product_id, substance_name, strength_numerator, strength_denominator - as opposed to like 3 separate tables for all that info. But not sure what is technically possible with unnest.

Additional Information

Should be similar to the fda_class work.

[jrlegrand]

I think this works... putting here for @Bridg109 to review...

SELECT
	substance.productid
	, substance.rn AS line
	, substance.token AS substancename
	, strength.token AS active_numerator_strength
	, unit.token AS active_ingred_unit
FROM (
	SELECT DISTINCT
		prod.productid
		, ROW_NUMBER() OVER (PARTITION BY prod.productid) AS rn
		, arr.token
	FROM datasource.fda_ndc_product prod
	, UNNEST(string_to_array(prod.substancename, '; ')) arr(token)
) substance
LEFT JOIN (
	SELECT DISTINCT
		prod.productid
		, ROW_NUMBER() OVER (PARTITION BY prod.productid) AS rn
		, arr.token
	FROM datasource.fda_ndc_product prod
	, UNNEST(string_to_array(prod.active_numerator_strength, '; ')) arr(token)
) strength ON substance.productid = strength.productid AND substance.rn = strength.rn
LEFT JOIN (
	SELECT DISTINCT
		prod.productid
		, ROW_NUMBER() OVER (PARTITION BY prod.productid) AS rn
		, arr.token
	FROM datasource.fda_ndc_product prod
	, UNNEST(string_to_array(prod.active_ingred_unit, '; ')) arr(token)
) unit ON substance.productid = unit.productid AND substance.rn = unit.rn;

[Bridg109]

You can unnest() multiple arrays in parellel