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	fieldPath	fieldValue	fieldType	weight
act_date	act_date	∅	string	12,040
act_date	act_date	02/2000	string	54
act_date	act_date	11/1986	string	119
act_date	act_date	11/1992	string	92
act_date	act_date	10/1999	string	41
act_date	act_date	12/1992	string	86
act_date	act_date	09/1994	string	20
act_date	act_date	08/1990	string	36
act_date	act_date	01/1990	string	15
act_date	act_date	08/1981	string	40
act_date	act_date	12/1986	string	56
act_date	act_date	11/1987	string	25
act_date	act_date	04/1996	string	3
act_date	act_date	01/1994	string	36
act_date	act_date	04/1999	string	45
act_date	act_date	03/1992	string	17
act_date	act_date	07/1995	string	15
act_date	act_date	04/1982	string	92
act_date	act_date	05/1991	string	31
act_date	act_date	03/1998	string	62
act_date	act_date	01/1987	string	31
act_date	act_date	05/1993	string	8
act_date	act_date	02/1987	string	129
act_date	act_date	10/1987	string	62
act_date	act_date	09/1999	string	31
act_date	act_date	07/1982	string	80
act_date	act_date	01/1998	string	28
act_date	act_date	04/1990	string	16
act_date	act_date	12/1983	string	77
act_date	act_date	12/1997	string	31
act_date	act_date	09/1990	string	11
act_date	act_date	07/1983	string	20
act_date	act_date	09/1991	string	14
act_date	act_date	09/1995	string	15
act_date	act_date	05/1988	string	20
act_date	act_date	06/2000	string	59
act_date	act_date	06/1982	string	13
act_date	act_date	11/1993	string	32
act_date	act_date	11/1994	string	4
act_date	act_date	10/1989	string	25
act_date	act_date	10/1992	string	35
act_date	act_date	02/1991	string	6
aero_cht	aero_cht	BETHEL	string	34
aero_cht	aero_cht	W ALEUTIAN ISLS	string	3
aero_cht	aero_cht	ATLANTA	string	780
aero_cht	aero_cht	JACKSONVILLE	string	467
aero_cht	aero_cht	KANSAS CITY	string	713
aero_cht	aero_cht	HOUSTON	string	765
aero_cht	aero_cht	LAS VEGAS	string	136
aero_cht	aero_cht	LOS ANGELES	string	498
aero_cht	aero_cht	KLAMATH FALLS	string	260
c_ldg_rts	c_ldg_rts	Y	string	283
cbd_dir	cbd_dir	E	string	2,127
cbd_dir	cbd_dir	SE	string	2,281
cbd_dir	cbd_dir	NE	string	2,393
cbd_dir	cbd_dir	W	string	2,065
cbd_dir	cbd_dir	NNW	string	11
cbd_dir	cbd_dir	NNE	string	13
cert	cert	LU 06/1986	string	1
cert	cert	CS 05/1973	string	84
cert	cert	LS 05/1973	string	1
cert	cert	AS 06/1987	string	1
cert	cert	BS 03/1978	string	1
cert	cert	CS 03/1973	string	1
cert	cert	LU 01/1980	string	1
cert	cert	LU 03/1999	string	1
cert	cert	LU 06/1975	string	2
cert	cert	DS 05/1973	string	26
city	city	TAORA IS MALOELAP ATOLL	string	1
city	city	NAMORIK ATOLL	string	1
city	city	JABOR JALUIT ATOLL	string	1
city	city	ROI-NAMUR	string	1
city	city	JOHNSTON ISLAND	string	1
city	city	KWAJALEIN	string	1
city	city	PALMER	string	17
city	city	DELTA JUNCTION	string	6
city	city	HOMER	string	10
city	city	TRAPPER CREEK/TALKEETNA	string	1
city	city	SHAKTOOLIK	string	1
city	city	CAPE BEAUFORT	string	1
city	city	KAKE	string	2
city	city	LIME VILLAGE	string	1
city	city	SUTTON	string	5
city	city	DIOMEDE	string	1
city	city	HEALY	string	3
city	city	SHISHMAREF	string	1
city	city	CHALKYITSIK	string	1
city	city	NUNAPITCHUK	string	1
city	city	BIG MOUNTAIN	string	1
city	city	OUZINKIE	string	1
city	city	ST MICHAEL	string	1
city	city	BIG LAKE	string	5
city	city	MC GRATH	string	3
city	city	ATKA	string	1
city	city	STEBBINS	string	1
city	city	CHEFORNAK	string	1
city	city	KODIAK	string	5
city	city	BOUNDARY	string	1
city	city	BREVIG MISSION	string	1
city	city	NIKOLSKI	string	1

[
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": null,
    "weight": 12040
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "02/2000",
    "weight": 54
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1986",
    "weight": 119
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1992",
    "weight": 92
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1999",
    "weight": 41
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1992",
    "weight": 86
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1994",
    "weight": 20
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "08/1990",
    "weight": 36
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "01/1990",
    "weight": 15
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "08/1981",
    "weight": 40
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1986",
    "weight": 56
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1987",
    "weight": 25
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1996",
    "weight": 3
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "01/1994",
    "weight": 36
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1999",
    "weight": 45
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "03/1992",
    "weight": 17
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "07/1995",
    "weight": 15
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1982",
    "weight": 92
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1991",
    "weight": 31
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "03/1998",
    "weight": 62
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "01/1987",
    "weight": 31
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1993",
    "weight": 8
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "02/1987",
    "weight": 129
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1987",
    "weight": 62
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1999",
    "weight": 31
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "07/1982",
    "weight": 80
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "01/1998",
    "weight": 28
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1990",
    "weight": 16
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1983",
    "weight": 77
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1997",
    "weight": 31
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1990",
    "weight": 11
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "07/1983",
    "weight": 20
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1991",
    "weight": 14
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1995",
    "weight": 15
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1988",
    "weight": 20
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "06/2000",
    "weight": 59
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "06/1982",
    "weight": 13
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1993",
    "weight": 32
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1994",
    "weight": 4
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1989",
    "weight": 25
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1992",
    "weight": 35
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "02/1991",
    "weight": 6
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "BETHEL",
    "weight": 34
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "W ALEUTIAN ISLS",
    "weight": 3
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "ATLANTA",
    "weight": 780
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "JACKSONVILLE",
    "weight": 467
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "KANSAS CITY",
    "weight": 713
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "HOUSTON",
    "weight": 765
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "LAS VEGAS",
    "weight": 136
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "LOS ANGELES",
    "weight": 498
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "KLAMATH FALLS",
    "weight": 260
  },
  {
    "fieldName": "c_ldg_rts",
    "fieldPath": "c_ldg_rts",
    "fieldType": "string",
    "fieldValue": "Y",
    "weight": 283
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "E",
    "weight": 2127
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "SE",
    "weight": 2281
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "NE",
    "weight": 2393
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "W",
    "weight": 2065
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "NNW",
    "weight": 11
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "NNE",
    "weight": 13
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LU 06/1986",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "CS 05/1973",
    "weight": 84
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LS 05/1973",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "AS 06/1987",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "BS 03/1978",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "CS 03/1973",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LU 01/1980",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LU 03/1999",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LU 06/1975",
    "weight": 2
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "DS 05/1973",
    "weight": 26
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "TAORA IS  MALOELAP ATOLL",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NAMORIK ATOLL",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "JABOR JALUIT ATOLL",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ROI-NAMUR",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "JOHNSTON ISLAND",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KWAJALEIN",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "PALMER",
    "weight": 17
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "DELTA JUNCTION",
    "weight": 6
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "HOMER",
    "weight": 10
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "TRAPPER CREEK/TALKEETNA",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SHAKTOOLIK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CAPE BEAUFORT",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KAKE",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "LIME VILLAGE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SUTTON",
    "weight": 5
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "DIOMEDE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "HEALY",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SHISHMAREF",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHALKYITSIK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NUNAPITCHUK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "BIG MOUNTAIN",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "OUZINKIE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ST MICHAEL",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "BIG LAKE",
    "weight": 5
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "MC GRATH",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ATKA",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "STEBBINS",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHEFORNAK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KODIAK",
    "weight": 5
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "BOUNDARY",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "BREVIG MISSION",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NIKOLSKI",
    "weight": 1
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'string'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'number'
      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 'number'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
      WHEN 14 THEN 'string'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'string'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'number'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'number'
      WHEN 21 THEN 'number'
      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 CAST(NULL as VARCHAR)
      WHEN 0 THEN base."act_date"
      WHEN 1 THEN base."aero_cht"
      WHEN 2 THEN base."c_ldg_rts"
      WHEN 3 THEN base."cbd_dir"
      WHEN 5 THEN base."cert"
      WHEN 6 THEN base."city"
      WHEN 7 THEN base."cntl_twr"
      WHEN 8 THEN base."code"
      WHEN 9 THEN base."county"
      WHEN 10 THEN base."cust_intl"
      WHEN 12 THEN base."faa_dist"
      WHEN 13 THEN base."faa_region"
      WHEN 14 THEN base."fac_type"
      WHEN 15 THEN base."fac_use"
      WHEN 16 THEN base."fed_agree"
      WHEN 17 THEN base."full_name"
      WHEN 19 THEN base."joint_use"
      WHEN 22 THEN base."major"
      WHEN 23 THEN base."mil_rts"
      WHEN 24 THEN base."own_type"
      WHEN 25 THEN base."site_number"
      WHEN 26 THEN base."state"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 4 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
      WHEN 11 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 18 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 20 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 21 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") 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,5
)
SELECT
  "fieldName",
  "fieldPath",
  "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	fieldPath	fieldType	fieldValue	weight
major	major	string	N	19,523
cert	cert	string	∅	19,142
cntl_twr	cntl_twr	string	N	19,124
fed_agree	fed_agree	string	∅	16,252
c_ldg_rts	c_ldg_rts	string	∅	15,145
cust_intl	cust_intl	string	∅	15,145
joint_use	joint_use	string	∅	14,804
mil_rts	mil_rts	string	∅	14,716
fac_use	fac_use	string	PR	14,428
own_type	own_type	string	PR	14,306
fac_type	fac_type	string	AIRPORT	13,925
act_date	act_date	string	∅	12,040
faa_dist	faa_dist	string	NONE	7,085
fac_use	fac_use	string	PU	5,365
own_type	own_type	string	PU	5,174
fac_type	fac_type	string	HELIPORT	5,135
joint_use	joint_use	string	N	4,779
cust_intl	cust_intl	string	N	4,575
faa_region	faa_region	string	AGL	4,437
c_ldg_rts	c_ldg_rts	string	N	4,365
cbd_dir	cbd_dir	string	N	3,694
faa_region	faa_region	string	ASW	3,268
mil_rts	mil_rts	string	Y	2,958
faa_region	faa_region	string	ASO	2,924
faa_region	faa_region	string	AEA	2,586
cbd_dir	cbd_dir	string	NW	2,438
cbd_dir	cbd_dir	string	SW	2,427
cbd_dir	cbd_dir	string	NE	2,393
cbd_dir	cbd_dir	string	SE	2,281
cbd_dir	cbd_dir	string	S	2,155
cbd_dir	cbd_dir	string	E	2,127
mil_rts	mil_rts	string	N	2,119
faa_region	faa_region	string	ANM	2,102
cbd_dir	cbd_dir	string	W	2,065
state	state	string	TX	1,845
fed_agree	fed_agree	string	NGY	1,682
aero_cht	aero_cht	string	NEW YORK	1,581
faa_region	faa_region	string	ACE	1,579
faa_dist	faa_dist	string	CHI	1,528
faa_region	faa_region	string	AWP	1,503
aero_cht	aero_cht	string	CHICAGO	1,480
aero_cht	aero_cht	string	DETROIT	1,262
faa_dist	faa_dist	string	DET	1,240
faa_dist	faa_dist	string	SEA	1,162
faa_dist	faa_dist	string	MSP	1,046
faa_dist	faa_dist	string	ATL	1,029
state	state	string	CA	984
faa_dist	faa_dist	string	NYC	957
faa_dist	faa_dist	string	ORL	905
state	state	string	IL	890
aero_cht	aero_cht	string	DALLAS-FT WORTH	888
aero_cht	aero_cht	string	WASHINGTON	887
state	state	string	FL	856
aero_cht	aero_cht	string	ST LOUIS	847
faa_dist	faa_dist	string	HAR	846
state	state	string	PA	804
aero_cht	aero_cht	string	ATLANTA	780
aero_cht	aero_cht	string	HOUSTON	765
faa_region	faa_region	string	ANE	763
aero_cht	aero_cht	string	SEATTLE	749
state	state	string	OH	749
aero_cht	aero_cht	string	KANSAS CITY	713
aero_cht	aero_cht	string	TWIN CITIES	699
faa_dist	faa_dist	string	DEN	679
cntl_twr	cntl_twr	string	Y	669
faa_dist	faa_dist	string	DCA	667
state	state	string	IN	643
aero_cht	aero_cht	string	CINCINNATI	624
faa_dist	faa_dist	string	BIS	616
state	state	string	AK	608
faa_region	faa_region	string	AAL	608
aero_cht	aero_cht	string	MEMPHIS	605
faa_dist	faa_dist	string	SFO	585
state	state	string	NY	576
aero_cht	aero_cht	string	OMAHA	551
state	state	string	WI	543
state	state	string	MO	537
fed_agree	fed_agree	string	N	515
state	state	string	MN	507
aero_cht	aero_cht	string	SAN ANTONIO	503
faa_dist	faa_dist	string	JAN	502
state	state	string	LA	500
aero_cht	aero_cht	string	LOS ANGELES	498
state	state	string	MI	489
faa_dist	faa_dist	string	MEM	487
state	state	string	WA	484
fac_type	fac_type	string	SEAPLANE BASE	473
aero_cht	aero_cht	string	JACKSONVILLE	467
aero_cht	aero_cht	string	NEW ORLEANS	444
state	state	string	OK	443
state	state	string	OR	441
state	state	string	GA	440
state	state	string	ND	436
aero_cht	aero_cht	string	SAN FRANCISCO	427
state	state	string	CO	425
state	state	string	VA	421
aero_cht	aero_cht	string	GREEN BAY	418
state	state	string	KS	415
state	state	string	NC	400
aero_cht	aero_cht	string	CHARLOTTE	380

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

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'string'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'number'
      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 'number'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
      WHEN 14 THEN 'string'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'string'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'number'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'number'
      WHEN 21 THEN 'number'
      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 CAST(NULL as VARCHAR)
      WHEN 0 THEN base."act_date"
      WHEN 1 THEN base."aero_cht"
      WHEN 2 THEN base."c_ldg_rts"
      WHEN 3 THEN base."cbd_dir"
      WHEN 5 THEN base."cert"
      WHEN 6 THEN base."city"
      WHEN 7 THEN base."cntl_twr"
      WHEN 8 THEN base."code"
      WHEN 9 THEN base."county"
      WHEN 10 THEN base."cust_intl"
      WHEN 12 THEN base."faa_dist"
      WHEN 13 THEN base."faa_region"
      WHEN 14 THEN base."fac_type"
      WHEN 15 THEN base."fac_use"
      WHEN 16 THEN base."fed_agree"
      WHEN 17 THEN base."full_name"
      WHEN 19 THEN base."joint_use"
      WHEN 22 THEN base."major"
      WHEN 23 THEN base."mil_rts"
      WHEN 24 THEN base."own_type"
      WHEN 25 THEN base."site_number"
      WHEN 26 THEN base."state"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 4 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
      WHEN 11 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 18 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 20 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 21 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") 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,5
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldPath",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage0
)
SELECT 
   base."fieldName" as "fieldName",
   base."fieldPath" as "fieldPath",
   base."fieldType" as "fieldType",
   base."fieldValue" as "fieldValue",
   base."weight" as "weight"
FROM __stage1 as base
WHERE base."fieldType"='string'
ORDER BY 5 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	fieldPath	fieldType	fieldValue	weight
county	county	string	SANTA ROSA	26
county	county	string	SANTA BARBARA	22
county	county	string	SANTA CLARA	10
county	county	string	SANTA CRUZ	10
county	county	string	SANTA FE	9
city	city	string	SANTA ANA	6
city	city	string	SANTA FE	6
city	city	string	SANTA BARBARA	5
city	city	string	SANTA ROSA	4
city	city	string	SANTA MARIA	3
city	city	string	SANTA YNEZ	3
city	city	string	SANTA CRUZ	2
city	city	string	SANTA ELENA	2
city	city	string	SANTA YSABEL	2
city	city	string	RANCHO SANTA MARGARITA	2

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

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

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

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

SELECT 
   base."act_date" as "act_date",
   base."aero_cht" as "aero_cht",
   base."c_ldg_rts" as "c_ldg_rts",
   base."cbd_dir" as "cbd_dir",
   base."cbd_dist" as "cbd_dist",
   base."cert" as "cert",
   base."city" as "city",
   base."cntl_twr" as "cntl_twr",
   base."code" as "code",
   base."county" as "county",
   base."cust_intl" as "cust_intl",
   base."elevation" as "elevation",
   base."faa_dist" as "faa_dist",
   base."faa_region" as "faa_region",
   base."fac_type" as "fac_type",
   base."fac_use" as "fac_use",
   base."fed_agree" as "fed_agree",
   base."full_name" as "full_name",
   base."id" as "id",
   base."joint_use" as "joint_use",
   base."latitude" as "latitude",
   base."longitude" as "longitude",
   base."major" as "major",
   base."mil_rts" as "mil_rts",
   base."own_type" as "own_type",
   base."site_number" as "site_number",
   base."state" as "state"
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
11/1988	113
03/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
GREENVILLE	38
WASHINGTON	38
PHOENIX	37
CLINTON	37
PHILADELPHIA	35

cntl_twr

fieldValue	weight
N	19,124
Y	669

code

fieldValue	weight
3N0	1
11AK	1
61AK	1
2AK	1
35AK	1
DM2	1
SHH	1
AK65	1
2AK4	1
C05	1

county

fieldValue	weight
WASHINGTON	214
JEFFERSON	199
LOS ANGELES	176
MONTGOMERY	154
FRANKLIN	140
JACKSON	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
WILSON	9
WILLIAMS	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
51534.5*A	1
51518.01*A	1
50033.01*H	1
50870.27*A	1
50320.2*A	1
50425.1*C	1
50072.5*A	1
50215.02*H	1
50146.02*A	1
50584.4*A	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": "11/1988",
        "weight": 113
      },
      {
        "fieldValue": "03/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": "GREENVILLE",
        "weight": 38
      },
      {
        "fieldValue": "WASHINGTON",
        "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": "3N0",
        "weight": 1
      },
      {
        "fieldValue": "11AK",
        "weight": 1
      },
      {
        "fieldValue": "61AK",
        "weight": 1
      },
      {
        "fieldValue": "2AK",
        "weight": 1
      },
      {
        "fieldValue": "35AK",
        "weight": 1
      },
      {
        "fieldValue": "DM2",
        "weight": 1
      },
      {
        "fieldValue": "SHH",
        "weight": 1
      },
      {
        "fieldValue": "AK65",
        "weight": 1
      },
      {
        "fieldValue": "2AK4",
        "weight": 1
      },
      {
        "fieldValue": "C05",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "county",
    "values": [
      {
        "fieldValue": "WASHINGTON",
        "weight": 214
      },
      {
        "fieldValue": "JEFFERSON",
        "weight": 199
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 176
      },
      {
        "fieldValue": "MONTGOMERY",
        "weight": 154
      },
      {
        "fieldValue": "FRANKLIN",
        "weight": 140
      },
      {
        "fieldValue": "JACKSON",
        "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": "WILSON",
        "weight": 9
      },
      {
        "fieldValue": "WILLIAMS",
        "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": "51534.5*A",
        "weight": 1
      },
      {
        "fieldValue": "51518.01*A",
        "weight": 1
      },
      {
        "fieldValue": "50033.01*H",
        "weight": 1
      },
      {
        "fieldValue": "50870.27*A",
        "weight": 1
      },
      {
        "fieldValue": "50320.2*A",
        "weight": 1
      },
      {
        "fieldValue": "50425.1*C",
        "weight": 1
      },
      {
        "fieldValue": "50072.5*A",
        "weight": 1
      },
      {
        "fieldValue": "50215.02*H",
        "weight": 1
      },
      {
        "fieldValue": "50146.02*A",
        "weight": 1
      },
      {
        "fieldValue": "50584.4*A",
        "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 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'string'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'number'
      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 'number'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
      WHEN 14 THEN 'string'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'string'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'number'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'number'
      WHEN 21 THEN 'number'
      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 CAST(NULL as VARCHAR)
      WHEN 0 THEN base."act_date"
      WHEN 1 THEN base."aero_cht"
      WHEN 2 THEN base."c_ldg_rts"
      WHEN 3 THEN base."cbd_dir"
      WHEN 5 THEN base."cert"
      WHEN 6 THEN base."city"
      WHEN 7 THEN base."cntl_twr"
      WHEN 8 THEN base."code"
      WHEN 9 THEN base."county"
      WHEN 10 THEN base."cust_intl"
      WHEN 12 THEN base."faa_dist"
      WHEN 13 THEN base."faa_region"
      WHEN 14 THEN base."fac_type"
      WHEN 15 THEN base."fac_use"
      WHEN 16 THEN base."fed_agree"
      WHEN 17 THEN base."full_name"
      WHEN 19 THEN base."joint_use"
      WHEN 22 THEN base."major"
      WHEN 23 THEN base."mil_rts"
      WHEN 24 THEN base."own_type"
      WHEN 25 THEN base."site_number"
      WHEN 26 THEN base."state"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 4 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
      WHEN 11 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 18 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 20 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 21 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") 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,5
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldPath",
    "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,011
02/1987	43
04/1993	43
08/1985	37
03/1993	34
11/1986	33
02/1982	32
11/1992	29
11/1990	29
06/1983	29

aero_cht

fieldValue	weight
NEW YORK	446
CHICAGO	361
DETROIT	330
DALLAS-FT WORTH	238
WASHINGTON	213
ST LOUIS	212
HOUSTON	204
SEATTLE	202
TWIN CITIES	192
KANSAS CITY	188

c_ldg_rts

fieldValue	weight
∅	3,881
N	1,048
Y	71

cbd_dir

fieldValue	weight
N	920
NE	625
SW	618
NW	590
SE	578
E	567
S	521
W	516
∅	35
ENE	6

cbd_dist

fieldValue	weight
0 to 62	5,000

cert

fieldValue	weight
∅	4,829
BS 05/1973	28
AS 05/1973	24
CS 05/1973	17
LU 05/1973	12
LU 08/1990	10
LU 11/1974	6
DS 05/1973	6
ES 05/1973	4
LU 10/1974	3

city

fieldValue	weight
HOUSTON	28
RICHMOND	14
WASHINGTON	13
SPRINGFIELD	13
COLUMBUS	12
PHILADELPHIA	12
LOS ANGELES	12
BATON ROUGE	10
JACKSONVILLE	10
ORLANDO	9

cntl_twr

fieldValue	weight
N	4,820
Y	180

code

fieldValue	weight
O51	1
20II	1
6G2	1
UT80	1
8OR7	1
31OK	1
AK64	1
UT26	1
8OI6	1
MN36	1

county

fieldValue	weight
WASHINGTON	53
JEFFERSON	48
FRANKLIN	41
LOS ANGELES	39
HARRIS	36
JACKSON	34
LAKE	30
CASS	30
MARICOPA	29
MONTGOMERY	29

cust_intl

fieldValue	weight
∅	3,881
N	1,101
Y	18

elevation

fieldValue	weight
-113 to 11294	5,000

faa_dist

fieldValue	weight
NONE	1,786
CHI	382
DET	309
SEA	305
MSP	285
NYC	272
ATL	253
HAR	236
ORL	217
BIS	174

faa_region

fieldValue	weight
AGL	1,152
ASW	840
ASO	702
AEA	691
ANM	514
ACE	397
AWP	359
ANE	201
AAL	138
∅	6

fac_type

fieldValue	weight
AIRPORT	3,473
HELIPORT	1,328
SEAPLANE BASE	119
ULTRALIGHT	41
STOLPORT	25
GLIDERPORT	8
BALLOONPORT	6

fac_use

fieldValue	weight
PR	3,701
PU	1,299

fed_agree

fieldValue	weight
∅	4,152
NGY	412
N	104
NGY3	58
NGPY	45
1	25
NY1	24
NGPY3	22
N1	21
NGSY	19

full_name

fieldValue	weight
SMITH	6
ST MARY'S HOSPITAL	5
MEMORIAL HOSPITAL	5
HILLTOP	5
FLYING B RANCH	4
FAIRVIEW	4
MERCY HOSPITAL	4
ST FRANCIS HOSPITAL	4
VALLEY VIEW	4
SPRING VALLEY FARM	3

fieldValue	weight
1000 to 19783	5,000

joint_use

fieldValue	weight
∅	3,788
N	1,151
Y	61

latitude

fieldValue	weight
-14.21 to 71.28	5,000

longitude

fieldValue	weight
-100.02 to 174.11	5,000

major

fieldValue	weight
N	4,932
Y	68

mil_rts

fieldValue	weight
∅	3,773
Y	728
N	499

own_type

fieldValue	weight
PR	3,682
PU	1,249
MR	31
MA	26
MN	12

site_number

fieldValue	weight
20480.1*A	1
05410.7*A	1
15474.*H	1
20777.*A	1
24067.77*H	1
50523.35*A	1
50543.73*H	1
15203.01*H	1
08582.01*H	1
04373.3*A	1

state

fieldValue	weight
TX	475
CA	229
PA	225
IL	216
FL	210
OH	198
NY	175
IN	168
MN	146
WI	140

∅

fieldValue	weight
∅	5,000

[
  {
    "fieldName": "act_date",
    "values": [
      {
        "fieldValue": null,
        "weight": 3011
      },
      {
        "fieldValue": "02/1987",
        "weight": 43
      },
      {
        "fieldValue": "04/1993",
        "weight": 43
      },
      {
        "fieldValue": "08/1985",
        "weight": 37
      },
      {
        "fieldValue": "03/1993",
        "weight": 34
      },
      {
        "fieldValue": "11/1986",
        "weight": 33
      },
      {
        "fieldValue": "02/1982",
        "weight": 32
      },
      {
        "fieldValue": "11/1992",
        "weight": 29
      },
      {
        "fieldValue": "11/1990",
        "weight": 29
      },
      {
        "fieldValue": "06/1983",
        "weight": 29
      }
    ]
  },
  {
    "fieldName": "aero_cht",
    "values": [
      {
        "fieldValue": "NEW YORK",
        "weight": 446
      },
      {
        "fieldValue": "CHICAGO",
        "weight": 361
      },
      {
        "fieldValue": "DETROIT",
        "weight": 330
      },
      {
        "fieldValue": "DALLAS-FT WORTH",
        "weight": 238
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 213
      },
      {
        "fieldValue": "ST LOUIS",
        "weight": 212
      },
      {
        "fieldValue": "HOUSTON",
        "weight": 204
      },
      {
        "fieldValue": "SEATTLE",
        "weight": 202
      },
      {
        "fieldValue": "TWIN CITIES",
        "weight": 192
      },
      {
        "fieldValue": "KANSAS CITY",
        "weight": 188
      }
    ]
  },
  {
    "fieldName": "c_ldg_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3881
      },
      {
        "fieldValue": "N",
        "weight": 1048
      },
      {
        "fieldValue": "Y",
        "weight": 71
      }
    ]
  },
  {
    "fieldName": "cbd_dir",
    "values": [
      {
        "fieldValue": "N",
        "weight": 920
      },
      {
        "fieldValue": "NE",
        "weight": 625
      },
      {
        "fieldValue": "SW",
        "weight": 618
      },
      {
        "fieldValue": "NW",
        "weight": 590
      },
      {
        "fieldValue": "SE",
        "weight": 578
      },
      {
        "fieldValue": "E",
        "weight": 567
      },
      {
        "fieldValue": "S",
        "weight": 521
      },
      {
        "fieldValue": "W",
        "weight": 516
      },
      {
        "fieldValue": null,
        "weight": 35
      },
      {
        "fieldValue": "ENE",
        "weight": 6
      }
    ]
  },
  {
    "fieldName": "cbd_dist",
    "values": [
      {
        "fieldValue": "0 to 62",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "cert",
    "values": [
      {
        "fieldValue": null,
        "weight": 4829
      },
      {
        "fieldValue": "BS 05/1973",
        "weight": 28
      },
      {
        "fieldValue": "AS 05/1973",
        "weight": 24
      },
      {
        "fieldValue": "CS 05/1973",
        "weight": 17
      },
      {
        "fieldValue": "LU 05/1973",
        "weight": 12
      },
      {
        "fieldValue": "LU 08/1990",
        "weight": 10
      },
      {
        "fieldValue": "LU 11/1974",
        "weight": 6
      },
      {
        "fieldValue": "DS 05/1973",
        "weight": 6
      },
      {
        "fieldValue": "ES 05/1973",
        "weight": 4
      },
      {
        "fieldValue": "LU 10/1974",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "city",
    "values": [
      {
        "fieldValue": "HOUSTON",
        "weight": 28
      },
      {
        "fieldValue": "RICHMOND",
        "weight": 14
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 13
      },
      {
        "fieldValue": "SPRINGFIELD",
        "weight": 13
      },
      {
        "fieldValue": "COLUMBUS",
        "weight": 12
      },
      {
        "fieldValue": "PHILADELPHIA",
        "weight": 12
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 12
      },
      {
        "fieldValue": "BATON ROUGE",
        "weight": 10
      },
      {
        "fieldValue": "JACKSONVILLE",
        "weight": 10
      },
      {
        "fieldValue": "ORLANDO",
        "weight": 9
      }
    ]
  },
  {
    "fieldName": "cntl_twr",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4820
      },
      {
        "fieldValue": "Y",
        "weight": 180
      }
    ]
  },
  {
    "fieldName": "code",
    "values": [
      {
        "fieldValue": "O51",
        "weight": 1
      },
      {
        "fieldValue": "20II",
        "weight": 1
      },
      {
        "fieldValue": "6G2",
        "weight": 1
      },
      {
        "fieldValue": "UT80",
        "weight": 1
      },
      {
        "fieldValue": "8OR7",
        "weight": 1
      },
      {
        "fieldValue": "31OK",
        "weight": 1
      },
      {
        "fieldValue": "AK64",
        "weight": 1
      },
      {
        "fieldValue": "UT26",
        "weight": 1
      },
      {
        "fieldValue": "8OI6",
        "weight": 1
      },
      {
        "fieldValue": "MN36",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "county",
    "values": [
      {
        "fieldValue": "WASHINGTON",
        "weight": 53
      },
      {
        "fieldValue": "JEFFERSON",
        "weight": 48
      },
      {
        "fieldValue": "FRANKLIN",
        "weight": 41
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 39
      },
      {
        "fieldValue": "HARRIS",
        "weight": 36
      },
      {
        "fieldValue": "JACKSON",
        "weight": 34
      },
      {
        "fieldValue": "LAKE",
        "weight": 30
      },
      {
        "fieldValue": "CASS",
        "weight": 30
      },
      {
        "fieldValue": "MARICOPA",
        "weight": 29
      },
      {
        "fieldValue": "MONTGOMERY",
        "weight": 29
      }
    ]
  },
  {
    "fieldName": "cust_intl",
    "values": [
      {
        "fieldValue": null,
        "weight": 3881
      },
      {
        "fieldValue": "N",
        "weight": 1101
      },
      {
        "fieldValue": "Y",
        "weight": 18
      }
    ]
  },
  {
    "fieldName": "elevation",
    "values": [
      {
        "fieldValue": "-113 to 11294",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "faa_dist",
    "values": [
      {
        "fieldValue": "NONE",
        "weight": 1786
      },
      {
        "fieldValue": "CHI",
        "weight": 382
      },
      {
        "fieldValue": "DET",
        "weight": 309
      },
      {
        "fieldValue": "SEA",
        "weight": 305
      },
      {
        "fieldValue": "MSP",
        "weight": 285
      },
      {
        "fieldValue": "NYC",
        "weight": 272
      },
      {
        "fieldValue": "ATL",
        "weight": 253
      },
      {
        "fieldValue": "HAR",
        "weight": 236
      },
      {
        "fieldValue": "ORL",
        "weight": 217
      },
      {
        "fieldValue": "BIS",
        "weight": 174
      }
    ]
  },
  {
    "fieldName": "faa_region",
    "values": [
      {
        "fieldValue": "AGL",
        "weight": 1152
      },
      {
        "fieldValue": "ASW",
        "weight": 840
      },
      {
        "fieldValue": "ASO",
        "weight": 702
      },
      {
        "fieldValue": "AEA",
        "weight": 691
      },
      {
        "fieldValue": "ANM",
        "weight": 514
      },
      {
        "fieldValue": "ACE",
        "weight": 397
      },
      {
        "fieldValue": "AWP",
        "weight": 359
      },
      {
        "fieldValue": "ANE",
        "weight": 201
      },
      {
        "fieldValue": "AAL",
        "weight": 138
      },
      {
        "fieldValue": null,
        "weight": 6
      }
    ]
  },
  {
    "fieldName": "fac_type",
    "values": [
      {
        "fieldValue": "AIRPORT",
        "weight": 3473
      },
      {
        "fieldValue": "HELIPORT",
        "weight": 1328
      },
      {
        "fieldValue": "SEAPLANE BASE",
        "weight": 119
      },
      {
        "fieldValue": "ULTRALIGHT",
        "weight": 41
      },
      {
        "fieldValue": "STOLPORT",
        "weight": 25
      },
      {
        "fieldValue": "GLIDERPORT",
        "weight": 8
      },
      {
        "fieldValue": "BALLOONPORT",
        "weight": 6
      }
    ]
  },
  {
    "fieldName": "fac_use",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3701
      },
      {
        "fieldValue": "PU",
        "weight": 1299
      }
    ]
  },
  {
    "fieldName": "fed_agree",
    "values": [
      {
        "fieldValue": null,
        "weight": 4152
      },
      {
        "fieldValue": "NGY",
        "weight": 412
      },
      {
        "fieldValue": "N",
        "weight": 104
      },
      {
        "fieldValue": "NGY3",
        "weight": 58
      },
      {
        "fieldValue": "NGPY",
        "weight": 45
      },
      {
        "fieldValue": "1",
        "weight": 25
      },
      {
        "fieldValue": "NY1",
        "weight": 24
      },
      {
        "fieldValue": "NGPY3",
        "weight": 22
      },
      {
        "fieldValue": "N1",
        "weight": 21
      },
      {
        "fieldValue": "NGSY",
        "weight": 19
      }
    ]
  },
  {
    "fieldName": "full_name",
    "values": [
      {
        "fieldValue": "SMITH",
        "weight": 6
      },
      {
        "fieldValue": "ST MARY'S HOSPITAL",
        "weight": 5
      },
      {
        "fieldValue": "MEMORIAL HOSPITAL",
        "weight": 5
      },
      {
        "fieldValue": "HILLTOP",
        "weight": 5
      },
      {
        "fieldValue": "FLYING B RANCH",
        "weight": 4
      },
      {
        "fieldValue": "FAIRVIEW",
        "weight": 4
      },
      {
        "fieldValue": "MERCY HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "ST FRANCIS HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "VALLEY VIEW",
        "weight": 4
      },
      {
        "fieldValue": "SPRING VALLEY FARM",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "id",
    "values": [
      {
        "fieldValue": "1000 to 19783",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "joint_use",
    "values": [
      {
        "fieldValue": null,
        "weight": 3788
      },
      {
        "fieldValue": "N",
        "weight": 1151
      },
      {
        "fieldValue": "Y",
        "weight": 61
      }
    ]
  },
  {
    "fieldName": "latitude",
    "values": [
      {
        "fieldValue": "-14.21 to 71.28",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "longitude",
    "values": [
      {
        "fieldValue": "-100.02 to 174.11",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "major",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4932
      },
      {
        "fieldValue": "Y",
        "weight": 68
      }
    ]
  },
  {
    "fieldName": "mil_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3773
      },
      {
        "fieldValue": "Y",
        "weight": 728
      },
      {
        "fieldValue": "N",
        "weight": 499
      }
    ]
  },
  {
    "fieldName": "own_type",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3682
      },
      {
        "fieldValue": "PU",
        "weight": 1249
      },
      {
        "fieldValue": "MR",
        "weight": 31
      },
      {
        "fieldValue": "MA",
        "weight": 26
      },
      {
        "fieldValue": "MN",
        "weight": 12
      }
    ]
  },
  {
    "fieldName": "site_number",
    "values": [
      {
        "fieldValue": "20480.1*A",
        "weight": 1
      },
      {
        "fieldValue": "05410.7*A",
        "weight": 1
      },
      {
        "fieldValue": "15474.*H",
        "weight": 1
      },
      {
        "fieldValue": "20777.*A",
        "weight": 1
      },
      {
        "fieldValue": "24067.77*H",
        "weight": 1
      },
      {
        "fieldValue": "50523.35*A",
        "weight": 1
      },
      {
        "fieldValue": "50543.73*H",
        "weight": 1
      },
      {
        "fieldValue": "15203.01*H",
        "weight": 1
      },
      {
        "fieldValue": "08582.01*H",
        "weight": 1
      },
      {
        "fieldValue": "04373.3*A",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "state",
    "values": [
      {
        "fieldValue": "TX",
        "weight": 475
      },
      {
        "fieldValue": "CA",
        "weight": 229
      },
      {
        "fieldValue": "PA",
        "weight": 225
      },
      {
        "fieldValue": "IL",
        "weight": 216
      },
      {
        "fieldValue": "FL",
        "weight": 210
      },
      {
        "fieldValue": "OH",
        "weight": 198
      },
      {
        "fieldValue": "NY",
        "weight": 175
      },
      {
        "fieldValue": "IN",
        "weight": 168
      },
      {
        "fieldValue": "MN",
        "weight": 146
      },
      {
        "fieldValue": "WI",
        "weight": 140
      }
    ]
  },
  {
    "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 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'act_date'
      WHEN 1 THEN 'aero_cht'
      WHEN 2 THEN 'c_ldg_rts'
      WHEN 3 THEN 'cbd_dir'
      WHEN 4 THEN 'cbd_dist'
      WHEN 5 THEN 'cert'
      WHEN 6 THEN 'city'
      WHEN 7 THEN 'cntl_twr'
      WHEN 8 THEN 'code'
      WHEN 9 THEN 'county'
      WHEN 10 THEN 'cust_intl'
      WHEN 11 THEN 'elevation'
      WHEN 12 THEN 'faa_dist'
      WHEN 13 THEN 'faa_region'
      WHEN 14 THEN 'fac_type'
      WHEN 15 THEN 'fac_use'
      WHEN 16 THEN 'fed_agree'
      WHEN 17 THEN 'full_name'
      WHEN 18 THEN 'id'
      WHEN 19 THEN 'joint_use'
      WHEN 20 THEN 'latitude'
      WHEN 21 THEN 'longitude'
      WHEN 22 THEN 'major'
      WHEN 23 THEN 'mil_rts'
      WHEN 24 THEN 'own_type'
      WHEN 25 THEN 'site_number'
      WHEN 26 THEN 'state'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'string'
      WHEN 1 THEN 'string'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'string'
      WHEN 4 THEN 'number'
      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 'number'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
      WHEN 14 THEN 'string'
      WHEN 15 THEN 'string'
      WHEN 16 THEN 'string'
      WHEN 17 THEN 'string'
      WHEN 18 THEN 'number'
      WHEN 19 THEN 'string'
      WHEN 20 THEN 'number'
      WHEN 21 THEN 'number'
      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 CAST(NULL as VARCHAR)
      WHEN 0 THEN base."act_date"
      WHEN 1 THEN base."aero_cht"
      WHEN 2 THEN base."c_ldg_rts"
      WHEN 3 THEN base."cbd_dir"
      WHEN 5 THEN base."cert"
      WHEN 6 THEN base."city"
      WHEN 7 THEN base."cntl_twr"
      WHEN 8 THEN base."code"
      WHEN 9 THEN base."county"
      WHEN 10 THEN base."cust_intl"
      WHEN 12 THEN base."faa_dist"
      WHEN 13 THEN base."faa_region"
      WHEN 14 THEN base."fac_type"
      WHEN 15 THEN base."fac_use"
      WHEN 16 THEN base."fed_agree"
      WHEN 17 THEN base."full_name"
      WHEN 19 THEN base."joint_use"
      WHEN 22 THEN base."major"
      WHEN 23 THEN base."mil_rts"
      WHEN 24 THEN base."own_type"
      WHEN 25 THEN base."site_number"
      WHEN 26 THEN base."state"
    END as "fieldValue",
   COUNT(*) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 4 THEN MIN(CAST(base."cbd_dist" as VARCHAR)) || ' to ' || CAST(MAX(base."cbd_dist") as VARCHAR)
      WHEN 11 THEN MIN(CAST(base."elevation" as VARCHAR)) || ' to ' || CAST(MAX(base."elevation") as VARCHAR)
      WHEN 18 THEN MIN(CAST(base."id" as VARCHAR)) || ' to ' || CAST(MAX(base."id") as VARCHAR)
      WHEN 20 THEN MIN(CAST(base."latitude" as VARCHAR)) || ' to ' || CAST(MAX(base."latitude") as VARCHAR)
      WHEN 21 THEN MIN(CAST(base."longitude" as VARCHAR)) || ' to ' || CAST(MAX(base."longitude") 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,5
)
, __stage2 AS (
  SELECT
    "fieldName",
    "fieldPath",
    "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') extend {
    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",
    "total_ratings": 7846138
  },
  {
    "primaryName": "Eric Fellner",
    "total_ratings": 7845245
  },
  {
    "primaryName": "J.R.R. Tolkien",
    "total_ratings": 7798667
  },
  {
    "primaryName": "Jake Gyllenhaal",
    "total_ratings": 7762048
  },
  {
    "primaryName": "Jerry Bruckheimer",
    "total_ratings": 7721877
  },
  {
    "primaryName": "Edward Norton",
    "total_ratings": 7713816
  },
  {
    "primaryName": "Woody Harrelson",
    "total_ratings": 7711063
  },
  {
    "primaryName": "Ewan McGregor",
    "total_ratings": 7655458
  },
  {
    "primaryName": "George Clooney",
    "total_ratings": 7653368
  },
  {
    "primaryName": "David Benioff",
    "total_ratings": 7637309
  },
  {
    "primaryName": "Kevin Spacey",
    "total_ratings": 7603094
  },
  {
    "primaryName": "Brian Grazer",
    "total_ratings": 7570625
  },
  {
    "primaryName": "Anne Hathaway",
    "total_ratings": 7509220
  },
  {
    "primaryName": "Arnon Milchan",
    "total_ratings": 7494607
  },
  {
    "primaryName": "Jim Carrey",
    "total_ratings": 7421247
  },
  {
    "primaryName": "Robert Zemeckis",
    "total_ratings": 7371523
  },
  {
    "primaryName": "Danny Elfman",
    "total_ratings": 7340555
  },
  {
    "primaryName": "Ryan Reynolds",
    "total_ratings": 7319537
  },
  {
    "primaryName": "Jason Blum",
    "total_ratings": 7308653
  },
  {
    "primaryName": "James Cameron",
    "total_ratings": 7306015
  },
  {
    "primaryName": "Sally Menke",
    "total_ratings": 7239769
  },
  {
    "primaryName": "Tom Hardy",
    "total_ratings": 7213428
  },
  {
    "primaryName": "Clint Eastwood",
    "total_ratings": 7209434
  },
  {
    "primaryName": "Russell Crowe",
    "total_ratings": 7166573
  },
  {
    "primaryName": "Akiva Goldsman",
    "total_ratings": 7094797
  },
  {
    "primaryName": "Nicolas Cage",
    "total_ratings": 7053952
  },
  {
    "primaryName": "Michael Giacchino",
    "total_ratings": 7047025
  },
  {
    "primaryName": "Gary Oldman",
    "total_ratings": 7036991
  },
  {
    "primaryName": "Cate Blanchett",
    "total_ratings": 7017425
  },
  {
    "primaryName": "Michael Bay",
    "total_ratings": 7016200
  },
  {
    "primaryName": "J.J. Abrams",
    "total_ratings": 6991513
  },
  {
    "primaryName": "Willem Dafoe",
    "total_ratings": 6924815
  },
  {
    "primaryName": "Christopher Markus",
    "total_ratings": 6886318
  },
  {
    "primaryName": "Stephen McFeely",
    "total_ratings": 6886318
  },
  {
    "primaryName": "Neal H. Moritz",
    "total_ratings": 6870329
  },
  {
    "primaryName": "Alexandre Desplat",
    "total_ratings": 6867309
  },
  {
    "primaryName": "Jennifer Lawrence",
    "total_ratings": 6855625
  },
  {
    "primaryName": "Rachel McAdams",
    "total_ratings": 6777091
  },
  {
    "primaryName": "Lena Headey",
    "total_ratings": 6742472
  },
  {
    "primaryName": "Elijah Wood",
    "total_ratings": 6734358
  },
  {
    "primaryName": "Dwayne Johnson",
    "total_ratings": 6584416
  },
  {
    "primaryName": "Jason Statham",
    "total_ratings": 6582293
  },
  {
    "primaryName": "Steve Kloves",
    "total_ratings": 6559963
  },
  {
    "primaryName": "Denzel Washington",
    "total_ratings": 6556554
  },
  {
    "primaryName": "Sigourney Weaver",
    "total_ratings": 6515096
  },
  {
    "primaryName": "Arnold Schwarzenegger",
    "total_ratings": 6482925
  },
  {
    "primaryName": "Tim Burton",
    "total_ratings": 6467281
  },
  {
    "primaryName": "Steve Carell",
    "total_ratings": 6436581
  },
  {
    "primaryName": "Terry Rossio",
    "total_ratings": 6423369
  },
  {
    "primaryName": "Ceán Chaffin",
    "total_ratings": 6415865
  },
  {
    "primaryName": "Amy Adams",
    "total_ratings": 6401556
  },
  {
    "primaryName": "Daniel Radcliffe",
    "total_ratings": 6393663
  },
  {
    "primaryName": "J.K. Rowling",
    "total_ratings": 6392020
  },
  {
    "primaryName": "Mel Gibson",
    "total_ratings": 6322650
  },
  {
    "primaryName": "Anthony Hopkins",
    "total_ratings": 6315913
  },
  {
    "primaryName": "Emma Watson",
    "total_ratings": 6304351
  },
  {
    "primaryName": null,
    "total_ratings": 6300267
  },
  {
    "primaryName": "Rick McCallum",
    "total_ratings": 6211607
  },
  {
    "primaryName": "Jack Nicholson",
    "total_ratings": 6180622
  },
  {
    "primaryName": "Ted Elliott",
    "total_ratings": 6136354
  },
  {
    "primaryName": "Adam Sandler",
    "total_ratings": 6094935
  },
  {
    "primaryName": "Ed Harris",
    "total_ratings": 6077615
  },
  {
    "primaryName": "Chris Columbus",
    "total_ratings": 6076896
  },
  {
    "primaryName": "Cameron Diaz",
    "total_ratings": 6042333
  },
  {
    "primaryName": "Joseph Gordon-Levitt",
    "total_ratings": 6034397
  },
  {
    "primaryName": "Emilia Clarke",
    "total_ratings": 5995245
  },
  {
    "primaryName": "D.B. Weiss",
    "total_ratings": 5972128
  },
  {
    "primaryName": "Joaquin Phoenix",
    "total_ratings": 5967875
  },
  {
    "primaryName": "Daniel Craig",
    "total_ratings": 5967704
  },
  {
    "primaryName": "Luc Besson",
    "total_ratings": 5961182
  },
  {
    "primaryName": "Ryan Gosling",
    "total_ratings": 5956376
  },
  {
    "primaryName": "Vin Diesel",
    "total_ratings": 5944056
  },
  {
    "primaryName": "Andrew Stanton",
    "total_ratings": 5942167
  },
  {
    "primaryName": "John Goodman",
    "total_ratings": 5940986
  },
  {
    "primaryName": "Ralph Fiennes",
    "total_ratings": 5906715
  },
  {
    "primaryName": "Peter Dinklage",
    "total_ratings": 5906118
  },
  {
    "primaryName": "David Koepp",
    "total_ratings": 5894926
  },
  {
    "primaryName": "Pete Docter",
    "total_ratings": 5862141
  },
  {
    "primaryName": "Sylvester Stallone",
    "total_ratings": 5861981
  },
  {
    "primaryName": "Angelina Jolie",
    "total_ratings": 5857481
  },
  {
    "primaryName": "Ian Bryce",
    "total_ratings": 5848379
  },
  {
    "primaryName": "Hugo Weaving",
    "total_ratings": 5841890
  },
  {
    "primaryName": "Frank Darabont",
    "total_ratings": 5826908
  },
  {
    "primaryName": "Ben Kingsley",
    "total_ratings": 5825798
  },
  {
    "primaryName": "Viggo Mortensen",
    "total_ratings": 5823719
  },
  {
    "primaryName": "Richard Francis-Bruce",
    "total_ratings": 5798818
  },
  {
    "primaryName": "Colin Farrell",
    "total_ratings": 5788835
  },
  {
    "primaryName": "Charlize Theron",
    "total_ratings": 5765794
  },
  {
    "primaryName": "Joss Whedon",
    "total_ratings": 5757097
  },
  {
    "primaryName": "Nikolaj Coster-Waldau",
    "total_ratings": 5749486
  },
  {
    "primaryName": "Julianne Moore",
    "total_ratings": 5744632
  },
  {
    "primaryName": "Jonah Hill",
    "total_ratings": 5718674
  },
  {
    "primaryName": "Joel Coen",
    "total_ratings": 5700458
  },
  {
    "primaryName": "Zack Snyder",
    "total_ratings": 5695090
  },
  {
    "primaryName": "Ben Stiller",
    "total_ratings": 5685709
  },
  {
    "primaryName": "Bryan Cranston",
    "total_ratings": 5685238
  },
  {
    "primaryName": "Kate Winslet",
    "total_ratings": 5682918
  },
  {
    "primaryName": "Ethan Coen",
    "total_ratings": 5663547
  },
  {
    "primaryName": "Walter F. Parkes",
    "total_ratings": 5657162
  },
  {
    "primaryName": "Howard Shore",
    "total_ratings": 5638214
  },
  {
    "primaryName": "Jeremy Renner",
    "total_ratings": 5623966
  },
  {
    "primaryName": "Marco Beltrami",
    "total_ratings": 5590317
  },
  {
    "primaryName": "Bryan Singer",
    "total_ratings": 5590237
  },
  {
    "primaryName": "Emma Stone",
    "total_ratings": 5583422
  },
  {
    "primaryName": "Guillermo del Toro",
    "total_ratings": 5570240
  },
  {
    "primaryName": "Owen Wilson",
    "total_ratings": 5556539
  },
  {
    "primaryName": "Rupert Grint",
    "total_ratings": 5552590
  },
  {
    "primaryName": "Jude Law",
    "total_ratings": 5538884
  },
  {
    "primaryName": "Matthew Vaughn",
    "total_ratings": 5524297
  },
  {
    "primaryName": "Alex Kurtzman",
    "total_ratings": 5522344
  },
  {
    "primaryName": "Lana Wachowski",
    "total_ratings": 5518169
  },
  {
    "primaryName": "Keira Knightley",
    "total_ratings": 5512152
  },
  {
    "primaryName": "Lilly Wachowski",
    "total_ratings": 5444217
  },
  {
    "primaryName": "Judd Apatow",
    "total_ratings": 5443590
  },
  {
    "primaryName": "Chris Pratt",
    "total_ratings": 5439043
  },
  {
    "primaryName": "Francis Ford Coppola",
    "total_ratings": 5436313
  },
  {
    "primaryName": "Will Ferrell",
    "total_ratings": 5420626
  },
  {
    "primaryName": "Seth Rogen",
    "total_ratings": 5419570
  },
  {
    "primaryName": "Sandra Bullock",
    "total_ratings": 5405221
  },
  {
    "primaryName": "Steve Ditko",
    "total_ratings": 5371488
  },
  {
    "primaryName": "Frank Marshall",
    "total_ratings": 5363210
  },
  {
    "primaryName": "John Logan",
    "total_ratings": 5353165
  },
  {
    "primaryName": "Ken Watanabe",
    "total_ratings": 5352344
  },
  {
    "primaryName": "Sam Raimi",
    "total_ratings": 5334765
  },
  {
    "primaryName": "Carrie-Anne Moss",
    "total_ratings": 5305827
  },
  {
    "primaryName": "Benedict Cumberbatch",
    "total_ratings": 5273615
  },
  {
    "primaryName": "Robin Williams",
    "total_ratings": 5224218
  },
  {
    "primaryName": "Jennifer Connelly",
    "total_ratings": 5222568
  },
  {
    "primaryName": "Ron Howard",
    "total_ratings": 5215903
  },
  {
    "primaryName": "John Travolta",
    "total_ratings": 5206564
  },
  {
    "primaryName": "Laurence Fishburne",
    "total_ratings": 5187514
  },
  {
    "primaryName": "Ethan Hawke",
    "total_ratings": 5186955
  },
  {
    "primaryName": "Jamie Foxx",
    "total_ratings": 5180837
  },
  {
    "primaryName": "Lawrence Kasdan",
    "total_ratings": 5173484
  },
  {
    "primaryName": "Gerard Butler",
    "total_ratings": 5156049
  }
]

SELECT 
   people_0."primaryName" as "primaryName",
   COALESCE((
        SELECT SUM(a.val) as value
        FROM (
          SELECT UNNEST(list(distinct {key:movies."__distinct_key", val: movies."numVotes"})) a
        )
      ),0) as "total_ratings"
FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.*  FROM '../data/titles.parquet' as x) as movies
 LEFT JOIN '../data/principals.parquet' AS principals_0
  ON movies."tconst"=principals_0."tconst"
 LEFT JOIN '../data/names.parquet' AS people_0
  ON principals_0."nconst"=people_0."nconst"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Index the entire graph

Indexing can work across an entire network of joins and can be selective.

run: movies -> {
  index:
    *
    genres.*
    principals.category, principals.job
    principals.characters.*
    principals.people.primaryName
  by total_ratings
  sample: 5000
} -> {
  select: *
  order_by: weight desc
}

QUERY RESULTS

fieldName	fieldPath	fieldType	fieldValue	weight
averageRating	averageRating	number	1.0 to 9.9	721,652,487
endYear	endYear	number	1964.0 to 2024.0	721,652,487
isAdult	isAdult	string	0	721,652,487
numVotes	numVotes	number	100038.0 to 2755400.0	721,652,487
runtimeMinutes	runtimeMinutes	number	100.0 to 780.0	721,652,487
startYear	startYear	number	1902.0 to 2023.0	721,652,487
∅	∅	∅	∅	721,652,487
principals.job	principals/job	string	\N	717,721,603
principals.category	principals/category	string	actor	709,468,881
principals.characters.value	principals/characters/value	string	\N	708,405,369
principals.category	principals/category	string	director	627,226,750
principals.category	principals/category	string	actress	620,388,199
principals.category	principals/category	string	writer	618,310,824
principals.category	principals/category	string	producer	548,608,109
principals.job	principals/job	string	producer	546,528,398
genres.value	genres/value	string	Drama	369,517,653
principals.category	principals/category	string	composer	331,132,732
genres.value	genres/value	string	Action	265,124,627
principals.category	principals/category	string	cinematographer	227,722,220
genres.value	genres/value	string	Adventure	212,822,116
genres.value	genres/value	string	Comedy	202,604,523
principals.job	principals/job	string	director of photography	175,426,435
principals.job	principals/job	string	screenplay	174,467,152
principals.job	principals/job	string	written by	174,233,523
genres.value	genres/value	string	Crime	145,371,034
principals.category	principals/category	string	editor	131,944,084
genres.value	genres/value	string	Thriller	118,363,640
genres.value	genres/value	string	Sci-Fi	106,757,403
principals.job	principals/job	string	screenplay by	104,399,359
genres.value	genres/value	string	Mystery	88,051,461
genres.value	genres/value	string	Romance	73,968,499
genres.value	genres/value	string	Fantasy	68,238,494
genres.value	genres/value	string	Horror	67,502,830
principals.job	principals/job	string	story	56,850,492
genres.value	genres/value	string	Animation	50,793,152
genres.value	genres/value	string	Biography	47,376,539
principals.job	principals/job	string	created by	41,740,554
principals.category	principals/category	string	production_designer	36,223,136
principals.job	principals/job	string	novel	35,036,364
principals.job	principals/job	string	story by	33,523,685
genres.value	genres/value	string	Family	30,145,444
principals.job	principals/job	string	characters	23,352,213
principals.job	principals/job	string	based on the novel by	20,896,431
genres.value	genres/value	string	History	19,265,314
principals.people.primaryName	principals/people/primaryName	string	John Williams	14,181,524
principals.job	principals/job	string	book	14,111,479
principals.people.primaryName	principals/people/primaryName	string	Steven Spielberg	13,842,773
principals.people.primaryName	principals/people/primaryName	string	Tom Hanks	13,841,598
principals.people.primaryName	principals/people/primaryName	string	Stan Lee	13,513,530
principals.people.primaryName	principals/people/primaryName	string	Leonardo DiCaprio	13,318,721
principals.job	principals/job	string	based on characters created by	12,841,929
principals.people.primaryName	principals/people/primaryName	string	Robert De Niro	12,670,471
genres.value	genres/value	string	War	12,538,183
principals.people.primaryName	principals/people/primaryName	string	Christopher Nolan	11,851,648
principals.people.primaryName	principals/people/primaryName	string	Brad Pitt	11,800,067
principals.people.primaryName	principals/people/primaryName	string	George Lucas	10,556,861
principals.job	principals/job	string	film editor	10,498,481
genres.value	genres/value	string	Sport	10,466,316
principals.job	principals/job	string	co-director	10,415,380
genres.value	genres/value	string	Music	10,340,766
principals.people.primaryName	principals/people/primaryName	string	Matt Damon	10,132,138
principals.people.primaryName	principals/people/primaryName	string	Tom Cruise	9,919,404
principals.job	principals/job	string	developed by	9,549,826
principals.people.primaryName	principals/people/primaryName	string	Christian Bale	9,340,646
principals.people.primaryName	principals/people/primaryName	string	Hans Zimmer	9,318,476
principals.people.primaryName	principals/people/primaryName	string	Robert Downey Jr.	9,265,752
principals.people.primaryName	principals/people/primaryName	string	Ian McKellen	9,242,947
principals.people.primaryName	principals/people/primaryName	string	Samuel L. Jackson	9,147,638
principals.people.primaryName	principals/people/primaryName	string	Emma Thomas	8,773,124
principals.people.primaryName	principals/people/primaryName	string	Stephen King	8,696,212
principals.people.primaryName	principals/people/primaryName	string	Peter Jackson	8,602,818
principals.people.primaryName	principals/people/primaryName	string	Jack Kirby	8,481,368
principals.people.primaryName	principals/people/primaryName	string	Scott Rudin	8,224,834
principals.people.primaryName	principals/people/primaryName	string	Liam Neeson	8,036,213
principals.people.primaryName	principals/people/primaryName	string	Morgan Freeman	8,030,826
principals.people.primaryName	principals/people/primaryName	string	Martin Scorsese	7,942,225
principals.people.primaryName	principals/people/primaryName	string	Harrison Ford	7,928,868
principals.people.primaryName	principals/people/primaryName	string	Scarlett Johansson	7,901,679
principals.job	principals/job	string	screen story	7,831,426
principals.people.primaryName	principals/people/primaryName	string	Fran Walsh	7,821,878
principals.people.primaryName	principals/people/primaryName	string	Hugh Jackman	7,774,022
principals.people.primaryName	principals/people/primaryName	string	Thomas Newman	7,767,854
principals.people.primaryName	principals/people/primaryName	string	Bob Kane	7,764,131
principals.people.primaryName	principals/people/primaryName	string	Kathleen Kennedy	7,755,179
principals.people.primaryName	principals/people/primaryName	string	Al Pacino	7,658,993
principals.people.primaryName	principals/people/primaryName	string	Philippa Boyens	7,655,853
principals.people.primaryName	principals/people/primaryName	string	Edward Norton	7,629,927
principals.people.primaryName	principals/people/primaryName	string	Bradley Cooper	7,507,718
principals.people.primaryName	principals/people/primaryName	string	Johnny Depp	7,447,589
principals.people.primaryName	principals/people/primaryName	string	Simon Kinberg	7,422,420
principals.people.primaryName	principals/people/primaryName	string	Kevin Feige	7,349,228
principals.people.primaryName	principals/people/primaryName	string	Chris Evans	7,315,223
principals.job	principals/job	string	based on the book by	7,248,079
principals.people.primaryName	principals/people/primaryName	string	Orlando Bloom	7,199,739
principals.people.primaryName	principals/people/primaryName	string	Natalie Portman	7,175,031
principals.people.primaryName	principals/people/primaryName	string	Jonathan Nolan	7,094,902
principals.characters.value	principals/characters/value	string	Bruce Wayne	7,094,588
principals.people.primaryName	principals/people/primaryName	string	James Newton Howard	7,066,858
principals.people.primaryName	principals/people/primaryName	string	J.R.R. Tolkien	7,050,313
principals.people.primaryName	principals/people/primaryName	string	James Horner	7,022,605
principals.people.primaryName	principals/people/primaryName	string	Joel Silver	7,006,978
principals.people.primaryName	principals/people/primaryName	string	Mark Ruffalo	6,935,178
principals.characters.value	principals/characters/value	string	Gandalf	6,916,005
principals.people.primaryName	principals/people/primaryName	string	Will Smith	6,896,084
principals.people.primaryName	principals/people/primaryName	string	Charles Roven	6,871,823
principals.people.primaryName	principals/people/primaryName	string	Bruce Willis	6,847,496
principals.job	principals/job	string	based on the Marvel comics by	6,694,506
principals.people.primaryName	principals/people/primaryName	string	Anne Hathaway	6,670,061
principals.characters.value	principals/characters/value	string	Batman	6,658,502
principals.people.primaryName	principals/people/primaryName	string	Robert Richardson	6,644,386
principals.people.primaryName	principals/people/primaryName	string	Ridley Scott	6,445,137
principals.people.primaryName	principals/people/primaryName	string	Roger Deakins	6,428,335
principals.people.primaryName	principals/people/primaryName	string	Michael Giacchino	6,403,674
principals.people.primaryName	principals/people/primaryName	string	Keanu Reeves	6,358,678
principals.people.primaryName	principals/people/primaryName	string	Arnon Milchan	6,318,578
principals.people.primaryName	principals/people/primaryName	string	Elijah Wood	6,282,511
principals.people.primaryName	principals/people/primaryName	string	David S. Goyer	6,225,369
principals.characters.value	principals/characters/value	string	Tony Stark	6,171,515
principals.people.primaryName	principals/people/primaryName	string	Ryan Reynolds	6,158,791
principals.people.primaryName	principals/people/primaryName	string	David Benioff	6,145,642
principals.people.primaryName	principals/people/primaryName	string	Cate Blanchett	6,141,002
principals.people.primaryName	principals/people/primaryName	string	Robert Zemeckis	6,135,114
principals.people.primaryName	principals/people/primaryName	string	Matthew McConaughey	6,105,031
principals.people.primaryName	principals/people/primaryName	string	Alan Silvestri	6,090,595
principals.people.primaryName	principals/people/primaryName	string	Mark Wahlberg	6,078,410
principals.people.primaryName	principals/people/primaryName	string	James Cameron	6,044,103
principals.people.primaryName	principals/people/primaryName	string	Tim Bevan	5,950,348
principals.people.primaryName	principals/people/primaryName	string	Tim Burton	5,944,551
principals.people.primaryName	principals/people/primaryName	string	Neal H. Moritz	5,919,869
principals.people.primaryName	principals/people/primaryName	string	Chris Hemsworth	5,874,234
principals.people.primaryName	principals/people/primaryName	string	Tom Hardy	5,853,470
principals.people.primaryName	principals/people/primaryName	string	Willem Dafoe	5,801,796
principals.people.primaryName	principals/people/primaryName	string	Joseph Gordon-Levitt	5,794,212
principals.people.primaryName	principals/people/primaryName	string	Quentin Tarantino	5,793,946
principals.people.primaryName	principals/people/primaryName	string	Lorne Orleans	5,790,363
principals.people.primaryName	principals/people/primaryName	string	Lena Headey	5,781,252
principals.people.primaryName	principals/people/primaryName	string	Alexandre Desplat	5,767,491
principals.people.primaryName	principals/people/primaryName	string	Frank Darabont	5,739,001
principals.people.primaryName	principals/people/primaryName	string	Eric Fellner	5,736,577
principals.people.primaryName	principals/people/primaryName	string	J.J. Abrams	5,733,776
principals.people.primaryName	principals/people/primaryName	string	Brian Grazer	5,733,592
principals.people.primaryName	principals/people/primaryName	string	David Heyman	5,728,200
principals.people.primaryName	principals/people/primaryName	string	David Fincher	5,712,027
principals.people.primaryName	principals/people/primaryName	string	Jim Carrey	5,704,878
principals.people.primaryName	principals/people/primaryName	string	Rachel McAdams	5,679,232
principals.people.primaryName	principals/people/primaryName	string	Michael Bay	5,637,406
principals.people.primaryName	principals/people/primaryName	string	Clint Eastwood	5,633,136
principals.people.primaryName	principals/people/primaryName	string	Dwayne Johnson	5,586,616
principals.people.primaryName	principals/people/primaryName	string	Colin Farrell	5,568,446
principals.people.primaryName	principals/people/primaryName	string	Jake Gyllenhaal	5,522,176
principals.characters.value	principals/characters/value	string	Legolas	5,521,522
principals.characters.value	principals/characters/value	string	Frodo	5,521,522
principals.people.primaryName	principals/people/primaryName	string	Denzel Washington	5,491,112
principals.people.primaryName	principals/people/primaryName	string	Viggo Mortensen	5,489,611
principals.people.primaryName	principals/people/primaryName	string	Danny Elfman	5,431,083
principals.people.primaryName	principals/people/primaryName	string	Anthony Hopkins	5,414,129
principals.characters.value	principals/characters/value	string	Captain America	5,408,600
principals.people.primaryName	principals/people/primaryName	string	Ceán Chaffin	5,403,636
principals.people.primaryName	principals/people/primaryName	string	Sigourney Weaver	5,334,888
principals.people.primaryName	principals/people/primaryName	string	Jack Nicholson	5,328,133
principals.people.primaryName	principals/people/primaryName	string	Ewan McGregor	5,311,349
principals.characters.value	principals/characters/value	string	Jack	5,263,109
principals.people.primaryName	principals/people/primaryName	string	Kate Winslet	5,261,544
principals.people.primaryName	principals/people/primaryName	string	Ralph Fiennes	5,250,916
principals.people.primaryName	principals/people/primaryName	string	D.B. Weiss	5,228,476
principals.people.primaryName	principals/people/primaryName	string	Carter Burwell	5,220,882
principals.people.primaryName	principals/people/primaryName	string	John Goodman	5,183,237
principals.people.primaryName	principals/people/primaryName	string	Jeremy Renner	5,177,562
principals.characters.value	principals/characters/value	string	Steve Rogers	5,173,578
principals.people.primaryName	principals/people/primaryName	string	Peter Dinklage	5,163,652
principals.people.primaryName	principals/people/primaryName	string	Ken Watanabe	5,160,890
principals.people.primaryName	principals/people/primaryName	string	Michael Caine	5,090,036
principals.people.primaryName	principals/people/primaryName	string	Ian Bryce	5,057,783
principals.people.primaryName	principals/people/primaryName	string	Mel Gibson	5,048,545
principals.people.primaryName	principals/people/primaryName	string	Tim Robbins	5,030,481
principals.people.primaryName	principals/people/primaryName	string	Jennifer Lawrence	5,026,536
principals.people.primaryName	principals/people/primaryName	string	Frank Marshall	5,012,499
principals.people.primaryName	principals/people/primaryName	string	Nikolaj Coster-Waldau	5,004,462
principals.people.primaryName	principals/people/primaryName	string	Nicolas Cage	4,970,441
principals.people.primaryName	principals/people/primaryName	string	Russell Crowe	4,956,663
principals.people.primaryName	principals/people/primaryName	string	Joss Whedon	4,944,908
principals.people.primaryName	principals/people/primaryName	string	Adam Sandler	4,934,805
principals.people.primaryName	principals/people/primaryName	string	Jason Statham	4,880,956
principals.job	principals/job	string	creator	4,877,245
principals.people.primaryName	principals/people/primaryName	string	Jennifer Connelly	4,859,510
principals.people.primaryName	principals/people/primaryName	string	Woody Harrelson	4,843,130
principals.people.primaryName	principals/people/primaryName	string	Ben Affleck	4,834,433
principals.characters.value	principals/characters/value	string	Michael	4,819,586
principals.people.primaryName	principals/people/primaryName	string	Rick McCallum	4,814,267
principals.people.primaryName	principals/people/primaryName	string	Steven Zaillian	4,813,485
principals.people.primaryName	principals/people/primaryName	string	Arnold Schwarzenegger	4,787,592
principals.people.primaryName	principals/people/primaryName	string	Hugo Weaving	4,778,610
principals.characters.value	principals/characters/value	string	Narrator	4,743,957
principals.people.primaryName	principals/people/primaryName	string	Marco Beltrami	4,733,583
principals.people.primaryName	principals/people/primaryName	string	Pete Docter	4,715,101
principals.people.primaryName	principals/people/primaryName	string	Carrie-Anne Moss	4,695,817
principals.people.primaryName	principals/people/primaryName	string	Ed Harris	4,694,203
principals.people.primaryName	principals/people/primaryName	string	Emilia Clarke	4,691,006
principals.people.primaryName	principals/people/primaryName	string	Bryan Singer	4,673,906
principals.people.primaryName	principals/people/primaryName	string	Walter F. Parkes	4,668,444

[
  {
    "fieldName": "averageRating",
    "fieldPath": "averageRating",
    "fieldType": "number",
    "fieldValue": "1.0 to 9.9",
    "weight": 721652487
  },
  {
    "fieldName": "endYear",
    "fieldPath": "endYear",
    "fieldType": "number",
    "fieldValue": "1964.0 to 2024.0",
    "weight": 721652487
  },
  {
    "fieldName": "isAdult",
    "fieldPath": "isAdult",
    "fieldType": "string",
    "fieldValue": "0",
    "weight": 721652487
  },
  {
    "fieldName": "numVotes",
    "fieldPath": "numVotes",
    "fieldType": "number",
    "fieldValue": "100038.0 to 2755400.0",
    "weight": 721652487
  },
  {
    "fieldName": "runtimeMinutes",
    "fieldPath": "runtimeMinutes",
    "fieldType": "number",
    "fieldValue": "100.0 to 780.0",
    "weight": 721652487
  },
  {
    "fieldName": "startYear",
    "fieldPath": "startYear",
    "fieldType": "number",
    "fieldValue": "1902.0 to 2023.0",
    "weight": 721652487
  },
  {
    "fieldName": null,
    "fieldPath": null,
    "fieldType": null,
    "fieldValue": null,
    "weight": 721652487
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "\\N",
    "weight": 717721603
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "actor",
    "weight": 709468881
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "\\N",
    "weight": 708405369
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "director",
    "weight": 627226750
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "actress",
    "weight": 620388199
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "writer",
    "weight": 618310824
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "producer",
    "weight": 548608109
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "producer",
    "weight": 546528398
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Drama",
    "weight": 369517653
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "composer",
    "weight": 331132732
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Action",
    "weight": 265124627
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "cinematographer",
    "weight": 227722220
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Adventure",
    "weight": 212822116
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Comedy",
    "weight": 202604523
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "director of photography",
    "weight": 175426435
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screenplay",
    "weight": 174467152
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "written by",
    "weight": 174233523
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Crime",
    "weight": 145371034
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "editor",
    "weight": 131944084
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Thriller",
    "weight": 118363640
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Sci-Fi",
    "weight": 106757403
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screenplay by",
    "weight": 104399359
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Mystery",
    "weight": 88051461
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Romance",
    "weight": 73968499
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Fantasy",
    "weight": 68238494
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Horror",
    "weight": 67502830
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "story",
    "weight": 56850492
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Animation",
    "weight": 50793152
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Biography",
    "weight": 47376539
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "created by",
    "weight": 41740554
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "production_designer",
    "weight": 36223136
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "novel",
    "weight": 35036364
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "story by",
    "weight": 33523685
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Family",
    "weight": 30145444
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "characters",
    "weight": 23352213
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the novel by",
    "weight": 20896431
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "History",
    "weight": 19265314
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "John Williams",
    "weight": 14181524
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "book",
    "weight": 14111479
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steven Spielberg",
    "weight": 13842773
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Hanks",
    "weight": 13841598
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stan Lee",
    "weight": 13513530
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Leonardo DiCaprio",
    "weight": 13318721
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on characters created by",
    "weight": 12841929
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert De Niro",
    "weight": 12670471
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "War",
    "weight": 12538183
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christopher Nolan",
    "weight": 11851648
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Pitt",
    "weight": 11800067
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "George Lucas",
    "weight": 10556861
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "film editor",
    "weight": 10498481
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Sport",
    "weight": 10466316
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "co-director",
    "weight": 10415380
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Music",
    "weight": 10340766
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Matt Damon",
    "weight": 10132138
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Cruise",
    "weight": 9919404
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "developed by",
    "weight": 9549826
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christian Bale",
    "weight": 9340646
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hans Zimmer",
    "weight": 9318476
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Downey Jr.",
    "weight": 9265752
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ian McKellen",
    "weight": 9242947
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Samuel L. Jackson",
    "weight": 9147638
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emma Thomas",
    "weight": 8773124
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stephen King",
    "weight": 8696212
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Peter Jackson",
    "weight": 8602818
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jack Kirby",
    "weight": 8481368
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Scott Rudin",
    "weight": 8224834
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Liam Neeson",
    "weight": 8036213
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Morgan Freeman",
    "weight": 8030826
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Martin Scorsese",
    "weight": 7942225
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Harrison Ford",
    "weight": 7928868
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Scarlett Johansson",
    "weight": 7901679
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screen story",
    "weight": 7831426
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Fran Walsh",
    "weight": 7821878
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hugh Jackman",
    "weight": 7774022
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Thomas Newman",
    "weight": 7767854
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bob Kane",
    "weight": 7764131
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kathleen Kennedy",
    "weight": 7755179
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Al Pacino",
    "weight": 7658993
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Philippa Boyens",
    "weight": 7655853
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Edward Norton",
    "weight": 7629927
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Cooper",
    "weight": 7507718
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Johnny Depp",
    "weight": 7447589
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Simon Kinberg",
    "weight": 7422420
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kevin Feige",
    "weight": 7349228
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Chris Evans",
    "weight": 7315223
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the book by",
    "weight": 7248079
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Orlando Bloom",
    "weight": 7199739
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Natalie Portman",
    "weight": 7175031
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jonathan Nolan",
    "weight": 7094902
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bruce Wayne",
    "weight": 7094588
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Newton Howard",
    "weight": 7066858
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "J.R.R. Tolkien",
    "weight": 7050313
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Horner",
    "weight": 7022605
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joel Silver",
    "weight": 7006978
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mark Ruffalo",
    "weight": 6935178
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Gandalf",
    "weight": 6916005
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Will Smith",
    "weight": 6896084
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Charles Roven",
    "weight": 6871823
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bruce Willis",
    "weight": 6847496
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the Marvel comics by",
    "weight": 6694506
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Anne Hathaway",
    "weight": 6670061
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batman",
    "weight": 6658502
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Richardson",
    "weight": 6644386
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ridley Scott",
    "weight": 6445137
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Roger Deakins",
    "weight": 6428335
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Giacchino",
    "weight": 6403674
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Keanu Reeves",
    "weight": 6358678
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Arnon Milchan",
    "weight": 6318578
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Elijah Wood",
    "weight": 6282511
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David S. Goyer",
    "weight": 6225369
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Tony Stark",
    "weight": 6171515
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ryan Reynolds",
    "weight": 6158791
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Benioff",
    "weight": 6145642
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Cate Blanchett",
    "weight": 6141002
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Zemeckis",
    "weight": 6135114
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Matthew McConaughey",
    "weight": 6105031
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Alan Silvestri",
    "weight": 6090595
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mark Wahlberg",
    "weight": 6078410
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Cameron",
    "weight": 6044103
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Bevan",
    "weight": 5950348
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Burton",
    "weight": 5944551
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Neal H. Moritz",
    "weight": 5919869
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Chris Hemsworth",
    "weight": 5874234
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Hardy",
    "weight": 5853470
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Willem Dafoe",
    "weight": 5801796
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joseph Gordon-Levitt",
    "weight": 5794212
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Quentin Tarantino",
    "weight": 5793946
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lorne Orleans",
    "weight": 5790363
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lena Headey",
    "weight": 5781252
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Alexandre Desplat",
    "weight": 5767491
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Frank Darabont",
    "weight": 5739001
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Eric Fellner",
    "weight": 5736577
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "J.J. Abrams",
    "weight": 5733776
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brian Grazer",
    "weight": 5733592
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Heyman",
    "weight": 5728200
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Fincher",
    "weight": 5712027
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jim Carrey",
    "weight": 5704878
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Rachel McAdams",
    "weight": 5679232
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Bay",
    "weight": 5637406
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Clint Eastwood",
    "weight": 5633136
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Dwayne Johnson",
    "weight": 5586616
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Colin Farrell",
    "weight": 5568446
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jake Gyllenhaal",
    "weight": 5522176
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Legolas",
    "weight": 5521522
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Frodo",
    "weight": 5521522
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Denzel Washington",
    "weight": 5491112
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Viggo Mortensen",
    "weight": 5489611
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Danny Elfman",
    "weight": 5431083
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Anthony Hopkins",
    "weight": 5414129
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Captain America",
    "weight": 5408600
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ceán Chaffin",
    "weight": 5403636
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Sigourney Weaver",
    "weight": 5334888
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jack Nicholson",
    "weight": 5328133
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ewan McGregor",
    "weight": 5311349
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Jack",
    "weight": 5263109
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kate Winslet",
    "weight": 5261544
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ralph Fiennes",
    "weight": 5250916
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "D.B. Weiss",
    "weight": 5228476
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Carter Burwell",
    "weight": 5220882
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "John Goodman",
    "weight": 5183237
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jeremy Renner",
    "weight": 5177562
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Steve Rogers",
    "weight": 5173578
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Peter Dinklage",
    "weight": 5163652
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ken Watanabe",
    "weight": 5160890
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Caine",
    "weight": 5090036
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ian Bryce",
    "weight": 5057783
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mel Gibson",
    "weight": 5048545
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Robbins",
    "weight": 5030481
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jennifer Lawrence",
    "weight": 5026536
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Frank Marshall",
    "weight": 5012499
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Nikolaj Coster-Waldau",
    "weight": 5004462
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Nicolas Cage",
    "weight": 4970441
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Russell Crowe",
    "weight": 4956663
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joss Whedon",
    "weight": 4944908
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Adam Sandler",
    "weight": 4934805
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jason Statham",
    "weight": 4880956
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "creator",
    "weight": 4877245
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jennifer Connelly",
    "weight": 4859510
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Woody Harrelson",
    "weight": 4843130
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ben Affleck",
    "weight": 4834433
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Michael",
    "weight": 4819586
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Rick McCallum",
    "weight": 4814267
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steven Zaillian",
    "weight": 4813485
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Arnold Schwarzenegger",
    "weight": 4787592
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hugo Weaving",
    "weight": 4778610
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Narrator",
    "weight": 4743957
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Marco Beltrami",
    "weight": 4733583
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Pete Docter",
    "weight": 4715101
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Carrie-Anne Moss",
    "weight": 4695817
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ed Harris",
    "weight": 4694203
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emilia Clarke",
    "weight": 4691006
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bryan Singer",
    "weight": 4673906
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Walter F. Parkes",
    "weight": 4668444
  }
]

WITH __stage0 AS (
  SELECT * from (SELECT * FROM '../data/titles.parquet' USING SAMPLE 5000) as x limit 100000 )
, __stage1 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'averageRating'
      WHEN 1 THEN 'endYear'
      WHEN 2 THEN 'isAdult'
      WHEN 3 THEN 'numVotes'
      WHEN 4 THEN 'originalTitle'
      WHEN 5 THEN 'primaryTitle'
      WHEN 6 THEN 'runtimeMinutes'
      WHEN 7 THEN 'startYear'
      WHEN 8 THEN 'tconst'
      WHEN 9 THEN 'genres.value'
      WHEN 10 THEN 'principals.category'
      WHEN 11 THEN 'principals.job'
      WHEN 12 THEN 'principals.characters.value'
      WHEN 13 THEN 'principals.people.primaryName'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'averageRating'
      WHEN 1 THEN 'endYear'
      WHEN 2 THEN 'isAdult'
      WHEN 3 THEN 'numVotes'
      WHEN 4 THEN 'originalTitle'
      WHEN 5 THEN 'primaryTitle'
      WHEN 6 THEN 'runtimeMinutes'
      WHEN 7 THEN 'startYear'
      WHEN 8 THEN 'tconst'
      WHEN 9 THEN 'genres/value'
      WHEN 10 THEN 'principals/category'
      WHEN 11 THEN 'principals/job'
      WHEN 12 THEN 'principals/characters/value'
      WHEN 13 THEN 'principals/people/primaryName'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'number'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'number'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'number'
      WHEN 7 THEN 'number'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN CAST(NULL as VARCHAR)
      WHEN 2 THEN movies."isAdult"
      WHEN 4 THEN movies."originalTitle"
      WHEN 5 THEN movies."primaryTitle"
      WHEN 8 THEN movies."tconst"
      WHEN 9 THEN genres_0
      WHEN 10 THEN principals_0."category"
      WHEN 11 THEN principals_0."job"
      WHEN 12 THEN characters_0
      WHEN 13 THEN people_0."primaryName"
    END as "fieldValue",
   COALESCE((
        SELECT SUM(a.val) as value
        FROM (
          SELECT UNNEST(list(distinct {key:movies."__distinct_key", val: movies."numVotes"})) a
        )
      ),0) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(movies."averageRating" as VARCHAR)) || ' to ' || CAST(MAX(movies."averageRating") as VARCHAR)
      WHEN 1 THEN MIN(CAST(movies."endYear" as VARCHAR)) || ' to ' || CAST(MAX(movies."endYear") as VARCHAR)
      WHEN 3 THEN MIN(CAST(movies."numVotes" as VARCHAR)) || ' to ' || CAST(MAX(movies."numVotes") as VARCHAR)
      WHEN 6 THEN MIN(CAST(movies."runtimeMinutes" as VARCHAR)) || ' to ' || CAST(MAX(movies."runtimeMinutes") as VARCHAR)
      WHEN 7 THEN MIN(CAST(movies."startYear" as VARCHAR)) || ' to ' || CAST(MAX(movies."startYear") as VARCHAR)
    END as "fieldRange"
  FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.*  FROM __stage0 as x) as movies
  LEFT JOIN LATERAL (SELECT UNNEST(movies."genres"), 1 as ignoreme) as genres_0_outer(genres_0,ignoreme) ON genres_0_outer.ignoreme=1
   LEFT JOIN '../data/principals.parquet' AS principals_0
    ON movies."tconst"=principals_0."tconst"
  LEFT JOIN LATERAL (SELECT UNNEST(principals_0."characters"), 1 as ignoreme) as characters_0_outer(characters_0,ignoreme) ON characters_0_outer.ignoreme=1
   LEFT JOIN '../data/names.parquet' AS people_0
    ON principals_0."nconst"=people_0."nconst"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,14,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4,5
)
, __stage2 AS (
  SELECT
    "fieldName",
    "fieldPath",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage1
)
SELECT 
   base."fieldName" as "fieldName",
   base."fieldPath" as "fieldPath",
   base."fieldType" as "fieldType",
   base."fieldValue" as "fieldValue",
   base."weight" as "weight"
FROM __stage2 as base
ORDER BY 5 desc NULLS LAST

Search Index Naming Convention

By convention indexes in sources are named search_index.

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

  measure: total_ratings is numVotes.sum()

  view: search_index is {
    index:
      *
      genres.*
      principals.category, principals.job
      principals.characters.*
      principals.people.primaryName
    by total_ratings
  }
}

So to look for 'Brad'

run: movies2 -> search_index -> { 
  select: *
  where: fieldValue ~ 'Brad%'
  order_by: weight desc
}

QUERY RESULTS

fieldName	fieldPath	fieldType	fieldValue	weight
principals.people.primaryName	principals/people/primaryName	string	Brad Pitt	18,269,386
principals.people.primaryName	principals/people/primaryName	string	Bradley Cooper	8,980,668
principals.people.primaryName	principals/people/primaryName	string	Brad Bird	2,783,448
principals.people.primaryName	principals/people/primaryName	string	Bradley J. Fischer	2,582,567
principals.people.primaryName	principals/people/primaryName	string	Brad Fiedel	2,516,157
principals.people.primaryName	principals/people/primaryName	string	Brad Grey	1,858,689
principals.people.primaryName	principals/people/primaryName	string	Brad Fuller	1,589,845
principals.people.primaryName	principals/people/primaryName	string	Brad Garrett	989,180
principals.people.primaryName	principals/people/primaryName	string	Bradley Whitford	908,639
principals.people.primaryName	principals/people/primaryName	string	Brad Anderson	785,214
principals.people.primaryName	principals/people/primaryName	string	Bradley Thomas	672,907
principals.people.primaryName	principals/people/primaryName	string	Brad Dourif	595,034
principals.people.primaryName	principals/people/primaryName	string	Brad Falchuk	594,475
principals.people.primaryName	principals/people/primaryName	string	Brad Krevoy	593,257
principals.people.primaryName	principals/people/primaryName	string	Bradford Lewis	566,153
principals.people.primaryName	principals/people/primaryName	string	Brad Peyton	565,084
principals.characters.value	principals/characters/value	string	Brad Bellick	553,782
principals.people.primaryName	principals/people/primaryName	string	Brad Silberling	552,092
principals.characters.value	principals/characters/value	string	Brad Gurdlinger	466,826
principals.people.primaryName	principals/people/primaryName	string	Brad Ingelsby	452,543
principals.people.primaryName	principals/people/primaryName	string	Brad Segal	402,692
principals.people.primaryName	principals/people/primaryName	string	Brad Furman	385,968
principals.people.primaryName	principals/people/primaryName	string	Brad Mann	384,595
principals.people.primaryName	principals/people/primaryName	string	Braden Aftergood	374,772
principals.people.primaryName	principals/people/primaryName	string	Brad Winderbaum	366,995
principals.people.primaryName	principals/people/primaryName	string	Brad Swaile	345,759
principals.people.primaryName	principals/people/primaryName	string	Brad Wright	278,557
principals.people.primaryName	principals/people/primaryName	string	Brad Renfro	273,848
principals.characters.value	principals/characters/value	string	Bradley Fine	250,952
principals.people.primaryName	principals/people/primaryName	string	Brad Greenquist	250,909
principals.characters.value	principals/characters/value	string	Brad	245,925
principals.people.primaryName	principals/people/primaryName	string	Brad Copeland	234,415
principals.characters.value	principals/characters/value	string	Brad Whitaker	226,799
principals.people.primaryName	principals/people/primaryName	string	Brad Epstein	197,059
principals.people.primaryName	principals/people/primaryName	string	Bradley Gallo	184,654
principals.people.primaryName	principals/people/primaryName	string	Brady Corbet	173,307
principals.characters.value	principals/characters/value	string	Brad Majors - A Hero	158,457
principals.people.primaryName	principals/people/primaryName	string	Brad Wyman	156,357
principals.people.primaryName	principals/people/primaryName	string	Brad Davis	150,273
principals.people.primaryName	principals/people/primaryName	string	Bradford Dillman	131,412
principals.characters.value	principals/characters/value	string	Brad Adamson	113,849
principals.characters.value	principals/characters/value	string	Bradley Martin	112,103
principals.characters.value	principals/characters/value	string	Bradley Jackson	110,209
principals.characters.value	principals/characters/value	string	Brad Hamilton	110,039
principals.people.primaryName	principals/people/primaryName	string	Brad Kane	86,923
principals.people.primaryName	principals/people/primaryName	string	Brad Halvorson	85,643
principals.people.primaryName	principals/people/primaryName	string	Bradley James	84,478
principals.characters.value	principals/characters/value	string	Brad Cage	82,655
principals.characters.value	principals/characters/value	string	Brad Wesley	80,561
principals.people.primaryName	principals/people/primaryName	string	Bradford Lipson	78,284
principals.people.primaryName	principals/people/primaryName	string	Brady Noon	77,389
principals.people.primaryName	principals/people/primaryName	string	Brad Ableson	75,588
principals.people.primaryName	principals/people/primaryName	string	Bradford Young	75,006
principals.people.primaryName	principals/people/primaryName	string	Brad Booker	74,354
principals.people.primaryName	principals/people/primaryName	string	Brad Riddell	73,922
principals.people.primaryName	principals/people/primaryName	string	Brad Gann	73,337
principals.people.primaryName	principals/people/primaryName	string	Bradley Stonesifer	71,122
principals.characters.value	principals/characters/value	string	Bradley Thomas	71,027
principals.people.primaryName	principals/people/primaryName	string	Brad Leland	69,259
principals.characters.value	principals/characters/value	string	Brad Lewis	68,518
principals.characters.value	principals/characters/value	string	Brad Gregory	67,955
principals.people.primaryName	principals/people/primaryName	string	Bradley Parker	67,355
principals.characters.value	principals/characters/value	string	Brad Stand	65,526
principals.characters.value	principals/characters/value	string	Brad (segment \Tape 56\)	65,431
principals.people.primaryName	principals/people/primaryName	string	Brady Coleman	61,797
principals.characters.value	principals/characters/value	string	Brad Chang	59,267
principals.people.primaryName	principals/people/primaryName	string	Brad Beyer	57,584
principals.people.primaryName	principals/people/primaryName	string	Brad Weston	51,673
principals.characters.value	principals/characters/value	string	Brad Bottig	51,451
principals.characters.value	principals/characters/value	string	Brad Chase	48,224
principals.people.primaryName	principals/people/primaryName	string	Bradley King	47,442
principals.people.primaryName	principals/people/primaryName	string	Brad Loree	46,958
principals.characters.value	principals/characters/value	string	Brad Harris	46,640
principals.people.primaryName	principals/people/primaryName	string	Brad Morris	46,105
principals.characters.value	principals/characters/value	string	Brad Taylor	46,084
principals.people.primaryName	principals/people/primaryName	string	Bradley Raymond	43,731
principals.people.primaryName	principals/people/primaryName	string	Bradley Jenkel	42,784
principals.people.primaryName	principals/people/primaryName	string	Brad M. Gilbert	39,777
principals.people.primaryName	principals/people/primaryName	string	Brad Baruh	39,491
principals.characters.value	principals/characters/value	string	Brad Williams	39,300
principals.characters.value	principals/characters/value	string	Bradley	38,412
principals.characters.value	principals/characters/value	string	Brad Brown	37,424
principals.people.primaryName	principals/people/primaryName	string	Bradford L. Schlei	35,007
principals.people.primaryName	principals/people/primaryName	string	Brad Silverman	34,490
principals.people.primaryName	principals/people/primaryName	string	Brad Simpson	33,880
principals.people.primaryName	principals/people/primaryName	string	Bradford May	33,729
principals.people.primaryName	principals/people/primaryName	string	Brad Johnson	31,763

[
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Pitt",
    "weight": 18269386
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Cooper",
    "weight": 8980668
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Bird",
    "weight": 2783448
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley J. Fischer",
    "weight": 2582567
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Fiedel",
    "weight": 2516157
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Grey",
    "weight": 1858689
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Fuller",
    "weight": 1589845
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Garrett",
    "weight": 989180
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Whitford",
    "weight": 908639
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Anderson",
    "weight": 785214
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Thomas",
    "weight": 672907
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Dourif",
    "weight": 595034
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Falchuk",
    "weight": 594475
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Krevoy",
    "weight": 593257
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradford Lewis",
    "weight": 566153
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Peyton",
    "weight": 565084
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Bellick",
    "weight": 553782
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Silberling",
    "weight": 552092
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Gurdlinger",
    "weight": 466826
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Ingelsby",
    "weight": 452543
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Segal",
    "weight": 402692
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Furman",
    "weight": 385968
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Mann",
    "weight": 384595
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Braden Aftergood",
    "weight": 374772
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Winderbaum",
    "weight": 366995
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Swaile",
    "weight": 345759
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Wright",
    "weight": 278557
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Renfro",
    "weight": 273848
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bradley Fine",
    "weight": 250952
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Greenquist",
    "weight": 250909
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad",
    "weight": 245925
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Copeland",
    "weight": 234415
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Whitaker",
    "weight": 226799
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Epstein",
    "weight": 197059
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Gallo",
    "weight": 184654
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brady Corbet",
    "weight": 173307
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Majors - A Hero",
    "weight": 158457
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Wyman",
    "weight": 156357
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Davis",
    "weight": 150273
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradford Dillman",
    "weight": 131412
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Adamson",
    "weight": 113849
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bradley Martin",
    "weight": 112103
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bradley Jackson",
    "weight": 110209
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Hamilton",
    "weight": 110039
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Kane",
    "weight": 86923
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Halvorson",
    "weight": 85643
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley James",
    "weight": 84478
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Cage",
    "weight": 82655
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Wesley",
    "weight": 80561
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradford Lipson",
    "weight": 78284
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brady Noon",
    "weight": 77389
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Ableson",
    "weight": 75588
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradford Young",
    "weight": 75006
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Booker",
    "weight": 74354
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Riddell",
    "weight": 73922
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Gann",
    "weight": 73337
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Stonesifer",
    "weight": 71122
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bradley Thomas",
    "weight": 71027
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Leland",
    "weight": 69259
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Lewis",
    "weight": 68518
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Gregory",
    "weight": 67955
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Parker",
    "weight": 67355
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Stand",
    "weight": 65526
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad (segment \\Tape 56\\)",
    "weight": 65431
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brady Coleman",
    "weight": 61797
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Chang",
    "weight": 59267
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Beyer",
    "weight": 57584
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Weston",
    "weight": 51673
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Bottig",
    "weight": 51451
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Chase",
    "weight": 48224
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley King",
    "weight": 47442
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Loree",
    "weight": 46958
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Harris",
    "weight": 46640
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Morris",
    "weight": 46105
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Taylor",
    "weight": 46084
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Raymond",
    "weight": 43731
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Jenkel",
    "weight": 42784
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad M. Gilbert",
    "weight": 39777
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Baruh",
    "weight": 39491
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Williams",
    "weight": 39300
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bradley",
    "weight": 38412
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Brad Brown",
    "weight": 37424
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradford L. Schlei",
    "weight": 35007
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Silverman",
    "weight": 34490
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Simpson",
    "weight": 33880
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradford May",
    "weight": 33729
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Johnson",
    "weight": 31763
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'averageRating'
      WHEN 1 THEN 'endYear'
      WHEN 2 THEN 'isAdult'
      WHEN 3 THEN 'numVotes'
      WHEN 4 THEN 'originalTitle'
      WHEN 5 THEN 'primaryTitle'
      WHEN 6 THEN 'runtimeMinutes'
      WHEN 7 THEN 'startYear'
      WHEN 8 THEN 'tconst'
      WHEN 9 THEN 'genres.value'
      WHEN 10 THEN 'principals.category'
      WHEN 11 THEN 'principals.job'
      WHEN 12 THEN 'principals.characters.value'
      WHEN 13 THEN 'principals.people.primaryName'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'averageRating'
      WHEN 1 THEN 'endYear'
      WHEN 2 THEN 'isAdult'
      WHEN 3 THEN 'numVotes'
      WHEN 4 THEN 'originalTitle'
      WHEN 5 THEN 'primaryTitle'
      WHEN 6 THEN 'runtimeMinutes'
      WHEN 7 THEN 'startYear'
      WHEN 8 THEN 'tconst'
      WHEN 9 THEN 'genres/value'
      WHEN 10 THEN 'principals/category'
      WHEN 11 THEN 'principals/job'
      WHEN 12 THEN 'principals/characters/value'
      WHEN 13 THEN 'principals/people/primaryName'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'number'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'number'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'number'
      WHEN 7 THEN 'number'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN CAST(NULL as VARCHAR)
      WHEN 2 THEN movies2."isAdult"
      WHEN 4 THEN movies2."originalTitle"
      WHEN 5 THEN movies2."primaryTitle"
      WHEN 8 THEN movies2."tconst"
      WHEN 9 THEN genres_0
      WHEN 10 THEN principals_0."category"
      WHEN 11 THEN principals_0."job"
      WHEN 12 THEN characters_0
      WHEN 13 THEN people_0."primaryName"
    END as "fieldValue",
   COALESCE((
        SELECT SUM(a.val) as value
        FROM (
          SELECT UNNEST(list(distinct {key:movies2."__distinct_key", val: movies2."numVotes"})) a
        )
      ),0) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(movies2."averageRating" as VARCHAR)) || ' to ' || CAST(MAX(movies2."averageRating") as VARCHAR)
      WHEN 1 THEN MIN(CAST(movies2."endYear" as VARCHAR)) || ' to ' || CAST(MAX(movies2."endYear") as VARCHAR)
      WHEN 3 THEN MIN(CAST(movies2."numVotes" as VARCHAR)) || ' to ' || CAST(MAX(movies2."numVotes") as VARCHAR)
      WHEN 6 THEN MIN(CAST(movies2."runtimeMinutes" as VARCHAR)) || ' to ' || CAST(MAX(movies2."runtimeMinutes") as VARCHAR)
      WHEN 7 THEN MIN(CAST(movies2."startYear" as VARCHAR)) || ' to ' || CAST(MAX(movies2."startYear") as VARCHAR)
    END as "fieldRange"
  FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.*  FROM '../data/titles.parquet' as x) as movies2
  LEFT JOIN LATERAL (SELECT UNNEST(movies2."genres"), 1 as ignoreme) as genres_0_outer(genres_0,ignoreme) ON genres_0_outer.ignoreme=1
   LEFT JOIN '../data/principals.parquet' AS principals_0
    ON movies2."tconst"=principals_0."tconst"
  LEFT JOIN LATERAL (SELECT UNNEST(principals_0."characters"), 1 as ignoreme) as characters_0_outer(characters_0,ignoreme) ON characters_0_outer.ignoreme=1
   LEFT JOIN '../data/names.parquet' AS people_0
    ON principals_0."nconst"=people_0."nconst"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,14,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4,5
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldPath",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage0
)
SELECT 
   base."fieldName" as "fieldName",
   base."fieldPath" as "fieldPath",
   base."fieldType" as "fieldType",
   base."fieldValue" as "fieldValue",
   base."weight" as "weight"
FROM __stage1 as base
WHERE base."fieldValue" LIKE 'Brad%'
ORDER BY 5 desc NULLS LAST

So to look for 'Bat'

run: movies2 -> search_index -> { 
  select: *
  where: fieldValue ~ 'Bat%'
  order_by: weight desc
}

QUERY RESULTS

fieldName	fieldPath	fieldType	fieldValue	weight
principals.characters.value	principals/characters/value	string	Batman	10,424,082
primaryTitle	primaryTitle	string	Batman Begins	1,507,854
originalTitle	originalTitle	string	Batman Begins	1,507,854
principals.job	principals/job	string	Batman created by	1,020,989
principals.job	principals/job	string	Batman characters	964,899
principals.characters.value	principals/characters/value	string	Batty	790,162
originalTitle	originalTitle	string	Batman v Superman: Dawn of Justice	713,914
primaryTitle	primaryTitle	string	Batman v Superman: Dawn of Justice	713,914
primaryTitle	primaryTitle	string	Batman	389,337
originalTitle	originalTitle	string	Batman	389,337
originalTitle	originalTitle	string	Batman Returns	315,132
primaryTitle	primaryTitle	string	Batman Returns	315,132
principals.characters.value	principals/characters/value	string	Batou	260,600
primaryTitle	primaryTitle	string	Batman & Robin	260,430
originalTitle	originalTitle	string	Batman & Robin	260,430
originalTitle	originalTitle	string	Batman Forever	259,315
primaryTitle	primaryTitle	string	Batman Forever	259,315
primaryTitle	primaryTitle	string	Battleship	252,642
originalTitle	originalTitle	string	Battleship	252,642
primaryTitle	primaryTitle	string	Battlestar Galactica	248,435
originalTitle	originalTitle	string	Battlestar Galactica	248,435
principals.characters.value	principals/characters/value	string	Batgirl	221,004
originalTitle	originalTitle	string	Battle of the Bastards	217,000
primaryTitle	primaryTitle	string	Battle of the Bastards	217,000
originalTitle	originalTitle	string	Batoru rowaiaru	188,692
primaryTitle	primaryTitle	string	Battle Royale	188,692
originalTitle	originalTitle	string	Battle: Los Angeles	182,720
primaryTitle	primaryTitle	string	Battle Los Angeles	182,720
principals.characters.value	principals/characters/value	string	Batô	148,975
principals.characters.value	principals/characters/value	string	Batiatus	141,304
principals.characters.value	principals/characters/value	string	Battera	112,813
primaryTitle	primaryTitle	string	Bates Motel	112,103
originalTitle	originalTitle	string	Bates Motel	112,103
primaryTitle	primaryTitle	string	Batman: The Animated Series	109,143
originalTitle	originalTitle	string	Batman: The Animated Series	109,143
principals.characters.value	principals/characters/value	string	Battle Droids	107,847
originalTitle	originalTitle	string	Battlefield Earth	81,723
primaryTitle	primaryTitle	string	Battlefield Earth	81,723
principals.people.primaryName	principals/people/primaryName	string	Bathsheba Doran	76,412
principals.people.primaryName	principals/people/primaryName	string	Bate Khalil	73,385
primaryTitle	primaryTitle	string	Batman v Superman: Dawn of Justice (Ultimate Edition)	68,865
originalTitle	originalTitle	string	Batman v Superman: Dawn of Justice (Ultimate Edition)	68,865
originalTitle	originalTitle	string	Batman: Under the Red Hood	65,192
primaryTitle	primaryTitle	string	Batman: Under the Red Hood	65,192
originalTitle	originalTitle	string	Batman: The Dark Knight Returns, Part 1	61,312
primaryTitle	primaryTitle	string	Batman: The Dark Knight Returns, Part 1	61,312
primaryTitle	primaryTitle	string	Battleship Potemkin	59,629
primaryTitle	primaryTitle	string	Batman: The Killing Joke	59,389
originalTitle	originalTitle	string	Batman: The Killing Joke	59,389
originalTitle	originalTitle	string	Battle of the Sexes	57,648
primaryTitle	primaryTitle	string	Battle of the Sexes	57,648
principals.characters.value	principals/characters/value	string	Bathurst 2020	55,709
primaryTitle	primaryTitle	string	Batman: The Dark Knight Returns, Part 2	53,960
originalTitle	originalTitle	string	Batman: The Dark Knight Returns, Part 2	53,960
primaryTitle	primaryTitle	string	Batman: Mask of the Phantasm	53,743
originalTitle	originalTitle	string	Batman: Mask of the Phantasm	53,743
principals.characters.value	principals/characters/value	string	Batu	51,728
principals.characters.value	principals/characters/value	string	Bathsheba Everdene	51,291
originalTitle	originalTitle	string	Batwoman	45,279
principals.characters.value	principals/characters/value	string	Batwoman	45,279
primaryTitle	primaryTitle	string	Batwoman	45,279
principals.characters.value	principals/characters/value	string	Batwing	45,279
principals.characters.value	principals/characters/value	string	Batukeshwar 'Bittu' Tiwari	41,416
primaryTitle	primaryTitle	string	Batman: Year One	36,891
originalTitle	originalTitle	string	Batman: Year One	36,891
primaryTitle	primaryTitle	string	Batman: Assault on Arkham	36,460
originalTitle	originalTitle	string	Batman: Assault on Arkham	36,460
principals.characters.value	principals/characters/value	string	Battal	35,502
primaryTitle	primaryTitle	string	Batman: The Movie	34,606
originalTitle	originalTitle	string	Batman: The Movie	34,606
principals.people.primaryName	principals/people/primaryName	string	Battle Davis	33,046
principals.characters.value	principals/characters/value	string	Bathroom Attendant	33,012
primaryTitle	primaryTitle	string	Batman: Arkham City	32,852
originalTitle	originalTitle	string	Batman: Arkham City	32,852
originalTitle	originalTitle	string	Battle for the Planet of the Apes	32,287
primaryTitle	primaryTitle	string	Battle for the Planet of the Apes	32,287
principals.characters.value	principals/characters/value	string	Batty Koda	32,170

[
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batman",
    "weight": 10424082
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman Begins",
    "weight": 1507854
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman Begins",
    "weight": 1507854
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "Batman created by",
    "weight": 1020989
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "Batman characters",
    "weight": 964899
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batty",
    "weight": 790162
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman v Superman: Dawn of Justice",
    "weight": 713914
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman v Superman: Dawn of Justice",
    "weight": 713914
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman",
    "weight": 389337
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman",
    "weight": 389337
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman Returns",
    "weight": 315132
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman Returns",
    "weight": 315132
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batou",
    "weight": 260600
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman & Robin",
    "weight": 260430
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman & Robin",
    "weight": 260430
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman Forever",
    "weight": 259315
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman Forever",
    "weight": 259315
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battleship",
    "weight": 252642
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battleship",
    "weight": 252642
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battlestar Galactica",
    "weight": 248435
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battlestar Galactica",
    "weight": 248435
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batgirl",
    "weight": 221004
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battle of the Bastards",
    "weight": 217000
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battle of the Bastards",
    "weight": 217000
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batoru rowaiaru",
    "weight": 188692
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battle Royale",
    "weight": 188692
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battle: Los Angeles",
    "weight": 182720
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battle Los Angeles",
    "weight": 182720
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batô",
    "weight": 148975
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batiatus",
    "weight": 141304
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Battera",
    "weight": 112813
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Bates Motel",
    "weight": 112103
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Bates Motel",
    "weight": 112103
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Animated Series",
    "weight": 109143
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Animated Series",
    "weight": 109143
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Battle Droids",
    "weight": 107847
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battlefield Earth",
    "weight": 81723
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battlefield Earth",
    "weight": 81723
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bathsheba Doran",
    "weight": 76412
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bate Khalil",
    "weight": 73385
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman v Superman: Dawn of Justice (Ultimate Edition)",
    "weight": 68865
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman v Superman: Dawn of Justice (Ultimate Edition)",
    "weight": 68865
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Under the Red Hood",
    "weight": 65192
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Under the Red Hood",
    "weight": 65192
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Dark Knight Returns, Part 1",
    "weight": 61312
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Dark Knight Returns, Part 1",
    "weight": 61312
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battleship Potemkin",
    "weight": 59629
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Killing Joke",
    "weight": 59389
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Killing Joke",
    "weight": 59389
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battle of the Sexes",
    "weight": 57648
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battle of the Sexes",
    "weight": 57648
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bathurst 2020",
    "weight": 55709
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Dark Knight Returns, Part 2",
    "weight": 53960
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Dark Knight Returns, Part 2",
    "weight": 53960
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Mask of the Phantasm",
    "weight": 53743
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Mask of the Phantasm",
    "weight": 53743
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batu",
    "weight": 51728
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bathsheba Everdene",
    "weight": 51291
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batwoman",
    "weight": 45279
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batwoman",
    "weight": 45279
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batwoman",
    "weight": 45279
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batwing",
    "weight": 45279
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batukeshwar 'Bittu' Tiwari",
    "weight": 41416
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Year One",
    "weight": 36891
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Year One",
    "weight": 36891
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Assault on Arkham",
    "weight": 36460
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Assault on Arkham",
    "weight": 36460
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Battal",
    "weight": 35502
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Movie",
    "weight": 34606
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: The Movie",
    "weight": 34606
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Battle Davis",
    "weight": 33046
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bathroom Attendant",
    "weight": 33012
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Arkham City",
    "weight": 32852
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Batman: Arkham City",
    "weight": 32852
  },
  {
    "fieldName": "originalTitle",
    "fieldPath": "originalTitle",
    "fieldType": "string",
    "fieldValue": "Battle for the Planet of the Apes",
    "weight": 32287
  },
  {
    "fieldName": "primaryTitle",
    "fieldPath": "primaryTitle",
    "fieldType": "string",
    "fieldValue": "Battle for the Planet of the Apes",
    "weight": 32287
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batty Koda",
    "weight": 32170
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN 'averageRating'
      WHEN 1 THEN 'endYear'
      WHEN 2 THEN 'isAdult'
      WHEN 3 THEN 'numVotes'
      WHEN 4 THEN 'originalTitle'
      WHEN 5 THEN 'primaryTitle'
      WHEN 6 THEN 'runtimeMinutes'
      WHEN 7 THEN 'startYear'
      WHEN 8 THEN 'tconst'
      WHEN 9 THEN 'genres.value'
      WHEN 10 THEN 'principals.category'
      WHEN 11 THEN 'principals.job'
      WHEN 12 THEN 'principals.characters.value'
      WHEN 13 THEN 'principals.people.primaryName'
    END as "fieldName",
    CASE group_set
      WHEN 0 THEN 'averageRating'
      WHEN 1 THEN 'endYear'
      WHEN 2 THEN 'isAdult'
      WHEN 3 THEN 'numVotes'
      WHEN 4 THEN 'originalTitle'
      WHEN 5 THEN 'primaryTitle'
      WHEN 6 THEN 'runtimeMinutes'
      WHEN 7 THEN 'startYear'
      WHEN 8 THEN 'tconst'
      WHEN 9 THEN 'genres/value'
      WHEN 10 THEN 'principals/category'
      WHEN 11 THEN 'principals/job'
      WHEN 12 THEN 'principals/characters/value'
      WHEN 13 THEN 'principals/people/primaryName'
    END as "fieldPath",
    CASE group_set
      WHEN 0 THEN 'number'
      WHEN 1 THEN 'number'
      WHEN 2 THEN 'string'
      WHEN 3 THEN 'number'
      WHEN 4 THEN 'string'
      WHEN 5 THEN 'string'
      WHEN 6 THEN 'number'
      WHEN 7 THEN 'number'
      WHEN 8 THEN 'string'
      WHEN 9 THEN 'string'
      WHEN 10 THEN 'string'
      WHEN 11 THEN 'string'
      WHEN 12 THEN 'string'
      WHEN 13 THEN 'string'
    END as "fieldType",  CASE group_set WHEN 99999 THEN CAST(NULL as VARCHAR)
      WHEN 2 THEN movies2."isAdult"
      WHEN 4 THEN movies2."originalTitle"
      WHEN 5 THEN movies2."primaryTitle"
      WHEN 8 THEN movies2."tconst"
      WHEN 9 THEN genres_0
      WHEN 10 THEN principals_0."category"
      WHEN 11 THEN principals_0."job"
      WHEN 12 THEN characters_0
      WHEN 13 THEN people_0."primaryName"
    END as "fieldValue",
   COALESCE((
        SELECT SUM(a.val) as value
        FROM (
          SELECT UNNEST(list(distinct {key:movies2."__distinct_key", val: movies2."numVotes"})) a
        )
      ),0) as weight,
    CASE group_set
      WHEN 99999 THEN ''    WHEN 0 THEN MIN(CAST(movies2."averageRating" as VARCHAR)) || ' to ' || CAST(MAX(movies2."averageRating") as VARCHAR)
      WHEN 1 THEN MIN(CAST(movies2."endYear" as VARCHAR)) || ' to ' || CAST(MAX(movies2."endYear") as VARCHAR)
      WHEN 3 THEN MIN(CAST(movies2."numVotes" as VARCHAR)) || ' to ' || CAST(MAX(movies2."numVotes") as VARCHAR)
      WHEN 6 THEN MIN(CAST(movies2."runtimeMinutes" as VARCHAR)) || ' to ' || CAST(MAX(movies2."runtimeMinutes") as VARCHAR)
      WHEN 7 THEN MIN(CAST(movies2."startYear" as VARCHAR)) || ' to ' || CAST(MAX(movies2."startYear") as VARCHAR)
    END as "fieldRange"
  FROM (SELECT GEN_RANDOM_UUID() as __distinct_key, x.*  FROM '../data/titles.parquet' as x) as movies2
  LEFT JOIN LATERAL (SELECT UNNEST(movies2."genres"), 1 as ignoreme) as genres_0_outer(genres_0,ignoreme) ON genres_0_outer.ignoreme=1
   LEFT JOIN '../data/principals.parquet' AS principals_0
    ON movies2."tconst"=principals_0."tconst"
  LEFT JOIN LATERAL (SELECT UNNEST(principals_0."characters"), 1 as ignoreme) as characters_0_outer(characters_0,ignoreme) ON characters_0_outer.ignoreme=1
   LEFT JOIN '../data/names.parquet' AS people_0
    ON principals_0."nconst"=people_0."nconst"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,14,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4,5
)
, __stage1 AS (
  SELECT
    "fieldName",
    "fieldPath",
    "fieldType",
    COALESCE("fieldValue", "fieldRange") as "fieldValue",
    weight
  FROM __stage0
)
SELECT 
   base."fieldName" as "fieldName",
   base."fieldPath" as "fieldPath",
   base."fieldType" as "fieldType",
   base."fieldValue" as "fieldValue",
   base."weight" as "weight"
FROM __stage1 as base
WHERE base."fieldValue" LIKE 'Bat%'
ORDER BY 5 desc NULLS LAST