FHIR Chat · Concepts from RxNorm and terminology powered search · terminology

sqlite3 rxnorm.db  "with
by_tty as (
    select rxcui, tty, json_group_array(str) arr
    from rxnconso where SAB='RXNORM' group by rxcui, tty),
by_conso as (
    select rxcui, json_group_object(tty, json(arr)) as terms
    from by_tty group by rxcui),
by_atn as (
    select rxcui, atn, json_group_array(atv) arr
    from rxnsat where sab='RXNORM' group by rxcui, atn),
by_attr as (
    select rxcui, json_group_object(atn, json(arr)) as attrs
    from by_atn group by rxcui),
by_rela as (
    select rxcui2 as rxcui, rela, json_group_array(rxcui1) arr
    from rxnrel where sab='RXNORM' and stype1='CUI' group by rxcui, rela),
by_rels as (
    select rxcui, json_group_object(rela, json(arr)) as rels
    from by_rela group by rxcui)
select json_object(
    'cui', c.rxcui,
    'terms', json(terms),
    'attrs', json(coalesce(attrs, '{}')),
    'rels', json(coalesce(rels, '{}')))
from by_conso c
    left join by_attr a on c.rxcui=a.rxcui
    left join by_rels r on c.rxcui=r.rxcui
" | gzip > rxnorm.ndjson.gz

$ jq --slurp  '
    .[] |
    select(.terms.SCD[]?|test("loratadine")) |
    select(.attrs.RXTERM_FORM[]?=="Cap")
' <(zcat rxnorm.ndjson.gz )

{
  "cui": "828269",
  "terms": {
    "PSN": [
      "loratadine 10 MG 24HR Oral Capsule"
    ],
    "SCD": [
      "loratadine 10 MG Oral Capsule"
    ],
    "SY": [
      "loratadine 10 MG 24HR Oral Capsule"
    ]
  },
  "attrs": {
    "NDC": [
      "11673098607",
      "11673098630",
      "11822044600",
      "11822202203",
      "21130019502",
      "25000002108",
      "25000002113",
      "25000002121",
      "25000002134",
      "25000002136",
      "25000002146",
      "36800075539",
      "36800075552",
      "41226068630",
      "49483068607",
      "49483068630",
      "49738068607",
      "49738068630",
      "50804084010",
      "53943011613",
      "55910062939",
      "55910062952",
      "59556088201",
      "59556088281",
      "63868066910",
      "68016005510",
      "69452021103",
      "69452021107",
      "69452021116",
      "69452021126",
      "69452028317",
      "69452028319",
      "69452028325",
      "69842068607",
      "69842068630",
      "69842068660",
      "70000021701",
      "72288044639",
      "79481044600"
    ],
    "RXN_AI": [
      "{316167} 28889"
    ],
    "RXN_AM": [
      "{316167} 28889"
    ],
    "RXN_AVAILABLE_STRENGTH": [
      "10 MG"
    ],
    "RXN_BOSS_FROM": [
      "{316167} AI"
    ],
    "RXN_HUMAN_DRUG": [
      "US"
    ],
    "RXTERM_FORM": [
      "Cap"
    ]
  },
  "rels": {
    "consists_of": [
      "316167"
    ],
    "has_dose_form": [
      "316965"
    ],
    "has_tradename": [
      "836338"
    ],
    "isa": [
      "828268",
      "1165336",
      "1165337"
    ]
  }
}

$ jq -c  --slurp  '
    .[]
    | select(.designation[] | select(.use.code == "SBD"))
    | select(.property[] | select(.code=="RXTERM_FORM" and .valueString=="Gel"))
' <(zcat rxnorm.ndjson.gz )

{
  "code": "998437",
  "display": "miconazole nitrate 2 % Topical Gel",
  "designation": [
    {
      "use": {
        "code": "PSN"
      },
      "value": "miconazole nitrate 2 % Topical Gel"
    },
    {
      "use": {
        "code": "SCD"
      },
      "value": "miconazole nitrate 0.02 MG/MG Topical Gel"
    },
    {
      "use": {
        "code": "SY"
      },
      "value": "miconazole nitrate 2 % Topical Gel"
    }
  ],
  "property": [
    {
      "code": "RXN_AI",
      "valueString": "{998427} 42790"
    },
    {
      "code": "RXN_AM",
      "valueString": "{998427} 6932"
    },
    {
      "code": "RXN_AVAILABLE_STRENGTH",
      "valueString": "0.02 MG/MG"
    },
    {
      "code": "RXN_BOSS_FROM",
      "valueString": "{998427} AI"
    },
    {
      "code": "RXN_HUMAN_DRUG",
      "valueString": "US"
    },
    {
      "code": "RXTERM_FORM",
      "valueString": "Gel"
    },
    {
      "code": "consists_of",
      "valueCode": "998427"
    },
    {
      "code": "has_dose_form",
      "valueCode": "346286"
    },
    {
      "code": "isa",
      "valueCode": "1164112"
    },
    {
      "code": "isa",
      "valueCode": "377981"
    },
    {
      "code": "tty",
      "valueString": "PSN"
    },
    {
      "code": "tty",
      "valueString": "SCD"
    },
    {
      "code": "tty",
      "valueString": "SY"
    }
  ]
}

sqlite3 rxnorm.db  "
with
by_designation as (
    select rxcui, json_object('use', json_object('code', tty), 'value', str) designation, (case
        when tty = 'PSN' then 1
        when tty in ('SY', 'TMSY', 'DF', 'ET', 'DFG') then -1
        else 0
        end) priority
    from rxnconso where SAB='RXNORM' order by priority desc),
by_attr as (
    select rxcui, json_object('code', atn, 'valueString', atv) attribute
    from rxnsat where sab='RXNORM'),
by_rela as (
    select rxcui2 as rxcui, json_object('code', rela, 'valueCode', rxcui1) relation
    from rxnrel where sab='RXNORM' and stype1='CUI'),
by_prop as (
    select rxcui, json_object('code', 'TTY', 'valueString', designation->>'use.code') as property from by_designation
    UNION
    select rxcui, attribute as property from by_attr
    UNION
    select rxcui, relation as property from by_rela
),
output_pass_1 as (
    select json_object(
        'code', c.rxcui,
        'display', c.designation->>'value',
        'designation', json_group_array(json(c.designation))) as concept
    from by_designation c
    group by c.rxcui),
output_pass_2 as (
    select json_object(
        'code', prev.concept->>'code',
        'display', prev.concept->'display',
        'designation', prev.concept->'designation',
        'property', json_group_array(json(by_prop.property))) as concept
    from output_pass_1 prev
    left join by_prop on prev.concept->>'code' = by_prop.rxcui
    group by prev.concept->>'code'
    )
select json_object(
  'resourceType', 'CodeSystem',
  'id', 'rxnorm',
  'url', 'http://www.nlm.nih.gov/research/umls/rxnorm',
  'version', '20220703',
  'name', 'RxNorm',
  'title', 'RxNorm',
  'status', 'draft',
  'experimental', 'true',
  'valueSet', 'http://www.nlm.nih.gov/research/umls/rxnorm/vs',
  'content', 'complete',
  'property', json_array(
    json('{\"code\": \"NDC\", \"type\": \"string\"}'),
    json('{\"code\": \"ORIG_CODE\", \"type\": \"string\"}'),
    json('{\"code\": \"ORIG_SOURCE\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_ACTIVATED\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_AI\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_AM\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_AVAILABLE_STRENGTH\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_BN_CARDINALITY\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_BOSS_FROM\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_BOSS_STRENGTH_DENOM_UNIT\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_BOSS_STRENGTH_DENOM_VALUE\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_BOSS_STRENGTH_NUM_UNIT\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_BOSS_STRENGTH_NUM_VALUE\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_HUMAN_DRUG\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_IN_EXPRESSED_FLAG\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_OBSOLETED\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_QUALITATIVE_DISTINCTION\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_QUANTITY\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_STRENGTH\", \"type\": \"string\"}'),
    json('{\"code\": \"RXN_VET_DRUG\", \"type\": \"string\"}'),
    json('{\"code\": \"RXTERM_FORM\", \"type\": \"string\"}'),
    json('{\"code\": \"TTY\", \"type\": \"string\"}'),
    json('{\"code\": \"consists_of\", \"type\": \"code\"}'),
    json('{\"code\": \"constitutes\", \"type\": \"code\"}'),
    json('{\"code\": \"contained_in\", \"type\": \"code\"}'),
    json('{\"code\": \"contains\", \"type\": \"code\"}'),
    json('{\"code\": \"dose_form_of\", \"type\": \"code\"}'),
    json('{\"code\": \"doseformgroup_of\", \"type\": \"code\"}'),
    json('{\"code\": \"form_of\", \"type\": \"code\"}'),
    json('{\"code\": \"has_dose_form\", \"type\": \"code\"}'),
    json('{\"code\": \"has_doseformgroup\", \"type\": \"code\"}'),
    json('{\"code\": \"has_form\", \"type\": \"code\"}'),
    json('{\"code\": \"has_ingredient\", \"type\": \"code\"}'),
    json('{\"code\": \"has_ingredients\", \"type\": \"code\"}'),
    json('{\"code\": \"has_part\", \"type\": \"code\"}'),
    json('{\"code\": \"has_precise_ingredient\", \"type\": \"code\"}'),
    json('{\"code\": \"has_quantified_form\", \"type\": \"code\"}'),
    json('{\"code\": \"has_tradename\", \"type\": \"code\"}'),
    json('{\"code\": \"ingredient_of\", \"type\": \"code\"}'),
    json('{\"code\": \"ingredients_of\", \"type\": \"code\"}'),
    json('{\"code\": \"part_of\", \"type\": \"code\"}'),
    json('{\"code\": \"precise_ingredient_of\", \"type\": \"code\"}'),
    json('{\"code\": \"quantified_form_of\", \"type\": \"code\"}'),
    json('{\"code\": \"reformulated_to\", \"type\": \"code\"}'),
    json('{\"code\": \"reformulation_of\", \"type\": \"code\"}'),
    json('{\"code\": \"tradename_of\", \"type\": \"code\"}')
  ),
  'concept', json_group_array(concept)
) from output_pass_2
" \
  | sed 's/"inverse_isa"/"child"/g' \
  | sed 's/"isa"/"parent"/g' \
  | gzip > rxnorm.json.gz

{
  "resourceType": "CodeSystem",
  "url": "http://www.nlm.nih.gov/research/umls/rxnorm",
  "identifier": [
    {
      "system": "urn:ietf:rfc:3986",
      "value": "urn:oid:2.16.840.1.113883.4.642.4.343"
    }
  ],
  "version": "full_prescribe_03072022",
  "name": "RxNormCurrentPrescribableContent",
  "title": "RxNorm Current Prescribable Content",
  "status": "draft",
  "experimental": true,
  "publisher": "U.S. National Library of Medicine",
  "valueSet": "http://www.nlm.nih.gov/research/umls/rxnorm/vs",
  "description": "RxNorm provides normalized names for clinical drugs and links its names to many of the drug vocabularies commonly used in pharmacy management and drug interaction software",
  "copyright": "Attribution and Rights for Government Works:  Works produced by the U.S. government are not subject to copyright protection in the United States. Any such works found on National Library of Medicine (NLM) Web sites may be freely used or reproduced without permission in the U.S. Please acknowledge NLM as the source of the information by including the phrase “Courtesy of the U.S. National Library of Medicine” or “Source: U.S. National Library of Medicine.”",
  "caseSensitive": true,
  "content": "complete",
  "extension": [{
      "url": "https://tx.fhir.me/concepts.ndjson.gz",
      "valueUrl": "ipfs://QmdcesSyDm12aSjfg1fFR28Yhfi6yC6nRxcNJkazU2zN5r/concepts/rxnorm/full_prescribe_03072022.ndjson.gz"
  }]
}