d2ql cookbook¶

Working recipes, grouped by what you're trying to do. Each one is a committed program in examples/d2ql/ — run any with d2w query run examples/d2ql/<name>.d2ql (add --profile <name> / --json).

Explore metadata¶

Data elements by value type — profile the dictionary (metadata-elements-by-value-type.d2ql):

dataElements | group by valueType { n: count() } | order n desc

ANC aggregate elements (metadata-anc-elements.d2ql):

dataElements | where domainType = "AGGREGATE" and name like "ANC" | select id, name, valueType | order name asc

Indicators with their type — a nested reference (metadata-indicators.d2ql):

indicators | select id, name, indicatorType.name as type | order name asc | limit 25

Option sets with their options — a repeating association collected into a list (metadata-option-sets.d2ql):

optionSets | select id, name, options.name as options | limit 25

Organisation units & GeoJSON¶

Facilities per level (orgunits-per-level.d2ql):

organisationUnits | group by level { facilities: count() } | order level asc

Districts with their parent (orgunits-with-parent.d2ql):

organisationUnits | where level = 2 | select id, name, parent.name as parent | order name asc

Export districts as a GeoJSON FeatureCollection — transform each row to a Feature, fold them into one object, write the file (geojson-featurecollection.d2ql):

define function feature(ou): {
  type: "Feature",
  properties: { id: $ou.id, name: $ou.name, level: $ou.level },
  geometry: $ou.geometry
}

organisationUnits
  | where level = 2 and geometry.type = "Polygon"
  | transform feature($this)
  | fold { type: "FeatureCollection", features: $rows }
  >> "/tmp/districts.geojson"

Aggregate data¶

Indicator time series — reshape analytics to one row per month (analytics-time-series.d2ql):

analytics(dx: "fbfJHSPpUQD", pe: "LAST_12_MONTHS", ou: "ImspTQPwCqd")
  | transform { month: pe, anc1: value }
  | order month asc

Roll analytics up per data element (analytics-rollup.d2ql):

analytics(dx: "fbfJHSPpUQD;cYeuwXTCPkU", pe: "LAST_12_MONTHS", ou: "ImspTQPwCqd")
  | where value > 1000
  | group by dx { total: sum(value), periods: count() }
  | order total desc

Raw data values from a dataset/period/org unit (datavalues-by-dataset.d2ql):

dataValues(dataSet: "BfMAe6Itzgt", period: "202401", orgUnit: "ImspTQPwCqd")
  | transform { de: dataElement, ou: orgUnit, value: value }

Reshape with `transform`¶

transform { … } builds one object per row from d2path expressions — flat renames, computed fields, nested objects, arrays. These are the building blocks the FHIR/GeoJSON recipes below are made of.

Rename and compute — a flat row with a derived initial and a boolean:

dataElements | transform { name: name, initial: name.substring(0, 1), aggregate: domainType = "AGGREGATE" }

[
  { "name": "Accute Flaccid Paralysis (Deaths < 5 yrs)", "initial": "A", "aggregate": true },
  { "name": "Acute Flaccid Paralysis (AFP) follow-up", "initial": "A", "aggregate": true }
]

Nested object — group fields under a sub-object:

dataElements | transform { id: id, meta: { type: valueType, combo: categoryCombo.name } }

[
  { "id": "FTRrcoaog83", "meta": { "type": "NUMBER", "combo": "default" } },
  { "id": "P3jJH5Tu5VC", "meta": { "type": "NUMBER", "combo": "Morbidity Cases" } }
]

Build an array — e.g. a FHIR-style coding list:

dataElements | transform { display: name, coding: [ { system: "dhis2", code: id } ] }

[
  { "display": "Accute Flaccid Paralysis (Deaths < 5 yrs)",
    "coding": [ { "system": "dhis2", "code": "FTRrcoaog83" } ] }
]

Factor the shape into a function — transform fn($this) keeps the pipeline readable when the per-row object grows (this is how the FHIR recipes are written):

define function summary(de): { code: $de.id, label: $de.name, type: $de.valueType }
dataElements | transform summary($this)

DHIS2 → FHIR¶

The recipe is always the same: a define function builds the resource, transform wraps each row, fold builds the Bundle envelope.

Data elements → a Bundle of Observations (fhir-bundle-de.d2ql):

define function observation(de): {
  resourceType: "Observation", status: "final",
  code: { coding: [ { system: "dhis2", code: $de.id, display: $de.name } ] }
}

dataElements | where domainType = "AGGREGATE"
  | transform { resource: observation($this) }
  | fold { resourceType: "Bundle", type: "collection", entry: $rows }

Option set → a CodeSystem (fhir-optionset-codesystem.d2ql):

optionSets | where name like "Age" | limit 1
  | fold {
      resourceType: "CodeSystem", status: "active", content: "complete",
      concept: options.select({ code: code, display: name })
    }

Option set → a ValueSet (fhir-optionset-valueset.d2ql); option sets → a Bundle of CodeSystems (fhir-bundle-optionset.d2ql); data set → a Questionnaire and data sets → a Bundle of Questionnaires (fhir-dataset-questionnaire.d2ql, fhir-bundle-dataset.d2ql) all follow the same transform + fold shape.

Reusable libraries¶

A .d2ql file with defines is a library you keep under version control (library-immunisation.d2ql):

define MinLevel: 2
define function isImmunisation(de): $de.name like "BCG" or $de.name like "measles" or $de.name like "Penta"
define Aggregates: dataElements | where domainType = "AGGREGATE"

Aggregates | where isImmunisation($this) | select id, name, valueType | order name asc | limit 25

Run it with d2w query run examples/d2ql/library-immunisation.d2ql, or run a specific definition with --define <name>.