Malloy Dimensional Indexes

VSCode

Malloy has a special query operator that builds _Dimensional Search Indexes for sources. A Dimensional Search Index is a table with 4 columns. Dimensional indexes are useful for a variety of things including filtering suggestions and LLMs.

When filtering data, you might know a term, but not necessarily which column in the one of the join data contains it. Indexing the data on field names and high cardinality fields let's you qucikly find the term and the associated value.

Indexing could be used by LLMs to find the interesting column/term mapping in the data set.

fieldName - The path to the column in the source
fieldValue - The dimensional value for the field (or range if fieldType is not a string)
fieldType - Type type of the column.
weight - a weighting to use as to the importance the distinct dimensional value. Defaults to cardinality of the field.

Simple Example

We're going to take the airports table and index it. The results are an un ordered list of distinct fieldName/fieldValue pairs appear in the table. The weight, in this case is the number of rows that partciular occurs on.

run: duckdb.table('../data/airports.parquet') -> {
  index: *
}

QUERY RESULTS

fieldName	fieldValue	fieldType	weight
code	Z53	string	1
code	0AK8	string	1
code	IGT	string	1
code	71AK	string	1
code	KGX	string	1
code	0AK	string	1
code	1AK2	string	1
code	Z09	string	1
code	ILI	string	1
code	3Z5	string	1
code	5KO	string	1
code	MYU	string	1
code	WRG	string	1
code	AL05	string	1
code	AL33	string	1
code	0AL2	string	1
code	3AL2	string	1
code	AL52	string	1
code	8A1	string	1
code	60A	string	1
code	2AR9	string	1
code	AR51	string	1
code	3AR7	string	1
code	49E	string	1
code	33E	string	1
code	AZ90	string	1
code	AZC	string	1
code	0AZ6	string	1
code	CFT	string	1
code	SDL	string	1
code	O62	string	1
code	6L2	string	1
code	CL52	string	1
code	0CA7	string	1
code	CL26	string	1
code	CA58	string	1
code	3L7	string	1
code	CL61	string	1
code	L03	string	1
code	GNU	string	1
code	CLP	string	1
code	5AR7	string	1
code	0Y3	string	1
code	2AR3	string	1
code	AR01	string	1
code	7E1	string	1
code	95CA	string	1
code	CL80	string	1
code	3CL4	string	1
code	CA94	string	1
code	Q69	string	1
code	AK06	string	1
site_number	51516.21*A	string	1
site_number	50714.44*A	string	1
site_number	50711.*A	string	1
site_number	50199.6*C	string	1
site_number	50181.7*A	string	1
site_number	50036.03*A	string	1
site_number	50692.*A	string	1
site_number	50340.8*A	string	1
site_number	50920.*A	string	1
site_number	50738.*A	string	1
site_number	00302.01*H	string	1
site_number	00310.1*A	string	1
site_number	00536.*C	string	1
site_number	00374.1*A	string	1
site_number	00267.*A	string	1
site_number	00329.*A	string	1
site_number	01101.1*A	string	1
site_number	01156.02*A	string	1
site_number	01016.2*A	string	1
site_number	00937.01*H	string	1
site_number	01060.01*A	string	1
site_number	01156.01*H	string	1
site_number	01104.4*A	string	1
site_number	00742.12*H	string	1
site_number	00812.06*H	string	1
site_number	00802.4*U	string	1
site_number	01796.*A	string	1
site_number	01696.01*A	string	1
site_number	01280.8*H	string	1
site_number	01818.4*H	string	1
site_number	01767.12*A	string	1
site_number	01340.281*A	string	1
site_number	01625.1*H	string	1
site_number	50672.01*A	string	1
site_number	50033.5*A	string	1
site_number	50221.3*C	string	1
site_number	50216.31*A	string	1
site_number	50130.1*H	string	1
site_number	00812.08*H	string	1
site_number	00751.18*H	string	1
site_number	00825.21*A	string	1
site_number	02422.01*A	string	1
site_number	02352.34*H	string	1
site_number	50725.*A	string	1
site_number	00642.9*A	string	1
site_number	50215.1*A	string	1
site_number	01975.11*H	string	1
site_number	01126.12*A	string	1

[
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "Z53",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "0AK8",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "IGT",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "71AK",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "KGX",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "0AK",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "1AK2",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "Z09",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "ILI",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "3Z5",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "5KO",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "MYU",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "WRG",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AL05",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AL33",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "0AL2",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "3AL2",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AL52",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "8A1",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "60A",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "2AR9",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AR51",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "3AR7",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "49E",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "33E",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AZ90",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AZC",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "0AZ6",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CFT",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "SDL",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "O62",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "6L2",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CL52",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "0CA7",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CL26",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CA58",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "3L7",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CL61",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "L03",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "GNU",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CLP",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "5AR7",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "0Y3",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "2AR3",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AR01",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "7E1",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "95CA",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CL80",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "3CL4",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "CA94",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "Q69",
    "weight": 1
  },
  {
    "fieldName": "code",
    "fieldType": "string",
    "fieldValue": "AK06",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "51516.21*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50714.44*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50711.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50199.6*C",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50181.7*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50036.03*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50692.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50340.8*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50920.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50738.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00302.01*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00310.1*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00536.*C",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00374.1*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00267.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00329.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01101.1*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01156.02*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01016.2*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00937.01*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01060.01*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01156.01*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01104.4*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00742.12*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00812.06*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00802.4*U",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01796.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01696.01*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01280.8*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01818.4*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01767.12*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01340.281*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01625.1*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50672.01*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50033.5*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50221.3*C",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50216.31*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50130.1*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00812.08*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00751.18*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00825.21*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "02422.01*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "02352.34*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50725.*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "00642.9*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "50215.1*A",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01975.11*H",
    "weight": 1
  },
  {
    "fieldName": "site_number",
    "fieldType": "string",
    "fieldValue": "01126.12*A",
    "weight": 1
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'id'
      WHEN 1 THEN 'code'
      WHEN 2 THEN 'site_number'
      WHEN 3 THEN 'fac_type'
      WHEN 4 THEN 'fac_use'
      WHEN 5 THEN 'faa_region'
      WHEN 6 THEN 'faa_dist'
      WHEN 7 THEN 'city'
      WHEN 8 THEN 'county'
      WHEN 9 THEN 'state'
      WHEN 10 THEN 'full_name'
      WHEN 11 THEN 'own_type'
      WHEN 12 THEN 'longitude'
      WHEN 13 THEN 'latitude'
      WHEN 14 THEN 'elevation'
      WHEN 15 THEN 'aero_cht'
      WHEN 16 THEN 'cbd_dist'
      WHEN 17 THEN 'cbd_dir'
      WHEN 18 THEN 'act_date'
      WHEN 19 THEN 'cert'
      WHEN 20 THEN 'fed_agree'
      WHEN 21 THEN 'cust_intl'
      WHEN 22 THEN 'c_ldg_rts'
      WHEN 23 THEN 'joint_use'
      WHEN 24 THEN 'mil_rts'
      WHEN 25 THEN 'cntl_twr'
      WHEN 26 THEN 'major'
    END as "fieldName",  CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'string'
      WHEN 7 THEN 'string'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'number'
      WHEN 13 THEN 'number'
      WHEN 14 THEN 'number'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'number'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'string'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'string'
      WHEN 21 THEN 'string'
      WHEN 22 THEN 'string'
      WHEN 23 THEN 'string'
      WHEN 24 THEN 'string'
      WHEN 25 THEN 'string'
      WHEN 26 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN NULL
      WHEN 1 THEN base."code"
      WHEN 2 THEN base."site_number"
      WHEN 3 THEN base."fac_type"
      WHEN 4 THEN base."fac_use"
      WHEN 5 THEN base."faa_region"
      WHEN 6 THEN base."faa_dist"
      WHEN 7 THEN base."city"
      WHEN 8 THEN base."county"
      WHEN 9 THEN base."state"
      WHEN 10 THEN base."full_name"
      WHEN 11 THEN base."own_type"
      WHEN 15 THEN base."aero_cht"
      WHEN 17 THEN base."cbd_dir"
      WHEN 18 THEN base."act_date"
      WHEN 19 THEN base."cert"
      WHEN 20 THEN base."fed_agree"
      WHEN 21 THEN base."cust_intl"
      WHEN 22 THEN base."c_ldg_rts"
      WHEN 23 THEN base."joint_use"
      WHEN 24 THEN base."mil_rts"
      WHEN 25 THEN base."cntl_twr"
      WHEN 26 THEN base."major"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 12 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") as VARCHAR)
      WHEN 13 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 14 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 16 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
    END as "fieldRange"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,27,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
SELECT
  "fieldName",
  "fieldType",
  COALESCE("fieldValue", "fieldRange") as "fieldValue",
  weight
FROM __stage0

Add Ordering

Adding a second query stage to filter on string columns and ordering by weight descending shows us the most common fieldName/fieldValue pairs in the dataset.

All Malloy queries run as a single SQL query. The index: operator is no different. Click the SQL tab to see how this works.

run: duckdb.table('../data/airports.parquet') -> {
  index: *
} -> {
  where: fieldType = 'string'
  select: *
  order_by: weight desc
}

QUERY RESULTS

fieldName	fieldValue	fieldType	weight
major	N	string	19,523
cert	∅	string	19,142
cntl_twr	N	string	19,124
fed_agree	∅	string	16,252
cust_intl	∅	string	15,145
c_ldg_rts	∅	string	15,145
joint_use	∅	string	14,804
mil_rts	∅	string	14,716
fac_use	PR	string	14,428
own_type	PR	string	14,306
fac_type	AIRPORT	string	13,925
act_date	∅	string	12,040
faa_dist	NONE	string	7,085
fac_use	PU	string	5,365
own_type	PU	string	5,174
fac_type	HELIPORT	string	5,135
joint_use	N	string	4,779
cust_intl	N	string	4,575
faa_region	AGL	string	4,437
c_ldg_rts	N	string	4,365
cbd_dir	N	string	3,694
faa_region	ASW	string	3,268
mil_rts	Y	string	2,958
faa_region	ASO	string	2,924
faa_region	AEA	string	2,586
cbd_dir	NW	string	2,438
cbd_dir	SW	string	2,427
cbd_dir	NE	string	2,393
cbd_dir	SE	string	2,281
cbd_dir	S	string	2,155
cbd_dir	E	string	2,127
mil_rts	N	string	2,119
faa_region	ANM	string	2,102
cbd_dir	W	string	2,065
state	TX	string	1,845
fed_agree	NGY	string	1,682
aero_cht	NEW YORK	string	1,581
faa_region	ACE	string	1,579
faa_dist	CHI	string	1,528
faa_region	AWP	string	1,503
aero_cht	CHICAGO	string	1,480
aero_cht	DETROIT	string	1,262
faa_dist	DET	string	1,240
faa_dist	SEA	string	1,162
faa_dist	MSP	string	1,046
faa_dist	ATL	string	1,029
state	CA	string	984
faa_dist	NYC	string	957
faa_dist	ORL	string	905
state	IL	string	890
aero_cht	DALLAS-FT WORTH	string	888
aero_cht	WASHINGTON	string	887
state	FL	string	856
aero_cht	ST LOUIS	string	847
faa_dist	HAR	string	846
state	PA	string	804
aero_cht	ATLANTA	string	780
aero_cht	HOUSTON	string	765
faa_region	ANE	string	763
state	OH	string	749
aero_cht	SEATTLE	string	749
aero_cht	KANSAS CITY	string	713
aero_cht	TWIN CITIES	string	699
faa_dist	DEN	string	679
cntl_twr	Y	string	669
faa_dist	DCA	string	667
state	IN	string	643
aero_cht	CINCINNATI	string	624
faa_dist	BIS	string	616
faa_region	AAL	string	608
state	AK	string	608
aero_cht	MEMPHIS	string	605
faa_dist	SFO	string	585
state	NY	string	576
aero_cht	OMAHA	string	551
state	WI	string	543
state	MO	string	537
fed_agree	N	string	515
state	MN	string	507
aero_cht	SAN ANTONIO	string	503
faa_dist	JAN	string	502
state	LA	string	500
aero_cht	LOS ANGELES	string	498
state	MI	string	489
faa_dist	MEM	string	487
state	WA	string	484
fac_type	SEAPLANE BASE	string	473
aero_cht	JACKSONVILLE	string	467
aero_cht	NEW ORLEANS	string	444
state	OK	string	443
state	OR	string	441
state	GA	string	440
state	ND	string	436
aero_cht	SAN FRANCISCO	string	427
state	CO	string	425
state	VA	string	421
aero_cht	GREEN BAY	string	418
state	KS	string	415
state	NC	string	400
aero_cht	CHARLOTTE	string	380

[
  {
    "fieldName": "major",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 19523
  },
  {
    "fieldName": "cert",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 19142
  },
  {
    "fieldName": "cntl_twr",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 19124
  },
  {
    "fieldName": "fed_agree",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 16252
  },
  {
    "fieldName": "cust_intl",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 15145
  },
  {
    "fieldName": "c_ldg_rts",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 15145
  },
  {
    "fieldName": "joint_use",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 14804
  },
  {
    "fieldName": "mil_rts",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 14716
  },
  {
    "fieldName": "fac_use",
    "fieldValue": "PR",
    "fieldType": "string",
    "weight": 14428
  },
  {
    "fieldName": "own_type",
    "fieldValue": "PR",
    "fieldType": "string",
    "weight": 14306
  },
  {
    "fieldName": "fac_type",
    "fieldValue": "AIRPORT",
    "fieldType": "string",
    "weight": 13925
  },
  {
    "fieldName": "act_date",
    "fieldValue": null,
    "fieldType": "string",
    "weight": 12040
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "NONE",
    "fieldType": "string",
    "weight": 7085
  },
  {
    "fieldName": "fac_use",
    "fieldValue": "PU",
    "fieldType": "string",
    "weight": 5365
  },
  {
    "fieldName": "own_type",
    "fieldValue": "PU",
    "fieldType": "string",
    "weight": 5174
  },
  {
    "fieldName": "fac_type",
    "fieldValue": "HELIPORT",
    "fieldType": "string",
    "weight": 5135
  },
  {
    "fieldName": "joint_use",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 4779
  },
  {
    "fieldName": "cust_intl",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 4575
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "AGL",
    "fieldType": "string",
    "weight": 4437
  },
  {
    "fieldName": "c_ldg_rts",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 4365
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 3694
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "ASW",
    "fieldType": "string",
    "weight": 3268
  },
  {
    "fieldName": "mil_rts",
    "fieldValue": "Y",
    "fieldType": "string",
    "weight": 2958
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "ASO",
    "fieldType": "string",
    "weight": 2924
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "AEA",
    "fieldType": "string",
    "weight": 2586
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "NW",
    "fieldType": "string",
    "weight": 2438
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "SW",
    "fieldType": "string",
    "weight": 2427
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "NE",
    "fieldType": "string",
    "weight": 2393
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "SE",
    "fieldType": "string",
    "weight": 2281
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "S",
    "fieldType": "string",
    "weight": 2155
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "E",
    "fieldType": "string",
    "weight": 2127
  },
  {
    "fieldName": "mil_rts",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 2119
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "ANM",
    "fieldType": "string",
    "weight": 2102
  },
  {
    "fieldName": "cbd_dir",
    "fieldValue": "W",
    "fieldType": "string",
    "weight": 2065
  },
  {
    "fieldName": "state",
    "fieldValue": "TX",
    "fieldType": "string",
    "weight": 1845
  },
  {
    "fieldName": "fed_agree",
    "fieldValue": "NGY",
    "fieldType": "string",
    "weight": 1682
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "NEW YORK",
    "fieldType": "string",
    "weight": 1581
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "ACE",
    "fieldType": "string",
    "weight": 1579
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "CHI",
    "fieldType": "string",
    "weight": 1528
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "AWP",
    "fieldType": "string",
    "weight": 1503
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "CHICAGO",
    "fieldType": "string",
    "weight": 1480
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "DETROIT",
    "fieldType": "string",
    "weight": 1262
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "DET",
    "fieldType": "string",
    "weight": 1240
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "SEA",
    "fieldType": "string",
    "weight": 1162
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "MSP",
    "fieldType": "string",
    "weight": 1046
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "ATL",
    "fieldType": "string",
    "weight": 1029
  },
  {
    "fieldName": "state",
    "fieldValue": "CA",
    "fieldType": "string",
    "weight": 984
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "NYC",
    "fieldType": "string",
    "weight": 957
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "ORL",
    "fieldType": "string",
    "weight": 905
  },
  {
    "fieldName": "state",
    "fieldValue": "IL",
    "fieldType": "string",
    "weight": 890
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "DALLAS-FT WORTH",
    "fieldType": "string",
    "weight": 888
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "WASHINGTON",
    "fieldType": "string",
    "weight": 887
  },
  {
    "fieldName": "state",
    "fieldValue": "FL",
    "fieldType": "string",
    "weight": 856
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "ST LOUIS",
    "fieldType": "string",
    "weight": 847
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "HAR",
    "fieldType": "string",
    "weight": 846
  },
  {
    "fieldName": "state",
    "fieldValue": "PA",
    "fieldType": "string",
    "weight": 804
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "ATLANTA",
    "fieldType": "string",
    "weight": 780
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "HOUSTON",
    "fieldType": "string",
    "weight": 765
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "ANE",
    "fieldType": "string",
    "weight": 763
  },
  {
    "fieldName": "state",
    "fieldValue": "OH",
    "fieldType": "string",
    "weight": 749
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "SEATTLE",
    "fieldType": "string",
    "weight": 749
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "KANSAS CITY",
    "fieldType": "string",
    "weight": 713
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "TWIN CITIES",
    "fieldType": "string",
    "weight": 699
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "DEN",
    "fieldType": "string",
    "weight": 679
  },
  {
    "fieldName": "cntl_twr",
    "fieldValue": "Y",
    "fieldType": "string",
    "weight": 669
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "DCA",
    "fieldType": "string",
    "weight": 667
  },
  {
    "fieldName": "state",
    "fieldValue": "IN",
    "fieldType": "string",
    "weight": 643
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "CINCINNATI",
    "fieldType": "string",
    "weight": 624
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "BIS",
    "fieldType": "string",
    "weight": 616
  },
  {
    "fieldName": "faa_region",
    "fieldValue": "AAL",
    "fieldType": "string",
    "weight": 608
  },
  {
    "fieldName": "state",
    "fieldValue": "AK",
    "fieldType": "string",
    "weight": 608
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "MEMPHIS",
    "fieldType": "string",
    "weight": 605
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "SFO",
    "fieldType": "string",
    "weight": 585
  },
  {
    "fieldName": "state",
    "fieldValue": "NY",
    "fieldType": "string",
    "weight": 576
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "OMAHA",
    "fieldType": "string",
    "weight": 551
  },
  {
    "fieldName": "state",
    "fieldValue": "WI",
    "fieldType": "string",
    "weight": 543
  },
  {
    "fieldName": "state",
    "fieldValue": "MO",
    "fieldType": "string",
    "weight": 537
  },
  {
    "fieldName": "fed_agree",
    "fieldValue": "N",
    "fieldType": "string",
    "weight": 515
  },
  {
    "fieldName": "state",
    "fieldValue": "MN",
    "fieldType": "string",
    "weight": 507
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "SAN ANTONIO",
    "fieldType": "string",
    "weight": 503
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "JAN",
    "fieldType": "string",
    "weight": 502
  },
  {
    "fieldName": "state",
    "fieldValue": "LA",
    "fieldType": "string",
    "weight": 500
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "LOS ANGELES",
    "fieldType": "string",
    "weight": 498
  },
  {
    "fieldName": "state",
    "fieldValue": "MI",
    "fieldType": "string",
    "weight": 489
  },
  {
    "fieldName": "faa_dist",
    "fieldValue": "MEM",
    "fieldType": "string",
    "weight": 487
  },
  {
    "fieldName": "state",
    "fieldValue": "WA",
    "fieldType": "string",
    "weight": 484
  },
  {
    "fieldName": "fac_type",
    "fieldValue": "SEAPLANE BASE",
    "fieldType": "string",
    "weight": 473
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "JACKSONVILLE",
    "fieldType": "string",
    "weight": 467
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "NEW ORLEANS",
    "fieldType": "string",
    "weight": 444
  },
  {
    "fieldName": "state",
    "fieldValue": "OK",
    "fieldType": "string",
    "weight": 443
  },
  {
    "fieldName": "state",
    "fieldValue": "OR",
    "fieldType": "string",
    "weight": 441
  },
  {
    "fieldName": "state",
    "fieldValue": "GA",
    "fieldType": "string",
    "weight": 440
  },
  {
    "fieldName": "state",
    "fieldValue": "ND",
    "fieldType": "string",
    "weight": 436
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "SAN FRANCISCO",
    "fieldType": "string",
    "weight": 427
  },
  {
    "fieldName": "state",
    "fieldValue": "CO",
    "fieldType": "string",
    "weight": 425
  },
  {
    "fieldName": "state",
    "fieldValue": "VA",
    "fieldType": "string",
    "weight": 421
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "GREEN BAY",
    "fieldType": "string",
    "weight": 418
  },
  {
    "fieldName": "state",
    "fieldValue": "KS",
    "fieldType": "string",
    "weight": 415
  },
  {
    "fieldName": "state",
    "fieldValue": "NC",
    "fieldType": "string",
    "weight": 400
  },
  {
    "fieldName": "aero_cht",
    "fieldValue": "CHARLOTTE",
    "fieldType": "string",
    "weight": 380
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'id'
      WHEN 1 THEN 'code'
      WHEN 2 THEN 'site_number'
      WHEN 3 THEN 'fac_type'
      WHEN 4 THEN 'fac_use'
      WHEN 5 THEN 'faa_region'
      WHEN 6 THEN 'faa_dist'
      WHEN 7 THEN 'city'
      WHEN 8 THEN 'county'
      WHEN 9 THEN 'state'
      WHEN 10 THEN 'full_name'
      WHEN 11 THEN 'own_type'
      WHEN 12 THEN 'longitude'
      WHEN 13 THEN 'latitude'
      WHEN 14 THEN 'elevation'
      WHEN 15 THEN 'aero_cht'
      WHEN 16 THEN 'cbd_dist'
      WHEN 17 THEN 'cbd_dir'
      WHEN 18 THEN 'act_date'
      WHEN 19 THEN 'cert'
      WHEN 20 THEN 'fed_agree'
      WHEN 21 THEN 'cust_intl'
      WHEN 22 THEN 'c_ldg_rts'
      WHEN 23 THEN 'joint_use'
      WHEN 24 THEN 'mil_rts'
      WHEN 25 THEN 'cntl_twr'
      WHEN 26 THEN 'major'
    END as "fieldName",  CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'string'
      WHEN 7 THEN 'string'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'number'
      WHEN 13 THEN 'number'
      WHEN 14 THEN 'number'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'number'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'string'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'string'
      WHEN 21 THEN 'string'
      WHEN 22 THEN 'string'
      WHEN 23 THEN 'string'
      WHEN 24 THEN 'string'
      WHEN 25 THEN 'string'
      WHEN 26 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN NULL
      WHEN 1 THEN base."code"
      WHEN 2 THEN base."site_number"
      WHEN 3 THEN base."fac_type"
      WHEN 4 THEN base."fac_use"
      WHEN 5 THEN base."faa_region"
      WHEN 6 THEN base."faa_dist"
      WHEN 7 THEN base."city"
      WHEN 8 THEN base."county"
      WHEN 9 THEN base."state"
      WHEN 10 THEN base."full_name"
      WHEN 11 THEN base."own_type"
      WHEN 15 THEN base."aero_cht"
      WHEN 17 THEN base."cbd_dir"
      WHEN 18 THEN base."act_date"
      WHEN 19 THEN base."cert"
      WHEN 20 THEN base."fed_agree"
      WHEN 21 THEN base."cust_intl"
      WHEN 22 THEN base."c_ldg_rts"
      WHEN 23 THEN base."joint_use"
      WHEN 24 THEN base."mil_rts"
      WHEN 25 THEN base."cntl_twr"
      WHEN 26 THEN base."major"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 12 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") as VARCHAR)
      WHEN 13 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 14 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 16 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
    END as "fieldRange"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,27,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage0
)
SELECT 
   base."fieldName" as "fieldName",
   base."fieldValue" as "fieldValue",
   base."fieldType" as "fieldType",
   base."weight" as "weight"
FROM __stage1 as base
WHERE base."fieldType"='string'
ORDER BY 4 desc NULLS LAST

Index For Filtering User Interfaces

Indexes can be used find the best way to filter a dataset. For example supposed we'd like to find 'SANTA CRUZ' in the dataset. Upon approaching the dataset, but we don't which column might contain it. In a UI you might imagine that you type 'SANTA' and let have suggestons for values that might be appropriate. In the results we can see that top value, 'SANTA ROSA', appears as county on 26 rows in the table. We can also see that 'SANTA CRUZ' is both a city and a county..

run: duckdb.table('../data/airports.parquet') -> {
  index: *
} -> {
  where: fieldValue ~ r'SANTA'
  select: *
  order_by: weight desc
  limit: 15
}

QUERY RESULTS

fieldName	fieldValue	fieldType	weight
county	SANTA ROSA	string	26
county	SANTA BARBARA	string	22
county	SANTA CRUZ	string	10
county	SANTA CLARA	string	10
county	SANTA FE	string	9
city	SANTA FE	string	6
city	SANTA ANA	string	6
city	SANTA BARBARA	string	5
city	SANTA ROSA	string	4
city	SANTA MARIA	string	3
city	SANTA YNEZ	string	3
city	RANCHO SANTA MARGARITA	string	2
city	SANTA YSABEL	string	2
city	SANTA CRUZ	string	2
city	SANTA ELENA	string	2

[
  {
    "fieldName": "county",
    "fieldValue": "SANTA ROSA",
    "fieldType": "string",
    "weight": 26
  },
  {
    "fieldName": "county",
    "fieldValue": "SANTA BARBARA",
    "fieldType": "string",
    "weight": 22
  },
  {
    "fieldName": "county",
    "fieldValue": "SANTA CRUZ",
    "fieldType": "string",
    "weight": 10
  },
  {
    "fieldName": "county",
    "fieldValue": "SANTA CLARA",
    "fieldType": "string",
    "weight": 10
  },
  {
    "fieldName": "county",
    "fieldValue": "SANTA FE",
    "fieldType": "string",
    "weight": 9
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA FE",
    "fieldType": "string",
    "weight": 6
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA ANA",
    "fieldType": "string",
    "weight": 6
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA BARBARA",
    "fieldType": "string",
    "weight": 5
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA ROSA",
    "fieldType": "string",
    "weight": 4
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA MARIA",
    "fieldType": "string",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA YNEZ",
    "fieldType": "string",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldValue": "RANCHO SANTA MARGARITA",
    "fieldType": "string",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA YSABEL",
    "fieldType": "string",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA CRUZ",
    "fieldType": "string",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldValue": "SANTA ELENA",
    "fieldType": "string",
    "weight": 2
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'id'
      WHEN 1 THEN 'code'
      WHEN 2 THEN 'site_number'
      WHEN 3 THEN 'fac_type'
      WHEN 4 THEN 'fac_use'
      WHEN 5 THEN 'faa_region'
      WHEN 6 THEN 'faa_dist'
      WHEN 7 THEN 'city'
      WHEN 8 THEN 'county'
      WHEN 9 THEN 'state'
      WHEN 10 THEN 'full_name'
      WHEN 11 THEN 'own_type'
      WHEN 12 THEN 'longitude'
      WHEN 13 THEN 'latitude'
      WHEN 14 THEN 'elevation'
      WHEN 15 THEN 'aero_cht'
      WHEN 16 THEN 'cbd_dist'
      WHEN 17 THEN 'cbd_dir'
      WHEN 18 THEN 'act_date'
      WHEN 19 THEN 'cert'
      WHEN 20 THEN 'fed_agree'
      WHEN 21 THEN 'cust_intl'
      WHEN 22 THEN 'c_ldg_rts'
      WHEN 23 THEN 'joint_use'
      WHEN 24 THEN 'mil_rts'
      WHEN 25 THEN 'cntl_twr'
      WHEN 26 THEN 'major'
    END as "fieldName",  CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'string'
      WHEN 7 THEN 'string'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'number'
      WHEN 13 THEN 'number'
      WHEN 14 THEN 'number'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'number'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'string'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'string'
      WHEN 21 THEN 'string'
      WHEN 22 THEN 'string'
      WHEN 23 THEN 'string'
      WHEN 24 THEN 'string'
      WHEN 25 THEN 'string'
      WHEN 26 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN NULL
      WHEN 1 THEN base."code"
      WHEN 2 THEN base."site_number"
      WHEN 3 THEN base."fac_type"
      WHEN 4 THEN base."fac_use"
      WHEN 5 THEN base."faa_region"
      WHEN 6 THEN base."faa_dist"
      WHEN 7 THEN base."city"
      WHEN 8 THEN base."county"
      WHEN 9 THEN base."state"
      WHEN 10 THEN base."full_name"
      WHEN 11 THEN base."own_type"
      WHEN 15 THEN base."aero_cht"
      WHEN 17 THEN base."cbd_dir"
      WHEN 18 THEN base."act_date"
      WHEN 19 THEN base."cert"
      WHEN 20 THEN base."fed_agree"
      WHEN 21 THEN base."cust_intl"
      WHEN 22 THEN base."c_ldg_rts"
      WHEN 23 THEN base."joint_use"
      WHEN 24 THEN base."mil_rts"
      WHEN 25 THEN base."cntl_twr"
      WHEN 26 THEN base."major"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 12 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") as VARCHAR)
      WHEN 13 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 14 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 16 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
    END as "fieldRange"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,27,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage0
)
SELECT 
   base."fieldName" as "fieldName",
   base."fieldValue" as "fieldValue",
   base."fieldType" as "fieldType",
   base."weight" as "weight"
FROM __stage1 as base
WHERE REGEXP_MATCHES(base."fieldValue", 'SANTA')
ORDER BY 4 desc NULLS LAST
LIMIT 15

We can then write a simple query to show the rows. It turns out that 'SANTA CRUZ' is a county in both California and Arizona.

run: duckdb.table('../data/airports.parquet') -> {
  where: county ~ 'SANTA CRUZ'
  select: *
}

QUERY RESULTS

id	code	site_number	fac_type	fac_use	faa_region	faa_dist	city	county	state	full_name	own_type	longitude	latitude	elevation	aero_cht	cbd_dist	cbd_dir	act_date	cert	fed_agree	cust_intl	c_ldg_rts	joint_use	mil_rts	cntl_twr	major
1,444	2AZ8	00811.*U	ULTRALIGHT	PR	AWP	NONE	TUBAC	SANTA CRUZ	AZ	TUBAC ULTRALIGHT FLIGHTPARK	PR	-111.03	31.61	3,200	PHOENIX	1	E	10/1987	∅	∅	∅	∅	∅	∅	N	N
1,312	OLS	00739.*A	AIRPORT	PU	AWP	NONE	NOGALES	SANTA CRUZ	AZ	NOGALES INTL	PU	-110.84	31.41	3,955	PHOENIX	7	NE	∅	∅	NGY	Y	N	N	Y	N	N
2,287	NSI	02220.*A	AIRPORT	PR	AWP	NONE	SAN NICOLAS ISLAND	SANTA CRUZ	CA	SAN NICOLAS ISLAND NOLF	MN	-119.45	33.23	504	LOS ANGELES	0	∅	∅	∅	∅	∅	∅	∅	Y	Y	N
2,307	CL77	02241.1*A	AIRPORT	PR	AWP	SFO	SANTA CRUZ	SANTA CRUZ	CA	BONNY DOON VILLAGE	PR	-122.12	37.07	2,020	SAN FRANCISCO	8	NW	07/1986	∅	∅	∅	∅	∅	∅	N	N
2,308	CA37	02241.12*H	HELIPORT	PR	AWP	SFO	SANTA CRUZ	SANTA CRUZ	CA	DOMINICAN SANTA CRUZ HOSPITAL	PR	-121.98	36.99	115	SAN FRANCISCO	3	NE	∅	∅	∅	∅	∅	∅	∅	N	N
1,693	6Q6	01486.9*A	AIRPORT	PR	AWP	SFO	DAVENPORT	SANTA CRUZ	CA	LAS TRANCAS	PR	-122.27	37.08	125	SAN FRANCISCO	6	NW	∅	∅	∅	∅	∅	N	N	N	N
2,441	CA65	02429.1*H	HELIPORT	PR	AWP	SFO	WATSONVILLE	SANTA CRUZ	CA	ALTA VISTA	PR	-121.86	36.97	480	SAN FRANCISCO	5	NW	∅	∅	∅	∅	∅	N	N	N	N
2,442	CL99	02429.11*H	HELIPORT	PR	AWP	SFO	WATSONVILLE	SANTA CRUZ	CA	WATSONVILLE COMMUNITY HOSPITAL	PU	-121.77	36.93	111	SAN FRANCISCO	0	E	09/1987	∅	∅	∅	∅	∅	∅	N	N
2,443	CA66	02429.2*A	AIRPORT	PR	AWP	SFO	WATSONVILLE	SANTA CRUZ	CA	MONTEREY BAY ACADEMY	PR	-121.84	36.9	70	SAN FRANCISCO	4	W	∅	∅	∅	∅	∅	N	N	N	N
2,440	WVI	02429.*A	AIRPORT	PU	AWP	SFO	WATSONVILLE	SANTA CRUZ	CA	WATSONVILLE MUNI	PU	-121.78	36.93	160	SAN FRANCISCO	3	NW	∅	∅	NGPRY	N	N	N	Y	N	N

[
  {
    "id": 1444,
    "code": "2AZ8",
    "site_number": "00811.*U",
    "fac_type": "ULTRALIGHT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "NONE",
    "city": "TUBAC",
    "county": "SANTA CRUZ",
    "state": "AZ",
    "full_name": "TUBAC ULTRALIGHT FLIGHTPARK",
    "own_type": "PR",
    "longitude": -111.03,
    "latitude": 31.61,
    "elevation": 3200,
    "aero_cht": "PHOENIX",
    "cbd_dist": 1,
    "cbd_dir": "E",
    "act_date": "10/1987",
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": null,
    "mil_rts": null,
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 1312,
    "code": "OLS",
    "site_number": "00739.*A",
    "fac_type": "AIRPORT",
    "fac_use": "PU",
    "faa_region": "AWP",
    "faa_dist": "NONE",
    "city": "NOGALES",
    "county": "SANTA CRUZ",
    "state": "AZ",
    "full_name": "NOGALES INTL",
    "own_type": "PU",
    "longitude": -110.84,
    "latitude": 31.41,
    "elevation": 3955,
    "aero_cht": "PHOENIX",
    "cbd_dist": 7,
    "cbd_dir": "NE",
    "act_date": null,
    "cert": null,
    "fed_agree": "NGY",
    "cust_intl": "Y",
    "c_ldg_rts": "N",
    "joint_use": "N",
    "mil_rts": "Y",
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 2287,
    "code": "NSI",
    "site_number": "02220.*A",
    "fac_type": "AIRPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "NONE",
    "city": "SAN NICOLAS ISLAND",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "SAN NICOLAS ISLAND NOLF",
    "own_type": "MN",
    "longitude": -119.45,
    "latitude": 33.23,
    "elevation": 504,
    "aero_cht": "LOS ANGELES",
    "cbd_dist": 0,
    "cbd_dir": null,
    "act_date": null,
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": null,
    "mil_rts": "Y",
    "cntl_twr": "Y",
    "major": "N"
  },
  {
    "id": 2307,
    "code": "CL77",
    "site_number": "02241.1*A",
    "fac_type": "AIRPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "SANTA CRUZ",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "BONNY DOON VILLAGE",
    "own_type": "PR",
    "longitude": -122.12,
    "latitude": 37.07,
    "elevation": 2020,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 8,
    "cbd_dir": "NW",
    "act_date": "07/1986",
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": null,
    "mil_rts": null,
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 2308,
    "code": "CA37",
    "site_number": "02241.12*H",
    "fac_type": "HELIPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "SANTA CRUZ",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "DOMINICAN SANTA CRUZ HOSPITAL",
    "own_type": "PR",
    "longitude": -121.98,
    "latitude": 36.99,
    "elevation": 115,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 3,
    "cbd_dir": "NE",
    "act_date": null,
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": null,
    "mil_rts": null,
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 1693,
    "code": "6Q6",
    "site_number": "01486.9*A",
    "fac_type": "AIRPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "DAVENPORT",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "LAS TRANCAS",
    "own_type": "PR",
    "longitude": -122.27,
    "latitude": 37.08,
    "elevation": 125,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 6,
    "cbd_dir": "NW",
    "act_date": null,
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": "N",
    "mil_rts": "N",
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 2441,
    "code": "CA65",
    "site_number": "02429.1*H",
    "fac_type": "HELIPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "WATSONVILLE",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "ALTA VISTA",
    "own_type": "PR",
    "longitude": -121.86,
    "latitude": 36.97,
    "elevation": 480,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 5,
    "cbd_dir": "NW",
    "act_date": null,
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": "N",
    "mil_rts": "N",
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 2442,
    "code": "CL99",
    "site_number": "02429.11*H",
    "fac_type": "HELIPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "WATSONVILLE",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "WATSONVILLE COMMUNITY HOSPITAL",
    "own_type": "PU",
    "longitude": -121.77,
    "latitude": 36.93,
    "elevation": 111,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 0,
    "cbd_dir": "E",
    "act_date": "09/1987",
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": null,
    "mil_rts": null,
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 2443,
    "code": "CA66",
    "site_number": "02429.2*A",
    "fac_type": "AIRPORT",
    "fac_use": "PR",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "WATSONVILLE",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "MONTEREY BAY ACADEMY",
    "own_type": "PR",
    "longitude": -121.84,
    "latitude": 36.9,
    "elevation": 70,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 4,
    "cbd_dir": "W",
    "act_date": null,
    "cert": null,
    "fed_agree": null,
    "cust_intl": null,
    "c_ldg_rts": null,
    "joint_use": "N",
    "mil_rts": "N",
    "cntl_twr": "N",
    "major": "N"
  },
  {
    "id": 2440,
    "code": "WVI",
    "site_number": "02429.*A",
    "fac_type": "AIRPORT",
    "fac_use": "PU",
    "faa_region": "AWP",
    "faa_dist": "SFO",
    "city": "WATSONVILLE",
    "county": "SANTA CRUZ",
    "state": "CA",
    "full_name": "WATSONVILLE MUNI",
    "own_type": "PU",
    "longitude": -121.78,
    "latitude": 36.93,
    "elevation": 160,
    "aero_cht": "SAN FRANCISCO",
    "cbd_dist": 3,
    "cbd_dir": "NW",
    "act_date": null,
    "cert": null,
    "fed_agree": "NGPRY",
    "cust_intl": "N",
    "c_ldg_rts": "N",
    "joint_use": "N",
    "mil_rts": "Y",
    "cntl_twr": "N",
    "major": "N"
  }
]

SELECT 
   base."id" as "id",
   base."code" as "code",
   base."site_number" as "site_number",
   base."fac_type" as "fac_type",
   base."fac_use" as "fac_use",
   base."faa_region" as "faa_region",
   base."faa_dist" as "faa_dist",
   base."city" as "city",
   base."county" as "county",
   base."state" as "state",
   base."full_name" as "full_name",
   base."own_type" as "own_type",
   base."longitude" as "longitude",
   base."latitude" as "latitude",
   base."elevation" as "elevation",
   base."aero_cht" as "aero_cht",
   base."cbd_dist" as "cbd_dist",
   base."cbd_dir" as "cbd_dir",
   base."act_date" as "act_date",
   base."cert" as "cert",
   base."fed_agree" as "fed_agree",
   base."cust_intl" as "cust_intl",
   base."c_ldg_rts" as "c_ldg_rts",
   base."joint_use" as "joint_use",
   base."mil_rts" as "mil_rts",
   base."cntl_twr" as "cntl_twr",
   base."major" as "major"
FROM '../data/airports.parquet' as base
WHERE base."county" LIKE 'SANTA CRUZ'

Indexing to show top values for each dimension

It is often difficult to approach a new dataset. The index operator provides an intersting way to quickly gain an understanding of the dataset. By piping the results of an index another stage, we can quickly see all the interesting values for each of the interesting dimesions. Again, the weight shows the number of rows for that particular dimension/value.

run: duckdb.table('../data/airports.parquet') -> {
  index: *
} -> {
  group_by: fieldName
  nest: values is {
    group_by: fieldValue, weight
    order_by: weight desc
    limit: 10
  }
  order_by: fieldName
}

QUERY RESULTS

fieldName

values

act_date

fieldValue	weight
∅	12,040
04/1993	145
02/1987	129
03/1993	127
11/1990	122
11/1986	119
02/1982	118
08/1988	115
03/1988	113
11/1988	113

aero_cht

fieldValue	weight
NEW YORK	1,581
CHICAGO	1,480
DETROIT	1,262
DALLAS-FT WORTH	888
WASHINGTON	887
ST LOUIS	847
ATLANTA	780
HOUSTON	765
SEATTLE	749
KANSAS CITY	713

c_ldg_rts

fieldValue	weight
∅	15,145
N	4,365
Y	283

cbd_dir

fieldValue	weight
N	3,694
NW	2,438
SW	2,427
NE	2,393
SE	2,281
S	2,155
E	2,127
W	2,065
∅	119
SSW	20

cbd_dist

fieldValue	weight
0 to 73	19,793

cert

fieldValue	weight
∅	19,142
AS 05/1973	94
CS 05/1973	84
BS 05/1973	81
LU 05/1973	37
LU 08/1990	33
DS 05/1973	26
AU 05/1973	25
ES 05/1973	15
LU 11/1974	9

city

fieldValue	weight
HOUSTON	108
LOS ANGELES	60
COLUMBUS	47
SPRINGFIELD	45
JACKSON	42
WASHINGTON	38
GREENVILLE	38
PHOENIX	37
CLINTON	37
PHILADELPHIA	35

cntl_twr

fieldValue	weight
N	19,124
Y	669

code

fieldValue	weight
45AK	1
GA52	1
CT03	1
5IS6	1
1IS7	1
27K	1
08LA	1
9LA4	1
IER	1
53U	1

county

fieldValue	weight
WASHINGTON	214
JEFFERSON	199
LOS ANGELES	176
MONTGOMERY	154
JACKSON	140
FRANKLIN	140
HARRIS	137
MARION	127
ORANGE	125
MARICOPA	117

cust_intl

fieldValue	weight
∅	15,145
N	4,575
Y	73

elevation

fieldValue	weight
-1 to 12442	19,793

faa_dist

fieldValue	weight
NONE	7,085
CHI	1,528
DET	1,240
SEA	1,162
MSP	1,046
ATL	1,029
NYC	957
ORL	905
HAR	846
DEN	679

faa_region

fieldValue	weight
AGL	4,437
ASW	3,268
ASO	2,924
AEA	2,586
ANM	2,102
ACE	1,579
AWP	1,503
ANE	763
AAL	608
∅	23

fac_type

fieldValue	weight
AIRPORT	13,925
HELIPORT	5,135
SEAPLANE BASE	473
ULTRALIGHT	125
STOLPORT	86
GLIDERPORT	37
BALLOONPORT	12

fac_use

fieldValue	weight
PR	14,428
PU	5,365

fed_agree

fieldValue	weight
∅	16,252
NGY	1,682
N	515
NGY3	219
NGPY	171
NY1	123
N1	97
1	96
NGSY	87
NGPY3	84

full_name

fieldValue	weight
MEMORIAL HOSPITAL	21
SMITH	14
JOHNSON	13
ST MARY'S HOSPITAL	11
MILLER	10
DAVIS FIELD	10
HILLTOP	10
FLYING W	9
WILSON	9
ST FRANCIS HOSPITAL	9

fieldValue	weight
1 to 19793	19,793

joint_use

fieldValue	weight
∅	14,804
N	4,779
Y	210

latitude

fieldValue	weight
-14.18 to 71.28	19,793

longitude

fieldValue	weight
-100.0 to 174.11	19,793

major

fieldValue	weight
N	19,523
Y	270

mil_rts

fieldValue	weight
∅	14,716
Y	2,958
N	2,119

own_type

fieldValue	weight
PR	14,306
PU	5,174
MR	128
MA	107
MN	78

site_number

fieldValue	weight
50717.1*C	1
00810.*A	1
03111.4*A	1
03570.11*H	1
06260.*A	1
04865.3*A	1
04861.03*A	1
04999.76*A	1
05247.15*H	1
11765.1*H	1

state

fieldValue	weight
TX	1,845
CA	984
IL	890
FL	856
PA	804
OH	749
IN	643
AK	608
NY	576
WI	543

∅

fieldValue	weight
∅	19,793

[
  {
    "fieldName": "act_date",
    "values": [
      {
        "fieldValue": null,
        "weight": 12040
      },
      {
        "fieldValue": "04/1993",
        "weight": 145
      },
      {
        "fieldValue": "02/1987",
        "weight": 129
      },
      {
        "fieldValue": "03/1993",
        "weight": 127
      },
      {
        "fieldValue": "11/1990",
        "weight": 122
      },
      {
        "fieldValue": "11/1986",
        "weight": 119
      },
      {
        "fieldValue": "02/1982",
        "weight": 118
      },
      {
        "fieldValue": "08/1988",
        "weight": 115
      },
      {
        "fieldValue": "03/1988",
        "weight": 113
      },
      {
        "fieldValue": "11/1988",
        "weight": 113
      }
    ]
  },
  {
    "fieldName": "aero_cht",
    "values": [
      {
        "fieldValue": "NEW YORK",
        "weight": 1581
      },
      {
        "fieldValue": "CHICAGO",
        "weight": 1480
      },
      {
        "fieldValue": "DETROIT",
        "weight": 1262
      },
      {
        "fieldValue": "DALLAS-FT WORTH",
        "weight": 888
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 887
      },
      {
        "fieldValue": "ST LOUIS",
        "weight": 847
      },
      {
        "fieldValue": "ATLANTA",
        "weight": 780
      },
      {
        "fieldValue": "HOUSTON",
        "weight": 765
      },
      {
        "fieldValue": "SEATTLE",
        "weight": 749
      },
      {
        "fieldValue": "KANSAS CITY",
        "weight": 713
      }
    ]
  },
  {
    "fieldName": "c_ldg_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 15145
      },
      {
        "fieldValue": "N",
        "weight": 4365
      },
      {
        "fieldValue": "Y",
        "weight": 283
      }
    ]
  },
  {
    "fieldName": "cbd_dir",
    "values": [
      {
        "fieldValue": "N",
        "weight": 3694
      },
      {
        "fieldValue": "NW",
        "weight": 2438
      },
      {
        "fieldValue": "SW",
        "weight": 2427
      },
      {
        "fieldValue": "NE",
        "weight": 2393
      },
      {
        "fieldValue": "SE",
        "weight": 2281
      },
      {
        "fieldValue": "S",
        "weight": 2155
      },
      {
        "fieldValue": "E",
        "weight": 2127
      },
      {
        "fieldValue": "W",
        "weight": 2065
      },
      {
        "fieldValue": null,
        "weight": 119
      },
      {
        "fieldValue": "SSW",
        "weight": 20
      }
    ]
  },
  {
    "fieldName": "cbd_dist",
    "values": [
      {
        "fieldValue": "0 to 73",
        "weight": 19793
      }
    ]
  },
  {
    "fieldName": "cert",
    "values": [
      {
        "fieldValue": null,
        "weight": 19142
      },
      {
        "fieldValue": "AS 05/1973",
        "weight": 94
      },
      {
        "fieldValue": "CS 05/1973",
        "weight": 84
      },
      {
        "fieldValue": "BS 05/1973",
        "weight": 81
      },
      {
        "fieldValue": "LU 05/1973",
        "weight": 37
      },
      {
        "fieldValue": "LU 08/1990",
        "weight": 33
      },
      {
        "fieldValue": "DS 05/1973",
        "weight": 26
      },
      {
        "fieldValue": "AU 05/1973",
        "weight": 25
      },
      {
        "fieldValue": "ES 05/1973",
        "weight": 15
      },
      {
        "fieldValue": "LU 11/1974",
        "weight": 9
      }
    ]
  },
  {
    "fieldName": "city",
    "values": [
      {
        "fieldValue": "HOUSTON",
        "weight": 108
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 60
      },
      {
        "fieldValue": "COLUMBUS",
        "weight": 47
      },
      {
        "fieldValue": "SPRINGFIELD",
        "weight": 45
      },
      {
        "fieldValue": "JACKSON",
        "weight": 42
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 38
      },
      {
        "fieldValue": "GREENVILLE",
        "weight": 38
      },
      {
        "fieldValue": "PHOENIX",
        "weight": 37
      },
      {
        "fieldValue": "CLINTON",
        "weight": 37
      },
      {
        "fieldValue": "PHILADELPHIA",
        "weight": 35
      }
    ]
  },
  {
    "fieldName": "cntl_twr",
    "values": [
      {
        "fieldValue": "N",
        "weight": 19124
      },
      {
        "fieldValue": "Y",
        "weight": 669
      }
    ]
  },
  {
    "fieldName": "code",
    "values": [
      {
        "fieldValue": "45AK",
        "weight": 1
      },
      {
        "fieldValue": "GA52",
        "weight": 1
      },
      {
        "fieldValue": "CT03",
        "weight": 1
      },
      {
        "fieldValue": "5IS6",
        "weight": 1
      },
      {
        "fieldValue": "1IS7",
        "weight": 1
      },
      {
        "fieldValue": "27K",
        "weight": 1
      },
      {
        "fieldValue": "08LA",
        "weight": 1
      },
      {
        "fieldValue": "9LA4",
        "weight": 1
      },
      {
        "fieldValue": "IER",
        "weight": 1
      },
      {
        "fieldValue": "53U",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "county",
    "values": [
      {
        "fieldValue": "WASHINGTON",
        "weight": 214
      },
      {
        "fieldValue": "JEFFERSON",
        "weight": 199
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 176
      },
      {
        "fieldValue": "MONTGOMERY",
        "weight": 154
      },
      {
        "fieldValue": "JACKSON",
        "weight": 140
      },
      {
        "fieldValue": "FRANKLIN",
        "weight": 140
      },
      {
        "fieldValue": "HARRIS",
        "weight": 137
      },
      {
        "fieldValue": "MARION",
        "weight": 127
      },
      {
        "fieldValue": "ORANGE",
        "weight": 125
      },
      {
        "fieldValue": "MARICOPA",
        "weight": 117
      }
    ]
  },
  {
    "fieldName": "cust_intl",
    "values": [
      {
        "fieldValue": null,
        "weight": 15145
      },
      {
        "fieldValue": "N",
        "weight": 4575
      },
      {
        "fieldValue": "Y",
        "weight": 73
      }
    ]
  },
  {
    "fieldName": "elevation",
    "values": [
      {
        "fieldValue": "-1 to 12442",
        "weight": 19793
      }
    ]
  },
  {
    "fieldName": "faa_dist",
    "values": [
      {
        "fieldValue": "NONE",
        "weight": 7085
      },
      {
        "fieldValue": "CHI",
        "weight": 1528
      },
      {
        "fieldValue": "DET",
        "weight": 1240
      },
      {
        "fieldValue": "SEA",
        "weight": 1162
      },
      {
        "fieldValue": "MSP",
        "weight": 1046
      },
      {
        "fieldValue": "ATL",
        "weight": 1029
      },
      {
        "fieldValue": "NYC",
        "weight": 957
      },
      {
        "fieldValue": "ORL",
        "weight": 905
      },
      {
        "fieldValue": "HAR",
        "weight": 846
      },
      {
        "fieldValue": "DEN",
        "weight": 679
      }
    ]
  },
  {
    "fieldName": "faa_region",
    "values": [
      {
        "fieldValue": "AGL",
        "weight": 4437
      },
      {
        "fieldValue": "ASW",
        "weight": 3268
      },
      {
        "fieldValue": "ASO",
        "weight": 2924
      },
      {
        "fieldValue": "AEA",
        "weight": 2586
      },
      {
        "fieldValue": "ANM",
        "weight": 2102
      },
      {
        "fieldValue": "ACE",
        "weight": 1579
      },
      {
        "fieldValue": "AWP",
        "weight": 1503
      },
      {
        "fieldValue": "ANE",
        "weight": 763
      },
      {
        "fieldValue": "AAL",
        "weight": 608
      },
      {
        "fieldValue": null,
        "weight": 23
      }
    ]
  },
  {
    "fieldName": "fac_type",
    "values": [
      {
        "fieldValue": "AIRPORT",
        "weight": 13925
      },
      {
        "fieldValue": "HELIPORT",
        "weight": 5135
      },
      {
        "fieldValue": "SEAPLANE BASE",
        "weight": 473
      },
      {
        "fieldValue": "ULTRALIGHT",
        "weight": 125
      },
      {
        "fieldValue": "STOLPORT",
        "weight": 86
      },
      {
        "fieldValue": "GLIDERPORT",
        "weight": 37
      },
      {
        "fieldValue": "BALLOONPORT",
        "weight": 12
      }
    ]
  },
  {
    "fieldName": "fac_use",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 14428
      },
      {
        "fieldValue": "PU",
        "weight": 5365
      }
    ]
  },
  {
    "fieldName": "fed_agree",
    "values": [
      {
        "fieldValue": null,
        "weight": 16252
      },
      {
        "fieldValue": "NGY",
        "weight": 1682
      },
      {
        "fieldValue": "N",
        "weight": 515
      },
      {
        "fieldValue": "NGY3",
        "weight": 219
      },
      {
        "fieldValue": "NGPY",
        "weight": 171
      },
      {
        "fieldValue": "NY1",
        "weight": 123
      },
      {
        "fieldValue": "N1",
        "weight": 97
      },
      {
        "fieldValue": "1",
        "weight": 96
      },
      {
        "fieldValue": "NGSY",
        "weight": 87
      },
      {
        "fieldValue": "NGPY3",
        "weight": 84
      }
    ]
  },
  {
    "fieldName": "full_name",
    "values": [
      {
        "fieldValue": "MEMORIAL HOSPITAL",
        "weight": 21
      },
      {
        "fieldValue": "SMITH",
        "weight": 14
      },
      {
        "fieldValue": "JOHNSON",
        "weight": 13
      },
      {
        "fieldValue": "ST MARY'S HOSPITAL",
        "weight": 11
      },
      {
        "fieldValue": "MILLER",
        "weight": 10
      },
      {
        "fieldValue": "DAVIS FIELD",
        "weight": 10
      },
      {
        "fieldValue": "HILLTOP",
        "weight": 10
      },
      {
        "fieldValue": "FLYING W",
        "weight": 9
      },
      {
        "fieldValue": "WILSON",
        "weight": 9
      },
      {
        "fieldValue": "ST FRANCIS HOSPITAL",
        "weight": 9
      }
    ]
  },
  {
    "fieldName": "id",
    "values": [
      {
        "fieldValue": "1 to 19793",
        "weight": 19793
      }
    ]
  },
  {
    "fieldName": "joint_use",
    "values": [
      {
        "fieldValue": null,
        "weight": 14804
      },
      {
        "fieldValue": "N",
        "weight": 4779
      },
      {
        "fieldValue": "Y",
        "weight": 210
      }
    ]
  },
  {
    "fieldName": "latitude",
    "values": [
      {
        "fieldValue": "-14.18 to 71.28",
        "weight": 19793
      }
    ]
  },
  {
    "fieldName": "longitude",
    "values": [
      {
        "fieldValue": "-100.0 to 174.11",
        "weight": 19793
      }
    ]
  },
  {
    "fieldName": "major",
    "values": [
      {
        "fieldValue": "N",
        "weight": 19523
      },
      {
        "fieldValue": "Y",
        "weight": 270
      }
    ]
  },
  {
    "fieldName": "mil_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 14716
      },
      {
        "fieldValue": "Y",
        "weight": 2958
      },
      {
        "fieldValue": "N",
        "weight": 2119
      }
    ]
  },
  {
    "fieldName": "own_type",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 14306
      },
      {
        "fieldValue": "PU",
        "weight": 5174
      },
      {
        "fieldValue": "MR",
        "weight": 128
      },
      {
        "fieldValue": "MA",
        "weight": 107
      },
      {
        "fieldValue": "MN",
        "weight": 78
      }
    ]
  },
  {
    "fieldName": "site_number",
    "values": [
      {
        "fieldValue": "50717.1*C",
        "weight": 1
      },
      {
        "fieldValue": "00810.*A",
        "weight": 1
      },
      {
        "fieldValue": "03111.4*A",
        "weight": 1
      },
      {
        "fieldValue": "03570.11*H",
        "weight": 1
      },
      {
        "fieldValue": "06260.*A",
        "weight": 1
      },
      {
        "fieldValue": "04865.3*A",
        "weight": 1
      },
      {
        "fieldValue": "04861.03*A",
        "weight": 1
      },
      {
        "fieldValue": "04999.76*A",
        "weight": 1
      },
      {
        "fieldValue": "05247.15*H",
        "weight": 1
      },
      {
        "fieldValue": "11765.1*H",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "state",
    "values": [
      {
        "fieldValue": "TX",
        "weight": 1845
      },
      {
        "fieldValue": "CA",
        "weight": 984
      },
      {
        "fieldValue": "IL",
        "weight": 890
      },
      {
        "fieldValue": "FL",
        "weight": 856
      },
      {
        "fieldValue": "PA",
        "weight": 804
      },
      {
        "fieldValue": "OH",
        "weight": 749
      },
      {
        "fieldValue": "IN",
        "weight": 643
      },
      {
        "fieldValue": "AK",
        "weight": 608
      },
      {
        "fieldValue": "NY",
        "weight": 576
      },
      {
        "fieldValue": "WI",
        "weight": 543
      }
    ]
  },
  {
    "fieldName": null,
    "values": [
      {
        "fieldValue": null,
        "weight": 19793
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'id'
      WHEN 1 THEN 'code'
      WHEN 2 THEN 'site_number'
      WHEN 3 THEN 'fac_type'
      WHEN 4 THEN 'fac_use'
      WHEN 5 THEN 'faa_region'
      WHEN 6 THEN 'faa_dist'
      WHEN 7 THEN 'city'
      WHEN 8 THEN 'county'
      WHEN 9 THEN 'state'
      WHEN 10 THEN 'full_name'
      WHEN 11 THEN 'own_type'
      WHEN 12 THEN 'longitude'
      WHEN 13 THEN 'latitude'
      WHEN 14 THEN 'elevation'
      WHEN 15 THEN 'aero_cht'
      WHEN 16 THEN 'cbd_dist'
      WHEN 17 THEN 'cbd_dir'
      WHEN 18 THEN 'act_date'
      WHEN 19 THEN 'cert'
      WHEN 20 THEN 'fed_agree'
      WHEN 21 THEN 'cust_intl'
      WHEN 22 THEN 'c_ldg_rts'
      WHEN 23 THEN 'joint_use'
      WHEN 24 THEN 'mil_rts'
      WHEN 25 THEN 'cntl_twr'
      WHEN 26 THEN 'major'
    END as "fieldName",  CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'string'
      WHEN 7 THEN 'string'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'number'
      WHEN 13 THEN 'number'
      WHEN 14 THEN 'number'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'number'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'string'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'string'
      WHEN 21 THEN 'string'
      WHEN 22 THEN 'string'
      WHEN 23 THEN 'string'
      WHEN 24 THEN 'string'
      WHEN 25 THEN 'string'
      WHEN 26 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN NULL
      WHEN 1 THEN base."code"
      WHEN 2 THEN base."site_number"
      WHEN 3 THEN base."fac_type"
      WHEN 4 THEN base."fac_use"
      WHEN 5 THEN base."faa_region"
      WHEN 6 THEN base."faa_dist"
      WHEN 7 THEN base."city"
      WHEN 8 THEN base."county"
      WHEN 9 THEN base."state"
      WHEN 10 THEN base."full_name"
      WHEN 11 THEN base."own_type"
      WHEN 15 THEN base."aero_cht"
      WHEN 17 THEN base."cbd_dir"
      WHEN 18 THEN base."act_date"
      WHEN 19 THEN base."cert"
      WHEN 20 THEN base."fed_agree"
      WHEN 21 THEN base."cust_intl"
      WHEN 22 THEN base."c_ldg_rts"
      WHEN 23 THEN base."joint_use"
      WHEN 24 THEN base."mil_rts"
      WHEN 25 THEN base."cntl_twr"
      WHEN 26 THEN base."major"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 12 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") as VARCHAR)
      WHEN 13 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 14 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 16 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
    END as "fieldRange"
  FROM '../data/airports.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,27,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage0
)
, __stage2 AS (
  SELECT
    group_set,
    base."fieldName" as "fieldName__0",
    CASE WHEN group_set=1 THEN
      base."fieldValue"
      END as "fieldValue__1",
    CASE WHEN group_set=1 THEN
      base."weight"
      END as "weight__1"
  FROM __stage1 as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
SELECT
  "fieldName__0" as "fieldName",
  COALESCE(LIST({
    "fieldValue": "fieldValue__1", 
    "weight": "weight__1"}  ORDER BY  "weight__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:10],[]) as "values"
FROM __stage2
GROUP BY 1
ORDER BY 1 ASC NULLS LAST

Sampling

With large datasets, you can also sample a small subsection using the sample: parameter. Sampled indexes are great at identifing the important low cardinality fields.

run: duckdb.table('../data/airports.parquet') -> {
  index: *
  sample: 5000  // sample only 5000 rows
} -> {
  group_by: fieldName
  nest: values is  {
    group_by: fieldValue, weight
    order_by: weight desc
    limit: 10
  }
  order_by: fieldName
}

QUERY RESULTS

fieldName

values

act_date

fieldValue	weight
∅	3,049
02/1987	40
08/1988	38
04/1993	35
02/1982	35
06/1991	34
11/1988	34
11/1990	33
11/1986	33
03/1993	31

aero_cht

fieldValue	weight
NEW YORK	422
CHICAGO	390
DETROIT	300
WASHINGTON	248
DALLAS-FT WORTH	228
ST LOUIS	219
TWIN CITIES	201
SEATTLE	192
ATLANTA	183
HOUSTON	183

c_ldg_rts

fieldValue	weight
∅	3,818
N	1,117
Y	65

cbd_dir

fieldValue	weight
N	895
NW	618
SE	608
SW	599
W	562
S	561
NE	557
E	554
∅	29
ESE	4

cbd_dist

fieldValue	weight
0 to 63	5,000

cert

fieldValue	weight
∅	4,845
AS 05/1973	24
CS 05/1973	17
BS 05/1973	14
LU 05/1973	12
ES 05/1973	7
DS 05/1973	7
LU 08/1990	5
LU 11/1974	4
AU 05/1973	4

city

fieldValue	weight
HOUSTON	20
JACKSON	14
LOS ANGELES	13
SAN ANTONIO	12
DALLAS	12
PHOENIX	11
OKLAHOMA CITY	11
SPRINGFIELD	11
ALBANY	10
MARION	10

cntl_twr

fieldValue	weight
N	4,839
Y	161

code

fieldValue	weight
31IN	1
8M8	1
6K5	1
OL12	1
27OH	1
1MO8	1
TN03	1
PN16	1
DTW	1
ND69	1

county

fieldValue	weight
WASHINGTON	55
JEFFERSON	49
LOS ANGELES	45
MONTGOMERY	45
FRANKLIN	40
JACKSON	36
MARION	31
ADAMS	30
ORANGE	30
WAYNE	29

cust_intl

fieldValue	weight
∅	3,818
N	1,161
Y	21

elevation

fieldValue	weight
-128 to 12442	5,000

faa_dist

fieldValue	weight
NONE	1,767
CHI	412
SEA	290
DET	288
MSP	283
ATL	278
NYC	250
HAR	229
ORL	202
DCA	181

faa_region

fieldValue	weight
AGL	1,144
ASW	816
ASO	710
AEA	692
ANM	522
ACE	403
AWP	364
ANE	202
AAL	142
∅	5

fac_type

fieldValue	weight
AIRPORT	3,527
HELIPORT	1,285
SEAPLANE BASE	124
ULTRALIGHT	35
STOLPORT	17
BALLOONPORT	6
GLIDERPORT	6

fac_use

fieldValue	weight
PR	3,638
PU	1,362

fed_agree

fieldValue	weight
∅	4,111
NGY	416
N	132
NGY3	52
NGPY	44
NY1	31
1	31
NGPY3	26
N1	24
NGSY	20

full_name

fieldValue	weight
SMITH	5
JOHNSON	5
MEMORIAL HOSPITAL	4
WILLIAMS	4
RUSSELL	4
ST JOSEPH'S HOSPITAL	4
HILLTOP	4
ST FRANCIS HOSPITAL	3
COTTONWOOD	3
EAGLES NEST	3

fieldValue	weight
10002 to 19787	5,000

joint_use

fieldValue	weight
∅	3,737
N	1,207
Y	56

latitude

fieldValue	weight
10.28 to 70.91	5,000

longitude

fieldValue	weight
-100.01 to 171.23	5,000

major

fieldValue	weight
N	4,941
Y	59

mil_rts

fieldValue	weight
∅	3,716
Y	727
N	557

own_type

fieldValue	weight
PR	3,634
PU	1,292
MR	36
MN	20
MA	18

site_number

fieldValue	weight
05412.01*A	1
01975.1*A	1
25398.1*C	1
23760.81*A	1
18722.23*A	1
15695.*H	1
22888.3*A	1
04982.12*A	1
25893.*A	1
06728.5*A	1

state

fieldValue	weight
TX	439
CA	242
IL	229
PA	218
FL	191
IN	184
OH	164
NY	145
MN	144
AK	142

∅

fieldValue	weight
∅	5,000

[
  {
    "fieldName": "act_date",
    "values": [
      {
        "fieldValue": null,
        "weight": 3049
      },
      {
        "fieldValue": "02/1987",
        "weight": 40
      },
      {
        "fieldValue": "08/1988",
        "weight": 38
      },
      {
        "fieldValue": "04/1993",
        "weight": 35
      },
      {
        "fieldValue": "02/1982",
        "weight": 35
      },
      {
        "fieldValue": "06/1991",
        "weight": 34
      },
      {
        "fieldValue": "11/1988",
        "weight": 34
      },
      {
        "fieldValue": "11/1990",
        "weight": 33
      },
      {
        "fieldValue": "11/1986",
        "weight": 33
      },
      {
        "fieldValue": "03/1993",
        "weight": 31
      }
    ]
  },
  {
    "fieldName": "aero_cht",
    "values": [
      {
        "fieldValue": "NEW YORK",
        "weight": 422
      },
      {
        "fieldValue": "CHICAGO",
        "weight": 390
      },
      {
        "fieldValue": "DETROIT",
        "weight": 300
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 248
      },
      {
        "fieldValue": "DALLAS-FT WORTH",
        "weight": 228
      },
      {
        "fieldValue": "ST LOUIS",
        "weight": 219
      },
      {
        "fieldValue": "TWIN CITIES",
        "weight": 201
      },
      {
        "fieldValue": "SEATTLE",
        "weight": 192
      },
      {
        "fieldValue": "ATLANTA",
        "weight": 183
      },
      {
        "fieldValue": "HOUSTON",
        "weight": 183
      }
    ]
  },
  {
    "fieldName": "c_ldg_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3818
      },
      {
        "fieldValue": "N",
        "weight": 1117
      },
      {
        "fieldValue": "Y",
        "weight": 65
      }
    ]
  },
  {
    "fieldName": "cbd_dir",
    "values": [
      {
        "fieldValue": "N",
        "weight": 895
      },
      {
        "fieldValue": "NW",
        "weight": 618
      },
      {
        "fieldValue": "SE",
        "weight": 608
      },
      {
        "fieldValue": "SW",
        "weight": 599
      },
      {
        "fieldValue": "W",
        "weight": 562
      },
      {
        "fieldValue": "S",
        "weight": 561
      },
      {
        "fieldValue": "NE",
        "weight": 557
      },
      {
        "fieldValue": "E",
        "weight": 554
      },
      {
        "fieldValue": null,
        "weight": 29
      },
      {
        "fieldValue": "ESE",
        "weight": 4
      }
    ]
  },
  {
    "fieldName": "cbd_dist",
    "values": [
      {
        "fieldValue": "0 to 63",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "cert",
    "values": [
      {
        "fieldValue": null,
        "weight": 4845
      },
      {
        "fieldValue": "AS 05/1973",
        "weight": 24
      },
      {
        "fieldValue": "CS 05/1973",
        "weight": 17
      },
      {
        "fieldValue": "BS 05/1973",
        "weight": 14
      },
      {
        "fieldValue": "LU 05/1973",
        "weight": 12
      },
      {
        "fieldValue": "ES 05/1973",
        "weight": 7
      },
      {
        "fieldValue": "DS 05/1973",
        "weight": 7
      },
      {
        "fieldValue": "LU 08/1990",
        "weight": 5
      },
      {
        "fieldValue": "LU 11/1974",
        "weight": 4
      },
      {
        "fieldValue": "AU 05/1973",
        "weight": 4
      }
    ]
  },
  {
    "fieldName": "city",
    "values": [
      {
        "fieldValue": "HOUSTON",
        "weight": 20
      },
      {
        "fieldValue": "JACKSON",
        "weight": 14
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 13
      },
      {
        "fieldValue": "SAN ANTONIO",
        "weight": 12
      },
      {
        "fieldValue": "DALLAS",
        "weight": 12
      },
      {
        "fieldValue": "PHOENIX",
        "weight": 11
      },
      {
        "fieldValue": "OKLAHOMA CITY",
        "weight": 11
      },
      {
        "fieldValue": "SPRINGFIELD",
        "weight": 11
      },
      {
        "fieldValue": "ALBANY",
        "weight": 10
      },
      {
        "fieldValue": "MARION",
        "weight": 10
      }
    ]
  },
  {
    "fieldName": "cntl_twr",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4839
      },
      {
        "fieldValue": "Y",
        "weight": 161
      }
    ]
  },
  {
    "fieldName": "code",
    "values": [
      {
        "fieldValue": "31IN",
        "weight": 1
      },
      {
        "fieldValue": "8M8",
        "weight": 1
      },
      {
        "fieldValue": "6K5",
        "weight": 1
      },
      {
        "fieldValue": "OL12",
        "weight": 1
      },
      {
        "fieldValue": "27OH",
        "weight": 1
      },
      {
        "fieldValue": "1MO8",
        "weight": 1
      },
      {
        "fieldValue": "TN03",
        "weight": 1
      },
      {
        "fieldValue": "PN16",
        "weight": 1
      },
      {
        "fieldValue": "DTW",
        "weight": 1
      },
      {
        "fieldValue": "ND69",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "county",
    "values": [
      {
        "fieldValue": "WASHINGTON",
        "weight": 55
      },
      {
        "fieldValue": "JEFFERSON",
        "weight": 49
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 45
      },
      {
        "fieldValue": "MONTGOMERY",
        "weight": 45
      },
      {
        "fieldValue": "FRANKLIN",
        "weight": 40
      },
      {
        "fieldValue": "JACKSON",
        "weight": 36
      },
      {
        "fieldValue": "MARION",
        "weight": 31
      },
      {
        "fieldValue": "ADAMS",
        "weight": 30
      },
      {
        "fieldValue": "ORANGE",
        "weight": 30
      },
      {
        "fieldValue": "WAYNE",
        "weight": 29
      }
    ]
  },
  {
    "fieldName": "cust_intl",
    "values": [
      {
        "fieldValue": null,
        "weight": 3818
      },
      {
        "fieldValue": "N",
        "weight": 1161
      },
      {
        "fieldValue": "Y",
        "weight": 21
      }
    ]
  },
  {
    "fieldName": "elevation",
    "values": [
      {
        "fieldValue": "-128 to 12442",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "faa_dist",
    "values": [
      {
        "fieldValue": "NONE",
        "weight": 1767
      },
      {
        "fieldValue": "CHI",
        "weight": 412
      },
      {
        "fieldValue": "SEA",
        "weight": 290
      },
      {
        "fieldValue": "DET",
        "weight": 288
      },
      {
        "fieldValue": "MSP",
        "weight": 283
      },
      {
        "fieldValue": "ATL",
        "weight": 278
      },
      {
        "fieldValue": "NYC",
        "weight": 250
      },
      {
        "fieldValue": "HAR",
        "weight": 229
      },
      {
        "fieldValue": "ORL",
        "weight": 202
      },
      {
        "fieldValue": "DCA",
        "weight": 181
      }
    ]
  },
  {
    "fieldName": "faa_region",
    "values": [
      {
        "fieldValue": "AGL",
        "weight": 1144
      },
      {
        "fieldValue": "ASW",
        "weight": 816
      },
      {
        "fieldValue": "ASO",
        "weight": 710
      },
      {
        "fieldValue": "AEA",
        "weight": 692
      },
      {
        "fieldValue": "ANM",
        "weight": 522
      },
      {
        "fieldValue": "ACE",
        "weight": 403
      },
      {
        "fieldValue": "AWP",
        "weight": 364
      },
      {
        "fieldValue": "ANE",
        "weight": 202
      },
      {
        "fieldValue": "AAL",
        "weight": 142
      },
      {
        "fieldValue": null,
        "weight": 5
      }
    ]
  },
  {
    "fieldName": "fac_type",
    "values": [
      {
        "fieldValue": "AIRPORT",
        "weight": 3527
      },
      {
        "fieldValue": "HELIPORT",
        "weight": 1285
      },
      {
        "fieldValue": "SEAPLANE BASE",
        "weight": 124
      },
      {
        "fieldValue": "ULTRALIGHT",
        "weight": 35
      },
      {
        "fieldValue": "STOLPORT",
        "weight": 17
      },
      {
        "fieldValue": "BALLOONPORT",
        "weight": 6
      },
      {
        "fieldValue": "GLIDERPORT",
        "weight": 6
      }
    ]
  },
  {
    "fieldName": "fac_use",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3638
      },
      {
        "fieldValue": "PU",
        "weight": 1362
      }
    ]
  },
  {
    "fieldName": "fed_agree",
    "values": [
      {
        "fieldValue": null,
        "weight": 4111
      },
      {
        "fieldValue": "NGY",
        "weight": 416
      },
      {
        "fieldValue": "N",
        "weight": 132
      },
      {
        "fieldValue": "NGY3",
        "weight": 52
      },
      {
        "fieldValue": "NGPY",
        "weight": 44
      },
      {
        "fieldValue": "NY1",
        "weight": 31
      },
      {
        "fieldValue": "1",
        "weight": 31
      },
      {
        "fieldValue": "NGPY3",
        "weight": 26
      },
      {
        "fieldValue": "N1",
        "weight": 24
      },
      {
        "fieldValue": "NGSY",
        "weight": 20
      }
    ]
  },
  {
    "fieldName": "full_name",
    "values": [
      {
        "fieldValue": "SMITH",
        "weight": 5
      },
      {
        "fieldValue": "JOHNSON",
        "weight": 5
      },
      {
        "fieldValue": "MEMORIAL HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "WILLIAMS",
        "weight": 4
      },
      {
        "fieldValue": "RUSSELL",
        "weight": 4
      },
      {
        "fieldValue": "ST JOSEPH'S HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "HILLTOP",
        "weight": 4
      },
      {
        "fieldValue": "ST FRANCIS HOSPITAL",
        "weight": 3
      },
      {
        "fieldValue": "COTTONWOOD",
        "weight": 3
      },
      {
        "fieldValue": "EAGLES NEST",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "id",
    "values": [
      {
        "fieldValue": "10002 to 19787",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "joint_use",
    "values": [
      {
        "fieldValue": null,
        "weight": 3737
      },
      {
        "fieldValue": "N",
        "weight": 1207
      },
      {
        "fieldValue": "Y",
        "weight": 56
      }
    ]
  },
  {
    "fieldName": "latitude",
    "values": [
      {
        "fieldValue": "10.28 to 70.91",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "longitude",
    "values": [
      {
        "fieldValue": "-100.01 to 171.23",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "major",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4941
      },
      {
        "fieldValue": "Y",
        "weight": 59
      }
    ]
  },
  {
    "fieldName": "mil_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3716
      },
      {
        "fieldValue": "Y",
        "weight": 727
      },
      {
        "fieldValue": "N",
        "weight": 557
      }
    ]
  },
  {
    "fieldName": "own_type",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3634
      },
      {
        "fieldValue": "PU",
        "weight": 1292
      },
      {
        "fieldValue": "MR",
        "weight": 36
      },
      {
        "fieldValue": "MN",
        "weight": 20
      },
      {
        "fieldValue": "MA",
        "weight": 18
      }
    ]
  },
  {
    "fieldName": "site_number",
    "values": [
      {
        "fieldValue": "05412.01*A",
        "weight": 1
      },
      {
        "fieldValue": "01975.1*A",
        "weight": 1
      },
      {
        "fieldValue": "25398.1*C",
        "weight": 1
      },
      {
        "fieldValue": "23760.81*A",
        "weight": 1
      },
      {
        "fieldValue": "18722.23*A",
        "weight": 1
      },
      {
        "fieldValue": "15695.*H",
        "weight": 1
      },
      {
        "fieldValue": "22888.3*A",
        "weight": 1
      },
      {
        "fieldValue": "04982.12*A",
        "weight": 1
      },
      {
        "fieldValue": "25893.*A",
        "weight": 1
      },
      {
        "fieldValue": "06728.5*A",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "state",
    "values": [
      {
        "fieldValue": "TX",
        "weight": 439
      },
      {
        "fieldValue": "CA",
        "weight": 242
      },
      {
        "fieldValue": "IL",
        "weight": 229
      },
      {
        "fieldValue": "PA",
        "weight": 218
      },
      {
        "fieldValue": "FL",
        "weight": 191
      },
      {
        "fieldValue": "IN",
        "weight": 184
      },
      {
        "fieldValue": "OH",
        "weight": 164
      },
      {
        "fieldValue": "NY",
        "weight": 145
      },
      {
        "fieldValue": "MN",
        "weight": 144
      },
      {
        "fieldValue": "AK",
        "weight": 142
      }
    ]
  },
  {
    "fieldName": null,
    "values": [
      {
        "fieldValue": null,
        "weight": 5000
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT * from (SELECT * FROM '../data/airports.parquet' USING SAMPLE 5000) as x limit 100000 )
, __stage1 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'id'
      WHEN 1 THEN 'code'
      WHEN 2 THEN 'site_number'
      WHEN 3 THEN 'fac_type'
      WHEN 4 THEN 'fac_use'
      WHEN 5 THEN 'faa_region'
      WHEN 6 THEN 'faa_dist'
      WHEN 7 THEN 'city'
      WHEN 8 THEN 'county'
      WHEN 9 THEN 'state'
      WHEN 10 THEN 'full_name'
      WHEN 11 THEN 'own_type'
      WHEN 12 THEN 'longitude'
      WHEN 13 THEN 'latitude'
      WHEN 14 THEN 'elevation'
      WHEN 15 THEN 'aero_cht'
      WHEN 16 THEN 'cbd_dist'
      WHEN 17 THEN 'cbd_dir'
      WHEN 18 THEN 'act_date'
      WHEN 19 THEN 'cert'
      WHEN 20 THEN 'fed_agree'
      WHEN 21 THEN 'cust_intl'
      WHEN 22 THEN 'c_ldg_rts'
      WHEN 23 THEN 'joint_use'
      WHEN 24 THEN 'mil_rts'
      WHEN 25 THEN 'cntl_twr'
      WHEN 26 THEN 'major'
    END as "fieldName",  CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'string'
      WHEN 7 THEN 'string'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'number'
      WHEN 13 THEN 'number'
      WHEN 14 THEN 'number'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'number'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'string'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'string'
      WHEN 21 THEN 'string'
      WHEN 22 THEN 'string'
      WHEN 23 THEN 'string'
      WHEN 24 THEN 'string'
      WHEN 25 THEN 'string'
      WHEN 26 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN NULL
      WHEN 1 THEN base."code"
      WHEN 2 THEN base."site_number"
      WHEN 3 THEN base."fac_type"
      WHEN 4 THEN base."fac_use"
      WHEN 5 THEN base."faa_region"
      WHEN 6 THEN base."faa_dist"
      WHEN 7 THEN base."city"
      WHEN 8 THEN base."county"
      WHEN 9 THEN base."state"
      WHEN 10 THEN base."full_name"
      WHEN 11 THEN base."own_type"
      WHEN 15 THEN base."aero_cht"
      WHEN 17 THEN base."cbd_dir"
      WHEN 18 THEN base."act_date"
      WHEN 19 THEN base."cert"
      WHEN 20 THEN base."fed_agree"
      WHEN 21 THEN base."cust_intl"
      WHEN 22 THEN base."c_ldg_rts"
      WHEN 23 THEN base."joint_use"
      WHEN 24 THEN base."mil_rts"
      WHEN 25 THEN base."cntl_twr"
      WHEN 26 THEN base."major"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 12 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") as VARCHAR)
      WHEN 13 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 14 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 16 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
    END as "fieldRange"
  FROM __stage0 as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,27,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
, __stage2 AS (
  SELECT
    "fieldName",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage1
)
, __stage3 AS (
  SELECT
    group_set,
    base."fieldName" as "fieldName__0",
    CASE WHEN group_set=1 THEN
      base."fieldValue"
      END as "fieldValue__1",
    CASE WHEN group_set=1 THEN
      base."weight"
      END as "weight__1"
  FROM __stage2 as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
SELECT
  "fieldName__0" as "fieldName",
  COALESCE(LIST({
    "fieldValue": "fieldValue__1", 
    "weight": "weight__1"}  ORDER BY  "weight__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:10],[]) as "values"
FROM __stage3
GROUP BY 1
ORDER BY 1 ASC NULLS LAST

A More Complex Example

The rest of this pages uses the model below. The data is an excerpt from the IMDB. The Malloy schema for this model is shown on the right. The core value is movies, but joined at the principals (the people that worked on the movie) and the people (the actual data about the individuals).

We use the measure total_ratings to determin a movie's popularity. An individual's popularity is determined by the some of all the ratings of the movies a person has worked on.

source: movies is duckdb.table('../data/titles.parquet') extend {
  join_many: principals is duckdb.table('../data/principals.parquet') {
    join_one: people is duckdb.table('../data/names.parquet') 
      on nconst = people.nconst
  } on tconst = principals.tconst
  measure: total_ratings is numVotes.sum()
}

Weights can be any measure

Often a row count will work nicely as a weight, but sometimes there is something better. In movies, for example the sum of the number of votes will not only find the interesting most interesting movies but will also find the most interesting people. For example the most interesting people in the dataset.

run: movies -> {
  group_by: principals.people.primaryName
  aggregate: total_ratings
}

QUERY RESULTS

primaryName	total_ratings
Brad Pitt	18,269,386
Stan Lee	18,229,893
John Williams	17,651,379
Leonardo DiCaprio	17,032,662
Tom Hanks	16,650,835
Christopher Nolan	15,368,415
Steven Spielberg	15,069,082
Robert De Niro	14,629,861
Christian Bale	13,730,681
Samuel L. Jackson	13,423,134
Robert Downey Jr.	12,685,331
George Lucas	12,584,331
Matt Damon	12,509,393
Quentin Tarantino	12,348,394
Bruce Willis	12,045,753
Jack Kirby	11,901,244
Johnny Depp	11,755,228
Morgan Freeman	11,435,905
Tom Cruise	11,290,573
Thomas Newman	11,056,371
Hans Zimmer	10,957,053
Scott Rudin	10,793,692
Charles Roven	10,778,794
Bob Kane	10,685,405
Ian McKellen	10,552,024
Chris Evans	10,497,785
Harrison Ford	10,449,365
Scarlett Johansson	10,128,373
David S. Goyer	10,022,517
Jonathan Nolan	9,823,211
Martin Scorsese	9,692,050
Peter Jackson	9,612,306
Orlando Bloom	9,482,690
James Newton Howard	9,473,293
Stephen King	9,454,020
Hugh Jackman	9,409,702
Kevin Feige	9,408,976
Ridley Scott	9,334,292
Natalie Portman	9,206,903
Liam Neeson	9,185,450
David Heyman	9,174,472
Mark Ruffalo	9,171,115
Bradley Cooper	8,980,668
Will Smith	8,960,562
Al Pacino	8,933,776
Emma Thomas	8,773,124
Tim Bevan	8,756,327
Roger Deakins	8,737,852
Fran Walsh	8,594,296
Michael Caine	8,533,028
Lorne Orleans	8,518,672
Lawrence Bender	8,503,500
Keanu Reeves	8,480,149
David Fincher	8,427,910
Mark Wahlberg	8,393,193
Joel Silver	8,360,819
Robert Richardson	8,351,268
Philippa Boyens	8,336,233
Kathleen Kennedy	8,278,785
Matthew McConaughey	8,271,368
Ben Affleck	8,268,036
James Horner	8,253,828
Alan Silvestri	8,072,953
Chris Hemsworth	8,058,188
Carter Burwell	7,997,536
Simon Kinberg	7,846,138
Eric Fellner	7,845,245
J.R.R. Tolkien	7,798,667
Jake Gyllenhaal	7,762,048
Jerry Bruckheimer	7,721,877
Edward Norton	7,713,816
Woody Harrelson	7,711,063
Ewan McGregor	7,655,458
George Clooney	7,653,368
David Benioff	7,637,309
Kevin Spacey	7,603,094
Brian Grazer	7,570,625
Anne Hathaway	7,509,220
Arnon Milchan	7,494,607
Jim Carrey	7,421,247
Robert Zemeckis	7,371,523
Danny Elfman	7,340,555
Ryan Reynolds	7,319,537
Jason Blum	7,308,653
James Cameron	7,306,015
Sally Menke	7,239,769
Tom Hardy	7,213,428
Clint Eastwood	7,209,434
Russell Crowe	7,166,573
Akiva Goldsman	7,094,797
Nicolas Cage	7,053,952
Michael Giacchino	7,047,025
Gary Oldman	7,036,991
Cate Blanchett	7,017,425
Michael Bay	7,016,200
J.J. Abrams	6,991,513
Willem Dafoe	6,924,815
Christopher Markus	6,886,318
Stephen McFeely	6,886,318
Neal H. Moritz	6,870,329
Alexandre Desplat	6,867,309
Jennifer Lawrence	6,855,625
Rachel McAdams	6,777,091
Lena Headey	6,742,472
Elijah Wood	6,734,358
Dwayne Johnson	6,584,416
Jason Statham	6,582,293
Steve Kloves	6,559,963
Denzel Washington	6,556,554
Sigourney Weaver	6,515,096
Arnold Schwarzenegger	6,482,925
Tim Burton	6,467,281
Steve Carell	6,436,581
Terry Rossio	6,423,369
Ceán Chaffin	6,415,865
Amy Adams	6,401,556
Daniel Radcliffe	6,393,663
J.K. Rowling	6,392,020
Mel Gibson	6,322,650
Anthony Hopkins	6,315,913
Emma Watson	6,304,351
∅	6,300,267
Rick McCallum	6,211,607
Jack Nicholson	6,180,622
Ted Elliott	6,136,354
Adam Sandler	6,094,935
Ed Harris	6,077,615
Chris Columbus	6,076,896
Cameron Diaz	6,042,333
Joseph Gordon-Levitt	6,034,397
Emilia Clarke	5,995,245
D.B. Weiss	5,972,128
Joaquin Phoenix	5,967,875
Daniel Craig	5,967,704
Luc Besson	5,961,182
Ryan Gosling	5,956,376
Vin Diesel	5,944,056
Andrew Stanton	5,942,167
John Goodman	5,940,986
Ralph Fiennes	5,906,715
Peter Dinklage	5,906,118
David Koepp	5,894,926
Pete Docter	5,862,141
Sylvester Stallone	5,861,981
Angelina Jolie	5,857,481
Ian Bryce	5,848,379
Hugo Weaving	5,841,890
Frank Darabont	5,826,908
Ben Kingsley	5,825,798
Viggo Mortensen	5,823,719
Richard Francis-Bruce	5,798,818
Colin Farrell	5,788,835
Charlize Theron	5,765,794
Joss Whedon	5,757,097
Nikolaj Coster-Waldau	5,749,486
Julianne Moore	5,744,632
Jonah Hill	5,718,674
Joel Coen	5,700,458
Zack Snyder	5,695,090
Ben Stiller	5,685,709
Bryan Cranston	5,685,238
Kate Winslet	5,682,918
Ethan Coen	5,663,547
Walter F. Parkes	5,657,162
Howard Shore	5,638,214
Jeremy Renner	5,623,966
Marco Beltrami	5,590,317
Bryan Singer	5,590,237
Emma Stone	5,583,422
Guillermo del Toro	5,570,240
Owen Wilson	5,556,539
Rupert Grint	5,552,590
Jude Law	5,538,884
Matthew Vaughn	5,524,297
Alex Kurtzman	5,522,344
Lana Wachowski	5,518,169
Keira Knightley	5,512,152
Lilly Wachowski	5,444,217
Judd Apatow	5,443,590
Chris Pratt	5,439,043
Francis Ford Coppola	5,436,313
Will Ferrell	5,420,626
Seth Rogen	5,419,570
Sandra Bullock	5,405,221
Steve Ditko	5,371,488
Frank Marshall	5,363,210
John Logan	5,353,165
Ken Watanabe	5,352,344
Sam Raimi	5,334,765
Carrie-Anne Moss	5,305,827
Benedict Cumberbatch	5,273,615
Robin Williams	5,224,218
Jennifer Connelly	5,222,568
Ron Howard	5,215,903
John Travolta	5,206,564
Laurence Fishburne	5,187,514
Ethan Hawke	5,186,955
Jamie Foxx	5,180,837
Lawrence Kasdan	5,173,484
Gerard Butler	5,156,049

[
  {
    "primaryName": "Brad Pitt",
    "total_ratings": 18269386
  },
  {
    "primaryName": "Stan Lee",
    "total_ratings": 18229893
  },
  {
    "primaryName": "John Williams",
    "total_ratings": 17651379
  },
  {
    "primaryName": "Leonardo DiCaprio",
    "total_ratings": 17032662
  },
  {
    "primaryName": "Tom Hanks",
    "total_ratings": 16650835
  },
  {
    "primaryName": "Christopher Nolan",
    "total_ratings": 15368415
  },
  {
    "primaryName": "Steven Spielberg",
    "total_ratings": 15069082
  },
  {
    "primaryName": "Robert De Niro",
    "total_ratings": 14629861
  },
  {
    "primaryName": "Christian Bale",
    "total_ratings": 13730681
  },
  {
    "primaryName": "Samuel L. Jackson",
    "total_ratings": 13423134
  },
  {
    "primaryName": "Robert Downey Jr.",
    "total_ratings": 12685331
  },
  {
    "primaryName": "George Lucas",
    "total_ratings": 12584331
  },
  {
    "primaryName": "Matt Damon",
    "total_ratings": 12509393
  },
  {
    "primaryName": "Quentin Tarantino",
    "total_ratings": 12348394
  },
  {
    "primaryName": "Bruce Willis",
    "total_ratings": 12045753
  },
  {
    "primaryName": "Jack Kirby",
    "total_ratings": 11901244
  },
  {
    "primaryName": "Johnny Depp",
    "total_ratings": 11755228
  },
  {
    "primaryName": "Morgan Freeman",
    "total_ratings": 11435905
  },
  {
    "primaryName": "Tom Cruise",
    "total_ratings": 11290573
  },
  {
    "primaryName": "Thomas Newman",
    "total_ratings": 11056371
  },
  {
    "primaryName": "Hans Zimmer",
    "total_ratings": 10957053
  },
  {
    "primaryName": "Scott Rudin",
    "total_ratings": 10793692
  },
  {
    "primaryName": "Charles Roven",
    "total_ratings": 10778794
  },
  {
    "primaryName": "Bob Kane",
    "total_ratings": 10685405
  },
  {
    "primaryName": "Ian McKellen",
    "total_ratings": 10552024
  },
  {
    "primaryName": "Chris Evans",
    "total_ratings": 10497785
  },
  {
    "primaryName": "Harrison Ford",
    "total_ratings": 10449365
  },
  {
    "primaryName": "Scarlett Johansson",
    "total_ratings": 10128373
  },
  {
    "primaryName": "David S. Goyer",
    "total_ratings": 10022517
  },
  {
    "primaryName": "Jonathan Nolan",
    "total_ratings": 9823211
  },
  {
    "primaryName": "Martin Scorsese",
    "total_ratings": 9692050
  },
  {
    "primaryName": "Peter Jackson",
    "total_ratings": 9612306
  },
  {
    "primaryName": "Orlando Bloom",
    "total_ratings": 9482690
  },
  {
    "primaryName": "James Newton Howard",
    "total_ratings": 9473293
  },
  {
    "primaryName": "Stephen King",
    "total_ratings": 9454020
  },
  {
    "primaryName": "Hugh Jackman",
    "total_ratings": 9409702
  },
  {
    "primaryName": "Kevin Feige",
    "total_ratings": 9408976
  },
  {
    "primaryName": "Ridley Scott",
    "total_ratings": 9334292
  },
  {
    "primaryName": "Natalie Portman",
    "total_ratings": 9206903
  },
  {
    "primaryName": "Liam Neeson",
    "total_ratings": 9185450
  },
  {
    "primaryName": "David Heyman",
    "total_ratings": 9174472
  },
  {
    "primaryName": "Mark Ruffalo",
    "total_ratings": 9171115
  },
  {
    "primaryName": "Bradley Cooper",
    "total_ratings": 8980668
  },
  {
    "primaryName": "Will Smith",
    "total_ratings": 8960562
  },
  {
    "primaryName": "Al Pacino",
    "total_ratings": 8933776
  },
  {
    "primaryName": "Emma Thomas",
    "total_ratings": 8773124
  },
  {
    "primaryName": "Tim Bevan",
    "total_ratings": 8756327
  },
  {
    "primaryName": "Roger Deakins",
    "total_ratings": 8737852
  },
  {
    "primaryName": "Fran Walsh",
    "total_ratings": 8594296
  },
  {
    "primaryName": "Michael Caine",
    "total_ratings": 8533028
  },
  {
    "primaryName": "Lorne Orleans",
    "total_ratings": 8518672
  },
  {
    "primaryName": "Lawrence Bender",
    "total_ratings": 8503500
  },
  {
    "primaryName": "Keanu Reeves",
    "total_ratings": 8480149
  },
  {
    "primaryName": "David Fincher",
    "total_ratings": 8427910
  },
  {
    "primaryName": "Mark Wahlberg",
    "total_ratings": 8393193
  },
  {
    "primaryName": "Joel Silver",
    "total_ratings": 8360819
  },
  {
    "primaryName": "Robert Richardson",
    "total_ratings": 8351268
  },
  {
    "primaryName": "Philippa Boyens",
    "total_ratings": 8336233
  },
  {
    "primaryName": "Kathleen Kennedy",
    "total_ratings": 8278785
  },
  {
    "primaryName": "Matthew McConaughey",
    "total_ratings": 8271368
  },
  {
    "primaryName": "Ben Affleck",
    "total_ratings": 8268036
  },
  {
    "primaryName": "James Horner",
    "total_ratings": 8253828
  },
  {
    "primaryName": "Alan Silvestri",
    "total_ratings": 8072953
  },
  {
    "primaryName": "Chris Hemsworth",
    "total_ratings": 8058188
  },
  {
    "primaryName": "Carter Burwell",
    "total_ratings": 7997536
  },
  {
    "primaryName": "Simon Kinberg",