I have a JSON type SQL column in SQL table as below example. I want the below code to be converted into separate columns such as drugs as table name and other attribute as column name, how can I use adf or any other means please guide. The below code is a example of table called report where I need to convert this into separate columns .
{
"drugs": {
"Codeine": {
"bin": "Y",
"name": "Codeine",
"icons": [
93,
100,
103
],
"drug_id": 36,
"pathway": {
"code": "prodrug",
"text": "is **inactive**, its metabolites are active."
},
"targets": [
"OPRM1"
],
"rxnorm_id": "2670",
"priclasses": [
"Analgesic/Anesthesiology"
],
"references": [
1,
2,
9,
17,
29,
30,
159,
171
],
"subclasses": [
"Analgesic agent",
"Antitussive agent",
"Opioid agonist",
"Phenanthrene "
],
"metabolizers": [
"CYP2D6"
],
"phenotype_ids": {
"OPRM1": "78",
"metabolic": "6"
},
"relevant_genes": [
"CYP2D6",
"OPRM1"
],
"dosing_guidelines": [
{
"text": "Normal to reduced morphine formation. Use label recommended age- or weight-specific dosing. If no response, may need to consider alternative analgesics such as morphine or a non-opioid.",
"source": "Genotype predicted",
"guidelines_id": 103
}
],
"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": "Decreased analgesic effects due to OPRM1 genotype.",
"icons_id": 100,
"sort_key": 52,
"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
}
]
},
"Dapsone": {
"bin": "X",
"name": "Dapsone",
"icons": [
99
],
"drug_id": 514,
"pathway": {
"code": "dualactive",
"text": "and its metabolites are **active**."
},
"targets": [],
"rxnorm_id": "3108",
"priclasses": [
"Infectious disease"
],
"references": [
1
],
"subclasses": [
"Miscellaneous antibiotic agent"
],
"metabolizers": [],
"phenotype_ids": {},
"relevant_genes": [],
"dosing_guidelines": [
{
"text": "Hemolysis and Heinz body formation may be exaggerated in individuals with a glucose-6-phosphate dehydrogenase (G6PD) deficiency, or methemoglobin reductase deficiency, or hemoglobin M. This reaction is frequently dose-related. Dapsone should be given with caution to these patients or if the patient is exposed to other agents or conditions such as infection or diabetic ketosis capable of producing hemolysis. Drugs or chemicals which have produced significant hemolysis in G6PD or methemoglobin reductase deficient patients include dapsone, sulfanilamide, nitrite, aniline, phenylhydrazine, napthalene, niridazole, nitro-furantoin and 8-amino-antimalarials such as primaquine. Toxic hepatitis and cholestatic jaundice have been reported early in therapy. Hyperbilirubinemia may occur more often in G6PD deficient patients. When feasible, baseline and subsequent monitoring of liver function is recommended; if abnormal, dapsone should be discontinued until the source of the abnormality is established.",
"source": "FDA - Additional testing",
"guidelines_id": 453
}
],
"drug_report_notes": [
{
"text": "According to FDA labeling, additional laboratory testing may be indicated.",
"icons_id": 99,
"sort_key": 51,
"references_id": null
}
]
},
"Digoxin": {
"bin": "B",
"name": "Digoxin",
"icons": [],
"drug_id": 47,
"pathway": {
"code": "nometab",
"text": "is not significantly metabolized, or not absorbed."
},
"targets": [],
"rxnorm_id": "3407",
"priclasses": [
"Cardiovascular"
],
"references": [
1
],
"subclasses": [
"Antiarrhythmic agent",
"Cardiac glycoside",
"Miscellaneous antiarrhythmic agent"
],
"metabolizers": [],
"phenotype_ids": {},
"relevant_genes": [],
"dosing_guidelines": [],
"drug_report_notes": [
{
"text": "All of digoxin's actions are mediated through its effects on Na-K ATPase. Up to 70% of the dose is excreted unchanged in the urine and only a small percentage (13%) of a dose of digoxin is metabolized in healthy volunteers. The metabolism of digoxin is not dependent upon the cytochrome P-450 system, and digoxin is not known to induce or inhibit the cytochrome P-450 system. Digoxin is a substrate for P-glycoprotein (P-gp). As an efflux protein on the apical membrane of enterocytes and of renal tubular cells, P-glycoprotein may limit the absorption and enhance the excretion of digoxin, respectively. Studies have suggested that variants of the ABCB1 gene (encoding P-gp) may influence digoxin serum levels; however, changes in digoxin exposure related to ABCB1 genotype are generally small (10-30%), account only for approximately 10% of the variability seen in digoxin pharmacokinetics, and are unlikely to be clinically significant.",
"icons_id": 60,
"sort_key": 1,
"references_id": null
}
]
},
"Doxepin": {
"bin": "Y",
"name": "Doxepin",
"icons": [
92,
103
],
"drug_id": 452,
"pathway": {
"code": "dualactive",
"text": "and its metabolites are **active**."
},
"targets": [],
"rxnorm_id": "3638",
"priclasses": [
"Psychiatry"
],
"references": [
1,
2,
50
],
"subclasses": [
"Antidepressant",
"Tricyclic antidepressant"
],
"metabolizers": [
"CYP2C19",
"CYP2D6"
],
"phenotype_ids": {
"metabolic": "5"
},
"relevant_genes": [
"CYP2C19",
"CYP2D6"
],
"dosing_guidelines": [
{
"text": "Certain drugs inhibit the activity of CYP2D6 and make normal metabolizers resemble poor metabolizers. An individual who is stable on a given dose of TCA may become abruptly toxic when given one of these inhibiting drugs as concomitant therapy. Concomitant use of tricyclic antidepressants with drugs that can inhibit cytochrome P450 2D6 may require lower doses than usually prescribed for either the tricyclic antidepressant or the other drug. Furthermore, whenever one of these other drugs is withdrawn from co-therapy, an increased dose of tricyclic antidepressant may be required. It is desirable to monitor TCA plasma levels whenever a TCA is going to be coadministered with another drug known to be an inhibitor of cytochrome P450 2D6.",
"source": "FDA",
"guidelines_id": 349
},
{
"text": "A 25% reduction of recommended starting dose may need to be considered. Utilize therapeutic drug monitoring to guide dose adjustments.",
"source": "Genotype predicted",
"guidelines_id": 66
}
],
"drug_report_notes": [
{
"text": "Genotype suggests a possible increase in exposure to doxepin.",
"icons_id": 92,
"sort_key": 57,
"references_id": null
},
{
"text": "Professional guidelines exist for the use of doxepin in patients with this genotype and/or phenotype.",
"icons_id": 103,
"sort_key": 50,
"references_id": null
}
]
The expected output i need is
Table name : drugs
bin
name
n so on for other values
3
Answers
You can use openjson to shred the document into smaller pieces:
Some of your properties are arrays etc, and those are a bit harder to put into single columns, so you got some work for you. You might wanna read about JSON_VALUE, JSON_QUERY to figure out what exactly you might need
This is a quick example of how to parse json and nested arrays. You just have to OUTER APPLY OPENJSON() on the nested array and define the columns with WITH
You can use zq and the Zed language to transform your data into a table format. Once it’s in a neat table you can easily insert it into a schema-defined table in a database. You will have to figure out what to do with the arrays though. Some dbs support arrays but others do not.
That will output data that looks like so:
You can either download the Zui Desktop App and use it to transform your data into a csv file. Or you can use the zq command line tool. The command for zq would be: