Add "id" property to JSON files to act as a foreign key

[innolitics]

Add "id" property to JSON files to act as a foreign key

[johndgiese]

@russellkan @fedorov hypothetically, we could split the externalReferences into a separate table, but this would be annoying. I can imagine other situations like this where splitting everything into tables could be annoying.

Thus, while I do feel that hierarchical structures should mostly be avoided in our JSON files, it is useful some times.

Thus I think you guys are right that sticking with JSON makes sense.

[johndgiese]

Thank you for your feedback @SevKohler , it sounds like we are going to stick with the JSON for now.

Thanks for providing this mapping!

[johndgiese]

Here are some additional thoughts about this:

• nearly all of the JSON files should be list of objects; each JSON file represents a table in a relational database
• each object in each list will have the same keys; these are the columns of the table
• each column will have flat values; i.e., no lists or nested objects
• most table/JSON file (e.g., ciods.json, modules.json, attributes.json, and the new sops.json) will all have an id column, representing the primary key of that table
• when there are references between tables, e.g. from sops.json to ciods.json, then the column names will end with "Id". E.g., ciodId. These can be thought of as "foreign keys" between the tables.
• some "join tables" will not have an id column, since there is no natural key. E.g., attributes_to_modules.json. Instead, these tables will have two foreign keys and meta-data associated with the relationship itself.
• applications that use the dicom-standard repo will need to join these tables and collapse them as appropriate. Thus, if you want your ciods.json to have a list of the SOPClassUIDs as originally proposed, you will have to write a python script to collapse them down. You also may need to prune out fields you don't use.
• we will occasionally make exceptions to this when there is a very compelling reason to, e.g. references.json could be converted to a list where the id column is the href, however, since almost every use case for references.json will want it to be an object, we felt it was unnecessarily pedantic to make this file follow the normal format

We think this approach to storing the data in dicom-standard will make it as generally useful as possible.

[johndgiese]

@russellkan perhaps we should switch from JSON to CSV files, since CSV files may be much more space-efficient than JSON?

[russellkan]

@johndgiese I think if we want to switch to CSVs, all of the JSON files should be converted to CSV files. However, I don't think that would be a good idea since while CSVs are great for storing tables, there are some files with a hierarchical structure such as module_to_attributes.json:

In this case, it may just be best to keep them all saved in a JSON format.

[fedorov]

I vote for keeping JSON. Most definitely, the hierarchical organization that does not map into fixed set of columns is probably a sufficient argument on its own. But also FYI ND-JSON is acceptable as input to Google BigQuery directly (in addition to CSV), and conversion of JSON to ND-JSON is trivial.

[SevKohler]

(Im not part of this but i have to say i prefer the json)
For the SOAP Uids to ciods i already did this (unfortanetly) manually for my masters thesis, maybe its of any use for you guys:
{
"1.2.840.10008.5.1.4.1.1.1" : "cr-image",
"1.2.840.10008.5.1.4.1.1.2" :"ct-image",
"1.2.840.10008.5.1.4.1.1.4" :"mr-image",
"1.2.840.10008.5.1.4.1.1.20" : "nm-image",
"1.2.840.10008.5.1.4.1.1.6.1" :"us-image",
"1.2.840.10008.5.1.4.1.1.3.1" : "us-multi-frame-image",
"1.2.840.10008.5.1.4.1.1.7" : "sc-image",
"1.2.840.10008.5.1.4.1.1.7.1" : "multi-frame-single-bit-sc-image",
"1.2.840.10008.5.1.4.1.1.7.2" : "multi-frame-grayscale-byte-sc-image",
"1.2.840.10008.5.1.4.1.1.7.3" :"multi-frame-grayscale-word-sc-image",
"1.2.840.10008.5.1.4.1.1.7.4" :"multi-frame-true-color-sc-image",
"1.2.840.10008.5.1.4.1.1.12.1" : "x-ray-angiographic-image",
"1.2.840.10008.5.1.4.1.1.12.2": "xrf-image",
"1.2.840.10008.5.1.4.1.1.481.1": "rt-image",
"1.2.840.10008.5.1.4.1.1.481.2": "rt-dose",
"1.2.840.10008.5.1.4.1.1.481.3": "rt-structure-set",
"1.2.840.10008.5.1.4.1.1.481.5": "rt-plan",
"1.2.840.10008.5.1.4.1.1.128": "pet-image",
"1.2.840.10008.5.1.4.1.1.1.1": "digital-x-ray-image",
"1.2.840.10008.5.1.4.1.1.1.1.1": "digital-x-ray-image",
"1.2.840.10008.5.1.4.1.1.1.2": "digital-mammography-x-ray-image",
"1.2.840.10008.5.1.4.1.1.1.2.1": "digital-mammography-x-ray-image",
"1.2.840.10008.5.1.4.1.1.1.3": "digital-intra-oral-x-ray-image",
"1.2.840.10008.5.1.4.1.1.1.3.1": "digital-intra-oral-x-ray-image",
"1.2.840.10008.5.1.4.1.1.481.4": "rt-beams-treatment-record",
"1.2.840.10008.5.1.4.1.1.481.6": "rt-brachy-treatment-record",
"1.2.840.10008.5.1.4.1.1.481.7": "rt-treatment-summary-record",
"1.2.840.10008.5.1.4.1.1.77.1.1": "vl-endoscopic-image",
"1.2.840.10008.5.1.4.1.1.77.1.2": "vl-microscopic-image",
"1.2.840.10008.5.1.4.1.1.77.1.3": "vl-slide-coordinates-microscopic-image",
"1.2.840.10008.5.1.4.1.1.77.1.4": "vl-photographic-image",
"1.2.840.10008.5.1.4.1.1.77.1.1.1": "video-endoscopic-image",
"1.2.840.10008.5.1.4.1.1.77.1.2.1": "video-microscopic-image",
"1.2.840.10008.5.1.4.1.1.77.1.4.1": "video-photographic-image",
"1.2.840.10008.5.1.4.1.1.77.1.6": "vl-whole-slide-microscopy-image",
"1.2.840.10008.5.1.4.1.1.11.1": "grayscale-softcopy-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.2": "color-softcopy-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.3": "pseudo-color-softcopy-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.4": "blending-softcopy-presentation-state",
"1.2.840.10008.5.1.4.1.1.131": "basic-structured-display",
"1.2.840.10008.5.1.4.1.1.11.5": "xa-xrf-grayscale-softcopy-presentation-state",
"1.2.840.10008.​5.​1.​4.​1.​1.​11.​6": "planar-mpr-volumetric-presentation-state",
"1.2.840.10008.​5.​1.​4.​1.​1.​11.​7": "planar-mpr-volumetric-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.8": "advanced-blending-presentation-state",
"1.2.840.10008.5.1.4.1.1.9.4.1": "basic-voice-audio",
"1.2.840.10008.5.1.4.1.1.9.1.1": "12-lead-ecg",
"1.2.840.10008.5.1.4.1.1.9.1.2": "general-ecg",
"1.2.840.10008.5.1.4.1.1.9.1.3": "ambulatory-ecg",
"1.2.840.10008.5.1.4.1.1.9.2.1": "hemodynamic",
"1.2.840.10008.5.1.4.1.1.9.3.1": "basic-cardiac-ep",
"1.2.840.10008.5.1.4.1.1.9.5.1": "arterial-pulse-waveform",
"1.2.840.10008.5.1.4.1.1.9.6.1": "respiratory-waveform",
"1.2.840.10008.5.1.4.1.1.9.4.2": "general-audio-waveform",
"1.2.840.10008.5.1.4.1.1.88.11": "basic-text-sr",
"1.2.840.10008.5.1.4.1.1.88.22": "enhanced-sr",
"1.2.840.10008.5.1.4.1.1.88.33": "comprehensive-sr",
"1.2.840.10008.5.1.4.1.1.88.59": "key-object-selection-document",
"1.2.840.10008.5.1.4.1.1.88.50": "mammography-cad-sr",
"1.2.840.10008.5.1.4.1.1.88.65": "chest-cad-sr",
"1.2.840.10008.5.1.4.1.1.88.40": "procedure-log",
"1.2.840.10008.5.1.4.1.1.88.67": "x-ray-radiation-dose-sr",
"1.2.840.10008.5.1.4.1.1.78.6": "spectacle-prescription-report",
"1.2.840.10008.5.1.4.1.1.88.69": "colon-cad-sr",
"1.2.840.10008.5.1.4.1.1.79.1": "macular-grid-thickness-and-volume-report",
"1.2.840.10008.5.1.4.1.1.88.70": "implantation-plan-sr-document",
"1.2.840.10008.5.1.4.1.1.88.34": "comprehensive-3d-sr",
"1.2.840.10008.5.1.4.1.1.88.68": "radiopharmaceutical-radiation-dose-sr",
"1.2.840.10008.5.1.4.1.1.88.35": "extensible-sr",
"1.2.840.10008.5.1.4.1.1.88.71": "acquisition-context-sr",
"1.2.840.10008.5.1.4.1.1.88.72": "simplified-adult-echo-sr",
"1.2.840.10008.5.1.4.1.1.88.73": "patient-radiation-dose-structured-report",
"1.2.840.10008.5.1.4.1.1.88.75": "performed-imaging-agent-administration-sr",
"1.2.840.10008.10.4": "rendition-selection-document",
"1.2.840.10008.5.1.4.1.1.4.1": "enhanced-mr-image",
"1.2.840.10008.5.1.4.1.1.4.2": "mr-spectroscopy",
"1.2.840.10008.5.1.4.1.1.4.3": "enhanced-mr-color-image",
"1.2.840.10008.5.1.4.1.1.66": "raw-data",
"1.2.840.10008.5.1.4.1.1.2.1": "enhanced-ct-image",
"1.2.840.10008.5.1.4.1.1.66.1": "spatial-registration",
"1.2.840.10008.5.1.4.1.1.66.3": "deformable-spatial-registration",
"1.2.840.10008.5.1.4.1.1.66.2": "spatial-fiducials",
"1.2.840.10008.5.1.4.1.1.77.1.5.1": "ophthalmic-photography-8-bit-image",
"1.2.840.10008.5.1.4.1.1.77.1.5.2": "ophthalmic-photography-16-bit-image",
"1.2.840.10008.5.1.4.1.1.77.1.5.3": "stereometric-relationship",
"1.2.840.10008.5.1.4.38.1": "hanging-protocol",
"1.2.840.10008.5.1.4.1.1.104.1": "encapsulated-pdf",
"1.2.840.10008.5.1.4.1.1.104.2": "encapsulated-cda",
"1.2.840.10008.5.1.4.1.1.67": "real-world-value-mapping",
"1.2.840.10008.5.1.4.1.1.12.1.1": "enhanced-x-ray-angiographic-image",
"1.2.840.10008.5.1.4.1.1.12.2.1": "enhanced-x-ray-rf-image",
"1.2.840.10008.5.1.4.1.1.481.8": "rt-ion-plan",
"1.2.840.10008.5.1.4.1.1.481.9": "rt-ion-beams-treatment-record",
"1.2.840.10008.5.1.4.1.1.66.4": "segmentation",
"1.2.840.10008.5.1.4.1.1.77.1.5.4": "ophthalmic-tomography-image",
"1.2.840.10008.5.1.4.1.1.13.1.1": "x-ray-3d-angiographic-image",
"1.2.840.10008.5.1.4.1.1.13.1.2": "x-ray-3d-craniofacial-image",
"1.2.840.10008.5.1.4.1.1.13.1.3": "breast-tomosynthesis-image",
"1.2.840.10008.5.1.4.1.1.130": "enhanced-pet-image",
"1.2.840.10008.5.1.4.1.1.66.5": "surface-segmentation",
"1.2.840.10008.5.1.4.39.1": "color-palette",
"1.2.840.10008.5.1.4.1.1.6.2": "enhanced-us-volume",
"1.2.840.10008.5.1.4.1.1.78.1": "lensometry-measurements",
"1.2.840.10008.5.1.4.1.1.78.2": "autorefraction-measurements",
"1.2.840.10008.5.1.4.1.1.78.3": "keratometry-measurements",
"1.2.840.10008.5.1.4.1.1.78.4": "subjective-refraction-measurements",
"1.2.840.10008.5.1.4.1.1.78.5": "visual-acuity-measurements",
"1.2.840.10008.5.1.4.1.1.78.7": "ophthalmic-axial-measurements",
"1.2.840.10008.5.1.4.1.1.78.8": "intraocular-lens-calculations",
"1.2.840.10008.5.1.4.43.1": "generic-implant-template",
"1.2.840.10008.5.1.4.44.1": "implant-assembly-template",
"1.2.840.10008.5.1.4.45.1": "implant-template-group",
"1.2.840.10008.5.1.4.34.7": "rt-beams-delivery-instruction",
"1.2.840.10008.5.1.4.1.1.80.1": "ophthalmic-visual-field-static-perimetry-measurements",
"1.2.840.10008.5.1.4.1.1.14.1": "intravascular-oct-image",
"1.2.840.10008.5.1.4.1.1.14.2": "intravascular-oct-image",
"1.2.840.10008.5.1.4.1.1.81.1": "ophthalmic-thickness-map",
"1.2.840.10008.5.1.4.1.1.68.1": "surface-scan-mesh",
"1.2.840.10008.5.1.4.1.1.68.2": "surface-scan-point-cloud",
"1.2.840.10008.5.1.4.1.1.2.2": "legacy-converted-enhanced-ct-image",
"1.2.840.10008.5.1.4.1.1.4.4": "legacy-converted-enhanced-mr-image",
"1.2.840.10008.5.1.4.1.1.128.1": "legacy-converted-enhanced-pet-image",
"1.2.840.10008.5.1.4.1.1.82.1": "corneal-topography-map",
"1.2.840.10008.5.1.4.1.1.13.1.4": "breast-projection-x-ray-image",
"1.2.840.10008.5.1.4.1.1.13.1.5": "breast-projection-x-ray-image",
"1.2.840.10008.5.1.4.1.1.30": "parametric-map",
"1.2.840.10008.5.1.4.1.1.77.1.5.5": "wide-field-ophthalmic-photography-stereographic-projection-image",
"1.2.840.10008.5.1.4.1.1.77.1.5.6": "wide-field-ophthalmic-photography-3d-coordinates-image",
"1.2.840.10008.5.1.4.1.1.66.6": "tractography-results",
"1.2.840.10008.5.1.4.34.10": "rt-brachy-application-setup-delivery-instruction",
"1.2.840.10008.5.1.4.1.1.11.6": "planar-mpr-volumetric-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.7": "planar-mpr-volumetric-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.9": "volume-rendering-volumetric-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.10": "volume-rendering-volumetric-presentation-state",
"1.2.840.10008.5.1.4.1.1.11.11": "volume-rendering-volumetric-presentation-state",
"1.2.840.10008.5.1.4.1.1.90.1": "content-assessment-results",
"1.2.840.10008.5.1.4.1.1.200.2": "table-ct-performed-procedure-protocol",
"1.2.840.10008.5.1.4.1.1.200.1": "ct-defined-procedure-protocol",
"1.2.840.10008.5.1.4.1.1.200.3": "protocol-approval",
"1.2.840.10008.5.1.4.1.1.77.1.5.7": "ophthalmic-optical-coherence-tomography-en-face-image",
"1.2.840.10008.5.1.4.1.1.77.1.5.8": "ophthalmic-optical-coherence-tomography-b-scan-volume-analysis",
"1.2.840.10008.5.1.4.1.1.104.3": "encapsulated-stl",
"1.2.840.10008.5.1.4.1.1.481.10": "rt-physician-intent",
"1.2.840.10008.5.1.4.1.1.481.11": "rt-segment-annotation",
"1.2.840.10008.5.1.4.1.1.481.12": "rt-radiation-set",
"1.2.840.10008.5.1.4.1.1.481.13": "c-arm-photon-electron-radiation"
}