Since this json is already in a SQL column, you don't need ADF to break it down to parts. You can use JSON functions in SQL server to do that.

example of few first columns:

declare @json varchar(max) = '{
    "drugs": {
        "Codeine": {
            "bin": "Y",
            "name": "Codeine",
            "icons": [
                93,
                103
            ],

            "drug_id": 36,
            "pathway": {
                "code": "prodrug",
                "text": "is **inactive**, its metabolites are active."
            },

            "targets": [],
            "rxnorm_id": "2670",
            "priclasses": [
                "Analgesic/Anesthesiology"
            ],
            "references": [
                1,
                16,
                17,
                100
            ],

            "subclasses": [
                "Analgesic agent",
                "Antitussive agent",
                "Opioid agonist",
                "Phenanthrene "
            ],

            "metabolizers": [
                "CYP2D6"
            ],

            "phenotype_ids": {
                "metabolic": "5"
            },
            "relevant_genes": [
                "CYP2D6"
            ],
            "dosing_guidelines": [
                {
                    "text": "Reduced morphine formation. Use label recommended age- or weight-specific dosing. If no response, consider alternative analgesics such as morphine or a non-opioid.",
                    "source": "CPIC",
                    "guidelines_id": 1
                },
                {
                    "text": "Analgesia: select alternative drug (e.g., acetaminophen, NSAID, morphine-not tramadol or oxycodone) or be alert to symptoms of insufficient pain relief.",
                    "source": "DPWG",
                    "guidelines_id": 362
                }
            ],
            "drug_report_notes": [
                {
                    "text": "Predicted codeine metabolism is reduced.",
                    "icons_id": 58,
                    "sort_key": 58,
                    "references_id": null
                },
                {
                    "text": "Genotype suggests a possible decrease in exposure to the active metabolite(s) of codeine.",
                    "icons_id": 93,
                    "sort_key": 56,
                    "references_id": null
                },
                {
                    "text": "Professional guidelines exist for the use of codeine in patients with this genotype and/or phenotype.",
                    "icons_id": 103,
                    "sort_key": 50,
                    "references_id": null
                }
            ]
        }
    }
}

select JSON_VALUE(JSON_QUERY(@json,'$.drugs.Codeine'),'$.bin') as bin,
       JSON_VALUE(JSON_QUERY(@json,'$.drugs.Codeine'),'$.name') as name,
       JSON_VALUE(JSON_QUERY(@json,'$.drugs.Codeine'),'$.drug_id') as drug_id,
       JSON_VALUE(JSON_QUERY(@json,'$.drugs.Codeine'),'$.icons[0]') as icon_1
'

You need to decide how to handle arrays, such as icons, where there are multiple values inside the same element.

References:

JSON_QUERY function

JSON_VALUE function