Malloy Documentation
search
previousNumbers
Pivoted Tablesnext

Links

VSCode document

Results can be annotated so they are rendered with links to external URLs.

Properties

# link

Property Description Example
.url_template URL template ($$ replaced by value) # link { url_template="https://example.com/$$" }
.field Use another field's value for the URL # link { field=product_id url_template="https://...$$" }

# image

Property Description Example
.height Image height # image { height=40px }
.width Image width # image { width=100px }
.alt Alt text # image { alt=Logo }
.alt.field Use another field for alt text # image { alt.field=product_name }

The alt.field property supports relative paths to reference fields in parent or grandparent records when images are in nested views:

  • alt.field=name — field in the same record

  • alt.field='../brand' — field in the parent record

  • alt.field='../../brand' — field in the grandparent record

document
source: flights is duckdb.table('../data/flights.parquet') extend {
  measure: flight_count is count()
  join_one: carriers is duckdb.table('../data/carriers.parquet') on carrier=carriers.code
}

Simple link annotation

If you have a column which is a url, you can turn it into a link with a simple '# link` annotation

document
run: flights -> {
    # link
    group_by: flight_link is concat("https://www.flightsfrom.com/", origin)
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "flight_link": "https://www.flightsfrom.com/ATL",
    "flight_count": 17875
  },
  {
    "flight_link": "https://www.flightsfrom.com/DFW",
    "flight_count": 17782
  },
  {
    "flight_link": "https://www.flightsfrom.com/ORD",
    "flight_count": 14214
  },
  {
    "flight_link": "https://www.flightsfrom.com/PHX",
    "flight_count": 12476
  },
  {
    "flight_link": "https://www.flightsfrom.com/LAS",
    "flight_count": 11096
  }
]
SELECT 
   CONCAT('https://www.flightsfrom.com/',base."origin") as "flight_link",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Link url_template, append to the end

Often the thing you want on the template is at the end of the string. Create links to flightsfrom.com.

document
run: flights -> {
    # link {url_template='https://www.flightsfrom.com/'}
    group_by: origin
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "origin": "ATL",
    "flight_count": 17875
  },
  {
    "origin": "DFW",
    "flight_count": 17782
  },
  {
    "origin": "ORD",
    "flight_count": 14214
  },
  {
    "origin": "PHX",
    "flight_count": 12476
  },
  {
    "origin": "LAS",
    "flight_count": 11096
  }
]
SELECT 
   base."origin" as "origin",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Link url_template, substitution

Sometimes the variable part of the link needs to be substituted in the middle of the string. In this case, the output columns value inserted where $$ appears in the url_template.

Flights to SJC with links to 'flightsfrom.com'.

document
run: flights -> {
    where: destination = 'SJC'
    # link {url_template='https://www.flightsfrom.com/$$-SJC'}
    group_by: origin
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "origin": "LAX",
    "flight_count": 495
  },
  {
    "origin": "SAN",
    "flight_count": 423
  },
  {
    "origin": "SEA",
    "flight_count": 386
  },
  {
    "origin": "PHX",
    "flight_count": 289
  },
  {
    "origin": "LAS",
    "flight_count": 283
  }
]
SELECT 
   base."origin" as "origin",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
WHERE base."destination"='SJC'
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Link with key column coming from another field.

In the example below we want to show the Carrier's nickname with a link using the carriers code. We can do this by including the carrier code in the query, hiding it with a #hidden tag and referencing carrier using field parameter in the # link tag.

document
run: flights -> {
    # link {url_template='https://www.flightsfrom.com/$$' field=carrier}
    group_by:
      carriers.nickname
      # hidden
      carrier
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "nickname": "Southwest",
    "carrier": "WN",
    "flight_count": 88751
  },
  {
    "nickname": "USAir",
    "carrier": "US",
    "flight_count": 37683
  },
  {
    "nickname": "American",
    "carrier": "AA",
    "flight_count": 34577
  },
  {
    "nickname": "Northwest",
    "carrier": "NW",
    "flight_count": 33580
  },
  {
    "nickname": "United",
    "carrier": "UA",
    "flight_count": 32757
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   base."carrier" as "carrier",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST
LIMIT 5

Images

The # image tag renders a field's value as an inline image:

document
run: duckdb.sql("""
  SELECT * FROM (VALUES
    ('US', 'https://flagcdn.com/48x36/us.png'),
    ('France', 'https://flagcdn.com/48x36/fr.png'),
    ('Japan', 'https://flagcdn.com/48x36/jp.png')
  ) AS t(country, flag_url)
""") -> {
  select:
    country
    # image { height=36px alt.field=country }
    flag_url
}
QUERY RESULTS 
[
  {
    "country": "US",
    "flag_url": "https://flagcdn.com/48x36/us.png"
  },
  {
    "country": "France",
    "flag_url": "https://flagcdn.com/48x36/fr.png"
  },
  {
    "country": "Japan",
    "flag_url": "https://flagcdn.com/48x36/jp.png"
  }
]
SELECT 
   base."country" as "country",
   base."flag_url" as "flag_url"
FROM (
  SELECT * FROM (VALUES
    ('US', 'https://flagcdn.com/48x36/us.png'),
    ('France', 'https://flagcdn.com/48x36/fr.png'),
    ('Japan', 'https://flagcdn.com/48x36/jp.png')
  ) AS t(country, flag_url)
) as base