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
cust_intl	cust_intl	string	∅	15,145
c_ldg_rts	c_ldg_rts	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
state	state	string	OH	749
aero_cht	aero_cht	string	SEATTLE	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": "cust_intl",
    "fieldPath": "cust_intl",
    "fieldType": "string",
    "fieldValue": null,
    "weight": 15145
  },
  {
    "fieldName": "c_ldg_rts",
    "fieldPath": "c_ldg_rts",
    "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": "state",
    "fieldPath": "state",
    "fieldType": "string",
    "fieldValue": "OH",
    "weight": 749
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "SEATTLE",
    "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 CRUZ	10
county	county	string	SANTA CLARA	10
county	county	string	SANTA FE	9
city	city	string	SANTA FE	6
city	city	string	SANTA ANA	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 ELENA	2
city	city	string	SANTA YSABEL	2
city	city	string	RANCHO SANTA MARGARITA	2
city	city	string	SANTA CRUZ	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 CRUZ",
    "weight": 10
  },
  {
    "fieldName": "county",
    "fieldPath": "county",
    "fieldType": "string",
    "fieldValue": "SANTA CLARA",
    "weight": 10
  },
  {
    "fieldName": "county",
    "fieldPath": "county",
    "fieldType": "string",
    "fieldValue": "SANTA FE",
    "weight": 9
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SANTA FE",
    "weight": 6
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SANTA ANA",
    "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 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
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SANTA CRUZ",
    "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
ROR	1
34AK	1
CJX	1
AKA	1
43AK	1
6K8	1
10AK	1
EXI	1
5Z2	1
MLL	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
HILLTOP	10
DAVIS FIELD	10
WILSON	9
TAYLOR	9
DAVIS	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
51518.6*A	1
51541.*A	1
50410.13*C	1
50720.04*A	1
50080.5*A	1
50870.21*A	1
50530.*A	1
50178.01*A	1
50427.61*A	1
50425.2*H	1

state

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

∅

fieldValue	weight
∅	19,793

[
  {
    "fieldName": "act_date",
    "values": [
      {
        "fieldValue": null,
        "weight": 12040
      },
      {
        "fieldValue": "04/1993",
        "weight": 145
      },
      {
        "fieldValue": "02/1987",
        "weight": 129
      },
      {
        "fieldValue": "03/1993",
        "weight": 127
      },
      {
        "fieldValue": "11/1990",
        "weight": 122
      },
      {
        "fieldValue": "11/1986",
        "weight": 119
      },
      {
        "fieldValue": "02/1982",
        "weight": 118
      },
      {
        "fieldValue": "08/1988",
        "weight": 115
      },
      {
        "fieldValue": "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": "ROR",
        "weight": 1
      },
      {
        "fieldValue": "34AK",
        "weight": 1
      },
      {
        "fieldValue": "CJX",
        "weight": 1
      },
      {
        "fieldValue": "AKA",
        "weight": 1
      },
      {
        "fieldValue": "43AK",
        "weight": 1
      },
      {
        "fieldValue": "6K8",
        "weight": 1
      },
      {
        "fieldValue": "10AK",
        "weight": 1
      },
      {
        "fieldValue": "EXI",
        "weight": 1
      },
      {
        "fieldValue": "5Z2",
        "weight": 1
      },
      {
        "fieldValue": "MLL",
        "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": "HILLTOP",
        "weight": 10
      },
      {
        "fieldValue": "DAVIS FIELD",
        "weight": 10
      },
      {
        "fieldValue": "WILSON",
        "weight": 9
      },
      {
        "fieldValue": "TAYLOR",
        "weight": 9
      },
      {
        "fieldValue": "DAVIS",
        "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": "51518.6*A",
        "weight": 1
      },
      {
        "fieldValue": "51541.*A",
        "weight": 1
      },
      {
        "fieldValue": "50410.13*C",
        "weight": 1
      },
      {
        "fieldValue": "50720.04*A",
        "weight": 1
      },
      {
        "fieldValue": "50080.5*A",
        "weight": 1
      },
      {
        "fieldValue": "50870.21*A",
        "weight": 1
      },
      {
        "fieldValue": "50530.*A",
        "weight": 1
      },
      {
        "fieldValue": "50178.01*A",
        "weight": 1
      },
      {
        "fieldValue": "50427.61*A",
        "weight": 1
      },
      {
        "fieldValue": "50425.2*H",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "state",
    "values": [
      {
        "fieldValue": "TX",
        "weight": 1845
      },
      {
        "fieldValue": "CA",
        "weight": 984
      },
      {
        "fieldValue": "IL",
        "weight": 890
      },
      {
        "fieldValue": "FL",
        "weight": 856
      },
      {
        "fieldValue": "PA",
        "weight": 804
      },
      {
        "fieldValue": "OH",
        "weight": 749
      },
      {
        "fieldValue": "IN",
        "weight": 643
      },
      {
        "fieldValue": "AK",
        "weight": 608
      },
      {
        "fieldValue": "NY",
        "weight": 576
      },
      {
        "fieldValue": "WI",
        "weight": 543
      }
    ]
  },
  {
    "fieldName": null,
    "values": [
      {
        "fieldValue": null,
        "weight": 19793
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN '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,044
04/1993	40
02/1987	36
11/1986	33
11/1990	33
11/1989	32
08/1985	31
03/1993	29
08/1988	29
05/1999	28

aero_cht

fieldValue	weight
NEW YORK	379
CHICAGO	357
DETROIT	353
WASHINGTON	231
DALLAS-FT WORTH	213
TWIN CITIES	206
ST LOUIS	204
SEATTLE	200
KANSAS CITY	194
ATLANTA	191

c_ldg_rts

fieldValue	weight
∅	3,837
N	1,096
Y	67

cbd_dir

fieldValue	weight
N	929
SW	643
NW	620
NE	597
SE	592
S	540
E	538
W	486
∅	29
NNE	7

cbd_dist

fieldValue	weight
0 to 62	5,000

cert

fieldValue	weight
∅	4,839
AS 05/1973	31
CS 05/1973	21
BS 05/1973	18
LU 05/1973	10
DS 05/1973	5
AU 05/1973	4
LU 08/1990	4
LU 12/1974	3
LU 12/1992	2

city

fieldValue	weight
HOUSTON	28
SALEM	15
SPRINGFIELD	11
PLYMOUTH	10
CLINTON	10
ALBANY	10
COLUMBUS	10
COLUMBIA	10
MANSFIELD	9
DALLAS	9

cntl_twr

fieldValue	weight
N	4,839
Y	161

code

fieldValue	weight
PS20	1
3W9	1
11AK	1
WA94	1
57WI	1
OG22	1
2AK	1
M13	1
RCX	1
2AK4	1

county

fieldValue	weight
WASHINGTON	61
FRANKLIN	45
MONTGOMERY	44
LOS ANGELES	42
LINCOLN	41
JACKSON	37
JEFFERSON	36
MARION	33
HARRIS	30
ADAMS	29

cust_intl

fieldValue	weight
∅	3,837
N	1,149
Y	14

elevation

fieldValue	weight
-113 to 10085	5,000

faa_dist

fieldValue	weight
NONE	1,747
CHI	376
DET	323
SEA	305
ATL	276
MSP	265
HAR	231
NYC	231
ORL	206
BIS	191

faa_region

fieldValue	weight
AGL	1,158
ASW	805
ASO	736
AEA	664
ANM	542
ACE	421
AWP	335
ANE	197
AAL	137
∅	5

fac_type

fieldValue	weight
AIRPORT	3,540
HELIPORT	1,274
SEAPLANE BASE	115
ULTRALIGHT	35
STOLPORT	20
GLIDERPORT	13
BALLOONPORT	3

fac_use

fieldValue	weight
PR	3,631
PU	1,369

fed_agree

fieldValue	weight
∅	4,116
NGY	406
N	119
NGY3	63
NGPY	38
NY1	35
1	32
NGSY	20
NGPY3	20
N1	20

full_name

fieldValue	weight
MEMORIAL HOSPITAL	6
MERCY HOSPITAL	4
DAVIS	4
MEDICAL CENTER	4
CLARK	4
ADAMS	4
FLYING W	4
FLYING H RANCH	3
ST JOSEPH'S HOSPITAL	3
RIVERSIDE	3

fieldValue	weight
10001 to 19789	5,000

joint_use

fieldValue	weight
∅	3,733
N	1,215
Y	52

latitude

fieldValue	weight
-14.21 to 70.4	5,000

longitude

fieldValue	weight
-100.0 to 170.88	5,000

major

fieldValue	weight
N	4,933
Y	67

mil_rts

fieldValue	weight
∅	3,714
Y	737
N	549

own_type

fieldValue	weight
PR	3,605
PU	1,328
MR	35
MA	20
MN	12

site_number

fieldValue	weight
14789.2*H	1
10703.*A	1
19589.04*H	1
50320.2*A	1
50072.5*A	1
22780.5*A	1
50215.02*H	1
50325.*A	1
25928.5*H	1
05541.2*A	1

state

fieldValue	weight
TX	455
CA	233
PA	219
IL	218
OH	200
FL	196
IN	160
MO	145
LA	140
AK	137

∅

fieldValue	weight
∅	5,000

[
  {
    "fieldName": "act_date",
    "values": [
      {
        "fieldValue": null,
        "weight": 3044
      },
      {
        "fieldValue": "04/1993",
        "weight": 40
      },
      {
        "fieldValue": "02/1987",
        "weight": 36
      },
      {
        "fieldValue": "11/1986",
        "weight": 33
      },
      {
        "fieldValue": "11/1990",
        "weight": 33
      },
      {
        "fieldValue": "11/1989",
        "weight": 32
      },
      {
        "fieldValue": "08/1985",
        "weight": 31
      },
      {
        "fieldValue": "03/1993",
        "weight": 29
      },
      {
        "fieldValue": "08/1988",
        "weight": 29
      },
      {
        "fieldValue": "05/1999",
        "weight": 28
      }
    ]
  },
  {
    "fieldName": "aero_cht",
    "values": [
      {
        "fieldValue": "NEW YORK",
        "weight": 379
      },
      {
        "fieldValue": "CHICAGO",
        "weight": 357
      },
      {
        "fieldValue": "DETROIT",
        "weight": 353
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 231
      },
      {
        "fieldValue": "DALLAS-FT WORTH",
        "weight": 213
      },
      {
        "fieldValue": "TWIN CITIES",
        "weight": 206
      },
      {
        "fieldValue": "ST LOUIS",
        "weight": 204
      },
      {
        "fieldValue": "SEATTLE",
        "weight": 200
      },
      {
        "fieldValue": "KANSAS CITY",
        "weight": 194
      },
      {
        "fieldValue": "ATLANTA",
        "weight": 191
      }
    ]
  },
  {
    "fieldName": "c_ldg_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3837
      },
      {
        "fieldValue": "N",
        "weight": 1096
      },
      {
        "fieldValue": "Y",
        "weight": 67
      }
    ]
  },
  {
    "fieldName": "cbd_dir",
    "values": [
      {
        "fieldValue": "N",
        "weight": 929
      },
      {
        "fieldValue": "SW",
        "weight": 643
      },
      {
        "fieldValue": "NW",
        "weight": 620
      },
      {
        "fieldValue": "NE",
        "weight": 597
      },
      {
        "fieldValue": "SE",
        "weight": 592
      },
      {
        "fieldValue": "S",
        "weight": 540
      },
      {
        "fieldValue": "E",
        "weight": 538
      },
      {
        "fieldValue": "W",
        "weight": 486
      },
      {
        "fieldValue": null,
        "weight": 29
      },
      {
        "fieldValue": "NNE",
        "weight": 7
      }
    ]
  },
  {
    "fieldName": "cbd_dist",
    "values": [
      {
        "fieldValue": "0 to 62",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "cert",
    "values": [
      {
        "fieldValue": null,
        "weight": 4839
      },
      {
        "fieldValue": "AS 05/1973",
        "weight": 31
      },
      {
        "fieldValue": "CS 05/1973",
        "weight": 21
      },
      {
        "fieldValue": "BS 05/1973",
        "weight": 18
      },
      {
        "fieldValue": "LU 05/1973",
        "weight": 10
      },
      {
        "fieldValue": "DS 05/1973",
        "weight": 5
      },
      {
        "fieldValue": "AU 05/1973",
        "weight": 4
      },
      {
        "fieldValue": "LU 08/1990",
        "weight": 4
      },
      {
        "fieldValue": "LU 12/1974",
        "weight": 3
      },
      {
        "fieldValue": "LU 12/1992",
        "weight": 2
      }
    ]
  },
  {
    "fieldName": "city",
    "values": [
      {
        "fieldValue": "HOUSTON",
        "weight": 28
      },
      {
        "fieldValue": "SALEM",
        "weight": 15
      },
      {
        "fieldValue": "SPRINGFIELD",
        "weight": 11
      },
      {
        "fieldValue": "PLYMOUTH",
        "weight": 10
      },
      {
        "fieldValue": "CLINTON",
        "weight": 10
      },
      {
        "fieldValue": "ALBANY",
        "weight": 10
      },
      {
        "fieldValue": "COLUMBUS",
        "weight": 10
      },
      {
        "fieldValue": "COLUMBIA",
        "weight": 10
      },
      {
        "fieldValue": "MANSFIELD",
        "weight": 9
      },
      {
        "fieldValue": "DALLAS",
        "weight": 9
      }
    ]
  },
  {
    "fieldName": "cntl_twr",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4839
      },
      {
        "fieldValue": "Y",
        "weight": 161
      }
    ]
  },
  {
    "fieldName": "code",
    "values": [
      {
        "fieldValue": "PS20",
        "weight": 1
      },
      {
        "fieldValue": "3W9",
        "weight": 1
      },
      {
        "fieldValue": "11AK",
        "weight": 1
      },
      {
        "fieldValue": "WA94",
        "weight": 1
      },
      {
        "fieldValue": "57WI",
        "weight": 1
      },
      {
        "fieldValue": "OG22",
        "weight": 1
      },
      {
        "fieldValue": "2AK",
        "weight": 1
      },
      {
        "fieldValue": "M13",
        "weight": 1
      },
      {
        "fieldValue": "RCX",
        "weight": 1
      },
      {
        "fieldValue": "2AK4",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "county",
    "values": [
      {
        "fieldValue": "WASHINGTON",
        "weight": 61
      },
      {
        "fieldValue": "FRANKLIN",
        "weight": 45
      },
      {
        "fieldValue": "MONTGOMERY",
        "weight": 44
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 42
      },
      {
        "fieldValue": "LINCOLN",
        "weight": 41
      },
      {
        "fieldValue": "JACKSON",
        "weight": 37
      },
      {
        "fieldValue": "JEFFERSON",
        "weight": 36
      },
      {
        "fieldValue": "MARION",
        "weight": 33
      },
      {
        "fieldValue": "HARRIS",
        "weight": 30
      },
      {
        "fieldValue": "ADAMS",
        "weight": 29
      }
    ]
  },
  {
    "fieldName": "cust_intl",
    "values": [
      {
        "fieldValue": null,
        "weight": 3837
      },
      {
        "fieldValue": "N",
        "weight": 1149
      },
      {
        "fieldValue": "Y",
        "weight": 14
      }
    ]
  },
  {
    "fieldName": "elevation",
    "values": [
      {
        "fieldValue": "-113 to 10085",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "faa_dist",
    "values": [
      {
        "fieldValue": "NONE",
        "weight": 1747
      },
      {
        "fieldValue": "CHI",
        "weight": 376
      },
      {
        "fieldValue": "DET",
        "weight": 323
      },
      {
        "fieldValue": "SEA",
        "weight": 305
      },
      {
        "fieldValue": "ATL",
        "weight": 276
      },
      {
        "fieldValue": "MSP",
        "weight": 265
      },
      {
        "fieldValue": "HAR",
        "weight": 231
      },
      {
        "fieldValue": "NYC",
        "weight": 231
      },
      {
        "fieldValue": "ORL",
        "weight": 206
      },
      {
        "fieldValue": "BIS",
        "weight": 191
      }
    ]
  },
  {
    "fieldName": "faa_region",
    "values": [
      {
        "fieldValue": "AGL",
        "weight": 1158
      },
      {
        "fieldValue": "ASW",
        "weight": 805
      },
      {
        "fieldValue": "ASO",
        "weight": 736
      },
      {
        "fieldValue": "AEA",
        "weight": 664
      },
      {
        "fieldValue": "ANM",
        "weight": 542
      },
      {
        "fieldValue": "ACE",
        "weight": 421
      },
      {
        "fieldValue": "AWP",
        "weight": 335
      },
      {
        "fieldValue": "ANE",
        "weight": 197
      },
      {
        "fieldValue": "AAL",
        "weight": 137
      },
      {
        "fieldValue": null,
        "weight": 5
      }
    ]
  },
  {
    "fieldName": "fac_type",
    "values": [
      {
        "fieldValue": "AIRPORT",
        "weight": 3540
      },
      {
        "fieldValue": "HELIPORT",
        "weight": 1274
      },
      {
        "fieldValue": "SEAPLANE BASE",
        "weight": 115
      },
      {
        "fieldValue": "ULTRALIGHT",
        "weight": 35
      },
      {
        "fieldValue": "STOLPORT",
        "weight": 20
      },
      {
        "fieldValue": "GLIDERPORT",
        "weight": 13
      },
      {
        "fieldValue": "BALLOONPORT",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "fac_use",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3631
      },
      {
        "fieldValue": "PU",
        "weight": 1369
      }
    ]
  },
  {
    "fieldName": "fed_agree",
    "values": [
      {
        "fieldValue": null,
        "weight": 4116
      },
      {
        "fieldValue": "NGY",
        "weight": 406
      },
      {
        "fieldValue": "N",
        "weight": 119
      },
      {
        "fieldValue": "NGY3",
        "weight": 63
      },
      {
        "fieldValue": "NGPY",
        "weight": 38
      },
      {
        "fieldValue": "NY1",
        "weight": 35
      },
      {
        "fieldValue": "1",
        "weight": 32
      },
      {
        "fieldValue": "NGSY",
        "weight": 20
      },
      {
        "fieldValue": "NGPY3",
        "weight": 20
      },
      {
        "fieldValue": "N1",
        "weight": 20
      }
    ]
  },
  {
    "fieldName": "full_name",
    "values": [
      {
        "fieldValue": "MEMORIAL HOSPITAL",
        "weight": 6
      },
      {
        "fieldValue": "MERCY HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "DAVIS",
        "weight": 4
      },
      {
        "fieldValue": "MEDICAL CENTER",
        "weight": 4
      },
      {
        "fieldValue": "CLARK",
        "weight": 4
      },
      {
        "fieldValue": "ADAMS",
        "weight": 4
      },
      {
        "fieldValue": "FLYING W",
        "weight": 4
      },
      {
        "fieldValue": "FLYING H RANCH",
        "weight": 3
      },
      {
        "fieldValue": "ST JOSEPH'S HOSPITAL",
        "weight": 3
      },
      {
        "fieldValue": "RIVERSIDE",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "id",
    "values": [
      {
        "fieldValue": "10001 to 19789",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "joint_use",
    "values": [
      {
        "fieldValue": null,
        "weight": 3733
      },
      {
        "fieldValue": "N",
        "weight": 1215
      },
      {
        "fieldValue": "Y",
        "weight": 52
      }
    ]
  },
  {
    "fieldName": "latitude",
    "values": [
      {
        "fieldValue": "-14.21 to 70.4",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "longitude",
    "values": [
      {
        "fieldValue": "-100.0 to 170.88",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "major",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4933
      },
      {
        "fieldValue": "Y",
        "weight": 67
      }
    ]
  },
  {
    "fieldName": "mil_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3714
      },
      {
        "fieldValue": "Y",
        "weight": 737
      },
      {
        "fieldValue": "N",
        "weight": 549
      }
    ]
  },
  {
    "fieldName": "own_type",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3605
      },
      {
        "fieldValue": "PU",
        "weight": 1328
      },
      {
        "fieldValue": "MR",
        "weight": 35
      },
      {
        "fieldValue": "MA",
        "weight": 20
      },
      {
        "fieldValue": "MN",
        "weight": 12
      }
    ]
  },
  {
    "fieldName": "site_number",
    "values": [
      {
        "fieldValue": "14789.2*H",
        "weight": 1
      },
      {
        "fieldValue": "10703.*A",
        "weight": 1
      },
      {
        "fieldValue": "19589.04*H",
        "weight": 1
      },
      {
        "fieldValue": "50320.2*A",
        "weight": 1
      },
      {
        "fieldValue": "50072.5*A",
        "weight": 1
      },
      {
        "fieldValue": "22780.5*A",
        "weight": 1
      },
      {
        "fieldValue": "50215.02*H",
        "weight": 1
      },
      {
        "fieldValue": "50325.*A",
        "weight": 1
      },
      {
        "fieldValue": "25928.5*H",
        "weight": 1
      },
      {
        "fieldValue": "05541.2*A",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "state",
    "values": [
      {
        "fieldValue": "TX",
        "weight": 455
      },
      {
        "fieldValue": "CA",
        "weight": 233
      },
      {
        "fieldValue": "PA",
        "weight": 219
      },
      {
        "fieldValue": "IL",
        "weight": 218
      },
      {
        "fieldValue": "OH",
        "weight": 200
      },
      {
        "fieldValue": "FL",
        "weight": 196
      },
      {
        "fieldValue": "IN",
        "weight": 160
      },
      {
        "fieldValue": "MO",
        "weight": 145
      },
      {
        "fieldValue": "LA",
        "weight": 140
      },
      {
        "fieldValue": "AK",
        "weight": 137
      }
    ]
  },
  {
    "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 10.0	714,469,771
endYear	endYear	number	1964.0 to 2024.0	714,469,771
isAdult	isAdult	string	0	714,469,771
numVotes	numVotes	number	100038.0 to 2755400.0	714,469,771
runtimeMinutes	runtimeMinutes	number	100.0 to 780.0	714,469,771
startYear	startYear	number	1920.0 to 2023.0	714,469,771
∅	∅	∅	∅	714,469,771
principals.job	principals/job	string	\N	710,157,083
principals.category	principals/category	string	actor	701,487,296
principals.characters.value	principals/characters/value	string	\N	699,848,852
principals.category	principals/category	string	director	620,204,196
principals.category	principals/category	string	writer	607,504,744
principals.category	principals/category	string	actress	602,626,378
principals.category	principals/category	string	producer	537,269,065
principals.job	principals/job	string	producer	535,386,766
genres.value	genres/value	string	Drama	359,114,628
principals.category	principals/category	string	composer	312,605,800
genres.value	genres/value	string	Action	264,226,875
principals.category	principals/category	string	cinematographer	224,101,431
genres.value	genres/value	string	Adventure	215,611,055
genres.value	genres/value	string	Comedy	212,507,003
principals.job	principals/job	string	screenplay	177,562,290
principals.job	principals/job	string	director of photography	168,243,129
principals.job	principals/job	string	written by	165,087,869
genres.value	genres/value	string	Crime	145,729,251
principals.category	principals/category	string	editor	132,908,043
genres.value	genres/value	string	Thriller	114,203,967
principals.job	principals/job	string	screenplay by	104,649,227
genres.value	genres/value	string	Sci-Fi	90,975,508
genres.value	genres/value	string	Mystery	75,660,294
genres.value	genres/value	string	Fantasy	74,967,764
genres.value	genres/value	string	Romance	74,817,238
genres.value	genres/value	string	Horror	64,058,121
principals.job	principals/job	string	story	57,869,437
genres.value	genres/value	string	Animation	55,199,540
genres.value	genres/value	string	Biography	47,161,526
principals.category	principals/category	string	production_designer	40,349,341
principals.job	principals/job	string	created by	40,026,996
principals.job	principals/job	string	novel	35,861,643
principals.job	principals/job	string	story by	33,261,702
genres.value	genres/value	string	Family	29,671,871
principals.job	principals/job	string	characters	26,267,654
genres.value	genres/value	string	History	20,121,196
principals.job	principals/job	string	based on the novel by	17,698,559
principals.people.primaryName	principals/people/primaryName	string	Leonardo DiCaprio	16,129,810
principals.people.primaryName	principals/people/primaryName	string	Brad Pitt	15,289,297
principals.job	principals/job	string	book	13,755,649
principals.people.primaryName	principals/people/primaryName	string	Tom Hanks	13,142,846
genres.value	genres/value	string	War	13,138,049
principals.people.primaryName	principals/people/primaryName	string	Christopher Nolan	13,112,845
genres.value	genres/value	string	Music	12,874,450
principals.job	principals/job	string	film editor	12,309,812
principals.people.primaryName	principals/people/primaryName	string	Christian Bale	12,050,004
principals.people.primaryName	principals/people/primaryName	string	Robert De Niro	12,046,334
principals.people.primaryName	principals/people/primaryName	string	Samuel L. Jackson	11,809,747
principals.job	principals/job	string	co-director	11,252,962
principals.people.primaryName	principals/people/primaryName	string	Quentin Tarantino	11,242,958
principals.people.primaryName	principals/people/primaryName	string	John Williams	10,867,148
principals.job	principals/job	string	based on characters created by	10,782,925
principals.people.primaryName	principals/people/primaryName	string	Stan Lee	10,444,364
principals.people.primaryName	principals/people/primaryName	string	Charles Roven	10,305,308
genres.value	genres/value	string	Sport	10,216,137
principals.job	principals/job	string	developed by	9,945,597
principals.people.primaryName	principals/people/primaryName	string	Ian McKellen	9,759,427
principals.people.primaryName	principals/people/primaryName	string	Bob Kane	9,719,269
principals.people.primaryName	principals/people/primaryName	string	Steven Spielberg	9,687,830
principals.characters.value	principals/characters/value	string	Batman	9,644,754
principals.people.primaryName	principals/people/primaryName	string	Thomas Newman	9,579,153
principals.people.primaryName	principals/people/primaryName	string	Tom Cruise	9,422,729
principals.people.primaryName	principals/people/primaryName	string	Johnny Depp	9,348,470
principals.characters.value	principals/characters/value	string	Bruce Wayne	9,340,134
principals.people.primaryName	principals/people/primaryName	string	Morgan Freeman	9,187,609
principals.people.primaryName	principals/people/primaryName	string	Matt Damon	9,149,079
principals.people.primaryName	principals/people/primaryName	string	David S. Goyer	9,085,091
principals.job	principals/job	string	based on the Marvel comics by	9,055,763
principals.people.primaryName	principals/people/primaryName	string	Peter Jackson	8,906,644
principals.people.primaryName	principals/people/primaryName	string	Orlando Bloom	8,895,876
principals.people.primaryName	principals/people/primaryName	string	Bruce Willis	8,711,181
principals.people.primaryName	principals/people/primaryName	string	Martin Scorsese	8,700,354
principals.people.primaryName	principals/people/primaryName	string	Bradley Cooper	8,425,640
principals.people.primaryName	principals/people/primaryName	string	Stephen King	8,376,793
principals.people.primaryName	principals/people/primaryName	string	Jonathan Nolan	8,343,275
principals.people.primaryName	principals/people/primaryName	string	Liam Neeson	8,209,528
principals.people.primaryName	principals/people/primaryName	string	Lawrence Bender	8,205,607
principals.people.primaryName	principals/people/primaryName	string	Michael Caine	8,183,734
principals.people.primaryName	principals/people/primaryName	string	Hans Zimmer	8,120,419
principals.people.primaryName	principals/people/primaryName	string	Emma Thomas	8,088,224
principals.people.primaryName	principals/people/primaryName	string	Fran Walsh	7,936,766
principals.people.primaryName	principals/people/primaryName	string	Philippa Boyens	7,770,741
principals.people.primaryName	principals/people/primaryName	string	J.R.R. Tolkien	7,711,979
principals.people.primaryName	principals/people/primaryName	string	Robert Richardson	7,709,024
principals.characters.value	principals/characters/value	string	Gandalf	7,631,041
principals.people.primaryName	principals/people/primaryName	string	George Lucas	7,585,197
principals.job	principals/job	string	based on the book by	7,560,659
principals.people.primaryName	principals/people/primaryName	string	Jack Kirby	7,535,566
principals.people.primaryName	principals/people/primaryName	string	Lorne Orleans	7,371,423
principals.people.primaryName	principals/people/primaryName	string	David Fincher	7,254,941
principals.people.primaryName	principals/people/primaryName	string	Sally Menke	7,239,769
principals.people.primaryName	principals/people/primaryName	string	Natalie Portman	7,173,322
principals.people.primaryName	principals/people/primaryName	string	Mark Ruffalo	7,156,155
principals.people.primaryName	principals/people/primaryName	string	Mark Wahlberg	7,152,730
principals.people.primaryName	principals/people/primaryName	string	Ridley Scott	6,860,931
principals.people.primaryName	principals/people/primaryName	string	James Horner	6,849,235
principals.people.primaryName	principals/people/primaryName	string	Edward Norton	6,729,200
principals.people.primaryName	principals/people/primaryName	string	Hugh Jackman	6,674,864
principals.people.primaryName	principals/people/primaryName	string	Al Pacino	6,660,396
principals.people.primaryName	principals/people/primaryName	string	Roger Deakins	6,610,720
principals.people.primaryName	principals/people/primaryName	string	Brian Grazer	6,556,669
principals.people.primaryName	principals/people/primaryName	string	Anne Hathaway	6,555,118
principals.people.primaryName	principals/people/primaryName	string	Chris Evans	6,534,707
principals.people.primaryName	principals/people/primaryName	string	Elijah Wood	6,483,293
principals.people.primaryName	principals/people/primaryName	string	Jerry Bruckheimer	6,461,054
principals.people.primaryName	principals/people/primaryName	string	Ben Affleck	6,453,066
principals.people.primaryName	principals/people/primaryName	string	Matthew McConaughey	6,445,973
principals.people.primaryName	principals/people/primaryName	string	Kevin Feige	6,443,135
principals.people.primaryName	principals/people/primaryName	string	Tim Bevan	6,428,885
principals.people.primaryName	principals/people/primaryName	string	Robert Downey Jr.	6,406,323
principals.people.primaryName	principals/people/primaryName	string	David Heyman	6,374,985
principals.people.primaryName	principals/people/primaryName	string	Scarlett Johansson	6,298,000
principals.people.primaryName	principals/people/primaryName	string	Will Smith	6,271,538
principals.people.primaryName	principals/people/primaryName	string	Chris Hemsworth	6,215,489
principals.people.primaryName	principals/people/primaryName	string	James Cameron	6,208,367
principals.people.primaryName	principals/people/primaryName	string	James Newton Howard	6,099,103
principals.people.primaryName	principals/people/primaryName	string	Danny Elfman	6,068,336
principals.people.primaryName	principals/people/primaryName	string	Harrison Ford	6,033,086
principals.people.primaryName	principals/people/primaryName	string	Cate Blanchett	5,991,509
principals.people.primaryName	principals/people/primaryName	string	Ewan McGregor	5,913,545
principals.people.primaryName	principals/people/primaryName	string	Mel Gibson	5,885,534
principals.people.primaryName	principals/people/primaryName	string	Robert Zemeckis	5,866,737
principals.people.primaryName	principals/people/primaryName	string	Pete Docter	5,862,141
principals.people.primaryName	principals/people/primaryName	string	Eric Fellner	5,838,440
principals.people.primaryName	principals/people/primaryName	string	Jason Blum	5,837,515
principals.people.primaryName	principals/people/primaryName	string	Neal H. Moritz	5,834,656
principals.people.primaryName	principals/people/primaryName	string	Richard Francis-Bruce	5,798,818
principals.people.primaryName	principals/people/primaryName	string	Willem Dafoe	5,769,684
principals.people.primaryName	principals/people/primaryName	string	Akiva Goldsman	5,752,462
principals.characters.value	principals/characters/value	string	Narrator	5,720,979
principals.people.primaryName	principals/people/primaryName	string	Frank Darabont	5,713,504
principals.people.primaryName	principals/people/primaryName	string	Stephen McFeely	5,699,459
principals.people.primaryName	principals/people/primaryName	string	Christopher Markus	5,699,459
principals.people.primaryName	principals/people/primaryName	string	Joseph Gordon-Levitt	5,674,867
principals.people.primaryName	principals/people/primaryName	string	Michael Bay	5,670,145
principals.people.primaryName	principals/people/primaryName	string	Sigourney Weaver	5,665,390
principals.people.primaryName	principals/people/primaryName	string	Clint Eastwood	5,641,837
principals.people.primaryName	principals/people/primaryName	string	Ryan Reynolds	5,602,598
principals.people.primaryName	principals/people/primaryName	string	Ralph Fiennes	5,575,812
principals.people.primaryName	principals/people/primaryName	string	Arnon Milchan	5,557,076
principals.characters.value	principals/characters/value	string	Frodo	5,556,178
principals.people.primaryName	principals/people/primaryName	string	Andrew Stanton	5,524,664
principals.characters.value	principals/characters/value	string	Legolas	5,521,522
principals.people.primaryName	principals/people/primaryName	string	Jennifer Lawrence	5,512,826
principals.people.primaryName	principals/people/primaryName	string	Scott Rudin	5,471,280
principals.people.primaryName	principals/people/primaryName	string	Jake Gyllenhaal	5,468,399
principals.people.primaryName	principals/people/primaryName	string	Simon Kinberg	5,448,589
principals.people.primaryName	principals/people/primaryName	string	Tom Hardy	5,425,192
principals.characters.value	principals/characters/value	string	Jack	5,422,655
principals.people.primaryName	principals/people/primaryName	string	Emma Watson	5,394,356
principals.people.primaryName	principals/people/primaryName	string	Jonah Hill	5,386,018
principals.people.primaryName	principals/people/primaryName	string	Ben Kingsley	5,381,627
principals.job	principals/job	string	original story by	5,380,885
principals.people.primaryName	principals/people/primaryName	string	Tim Burton	5,358,801
principals.people.primaryName	principals/people/primaryName	string	Russell Crowe	5,349,512
principals.people.primaryName	principals/people/primaryName	string	Anthony Hopkins	5,326,430
principals.people.primaryName	principals/people/primaryName	string	Denzel Washington	5,321,895
principals.people.primaryName	principals/people/primaryName	string	Ceán Chaffin	5,242,896
principals.people.primaryName	principals/people/primaryName	string	Joaquin Phoenix	5,229,143
principals.people.primaryName	principals/people/primaryName	string	Amy Adams	5,222,095
principals.people.primaryName	principals/people/primaryName	string	Vin Diesel	5,220,720
genres.value	genres/value	string	Western	5,174,672
principals.people.primaryName	principals/people/primaryName	string	Rick McCallum	5,131,088
principals.people.primaryName	principals/people/primaryName	string	Steve Kloves	5,098,652
principals.people.primaryName	principals/people/primaryName	string	Woody Harrelson	5,066,180
principals.people.primaryName	principals/people/primaryName	string	Carter Burwell	5,061,592
principals.people.primaryName	principals/people/primaryName	string	Dwayne Johnson	5,042,701
genres.value	genres/value	string	Musical	5,029,814
principals.people.primaryName	principals/people/primaryName	string	Viggo Mortensen	5,026,908
principals.people.primaryName	principals/people/primaryName	string	Terry Rossio	4,976,295
principals.people.primaryName	principals/people/primaryName	string	Steve Carell	4,955,275
principals.people.primaryName	principals/people/primaryName	string	Kathleen Kennedy	4,913,452
principals.people.primaryName	principals/people/primaryName	string	Ted Elliott	4,910,223
principals.people.primaryName	principals/people/primaryName	string	Ken Watanabe	4,899,180
principals.people.primaryName	principals/people/primaryName	string	J.K. Rowling	4,897,178
principals.people.primaryName	principals/people/primaryName	string	Lauren Shuler Donner	4,882,527
principals.people.primaryName	principals/people/primaryName	string	Uma Thurman	4,841,627
principals.people.primaryName	principals/people/primaryName	string	John Travolta	4,830,018
principals.people.primaryName	principals/people/primaryName	string	Paul Rudd	4,815,940
principals.characters.value	principals/characters/value	string	Captain America	4,804,068
principals.people.primaryName	principals/people/primaryName	string	Todd Phillips	4,803,847
principals.job	principals/job	string	creator	4,790,839
principals.people.primaryName	principals/people/primaryName	string	Seth Rogen	4,747,549
principals.people.primaryName	principals/people/primaryName	string	Alexandre Desplat	4,733,739
principals.people.primaryName	principals/people/primaryName	string	Tim Robbins	4,711,106
principals.people.primaryName	principals/people/primaryName	string	Marco Beltrami	4,690,576
principals.people.primaryName	principals/people/primaryName	string	Will Ferrell	4,685,531
principals.people.primaryName	principals/people/primaryName	string	Stanley Kubrick	4,677,865
principals.people.primaryName	principals/people/primaryName	string	Michael Giacchino	4,655,719
principals.people.primaryName	principals/people/primaryName	string	Alan Silvestri	4,652,513
principals.characters.value	principals/characters/value	string	Michael	4,618,522
principals.people.primaryName	principals/people/primaryName	string	George Clooney	4,611,238
principals.job	principals/job	string	screen story	4,609,247

[
  {
    "fieldName": "averageRating",
    "fieldPath": "averageRating",
    "fieldType": "number",
    "fieldValue": "1.0 to 10.0",
    "weight": 714469771
  },
  {
    "fieldName": "endYear",
    "fieldPath": "endYear",
    "fieldType": "number",
    "fieldValue": "1964.0 to 2024.0",
    "weight": 714469771
  },
  {
    "fieldName": "isAdult",
    "fieldPath": "isAdult",
    "fieldType": "string",
    "fieldValue": "0",
    "weight": 714469771
  },
  {
    "fieldName": "numVotes",
    "fieldPath": "numVotes",
    "fieldType": "number",
    "fieldValue": "100038.0 to 2755400.0",
    "weight": 714469771
  },
  {
    "fieldName": "runtimeMinutes",
    "fieldPath": "runtimeMinutes",
    "fieldType": "number",
    "fieldValue": "100.0 to 780.0",
    "weight": 714469771
  },
  {
    "fieldName": "startYear",
    "fieldPath": "startYear",
    "fieldType": "number",
    "fieldValue": "1920.0 to 2023.0",
    "weight": 714469771
  },
  {
    "fieldName": null,
    "fieldPath": null,
    "fieldType": null,
    "fieldValue": null,
    "weight": 714469771
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "\\N",
    "weight": 710157083
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "actor",
    "weight": 701487296
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "\\N",
    "weight": 699848852
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "director",
    "weight": 620204196
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "writer",
    "weight": 607504744
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "actress",
    "weight": 602626378
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "producer",
    "weight": 537269065
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "producer",
    "weight": 535386766
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Drama",
    "weight": 359114628
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "composer",
    "weight": 312605800
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Action",
    "weight": 264226875
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "cinematographer",
    "weight": 224101431
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Adventure",
    "weight": 215611055
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Comedy",
    "weight": 212507003
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screenplay",
    "weight": 177562290
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "director of photography",
    "weight": 168243129
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "written by",
    "weight": 165087869
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Crime",
    "weight": 145729251
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "editor",
    "weight": 132908043
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Thriller",
    "weight": 114203967
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screenplay by",
    "weight": 104649227
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Sci-Fi",
    "weight": 90975508
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Mystery",
    "weight": 75660294
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Fantasy",
    "weight": 74967764
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Romance",
    "weight": 74817238
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Horror",
    "weight": 64058121
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "story",
    "weight": 57869437
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Animation",
    "weight": 55199540
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Biography",
    "weight": 47161526
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "production_designer",
    "weight": 40349341
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "created by",
    "weight": 40026996
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "novel",
    "weight": 35861643
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "story by",
    "weight": 33261702
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Family",
    "weight": 29671871
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "characters",
    "weight": 26267654
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "History",
    "weight": 20121196
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the novel by",
    "weight": 17698559
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Leonardo DiCaprio",
    "weight": 16129810
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Pitt",
    "weight": 15289297
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "book",
    "weight": 13755649
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Hanks",
    "weight": 13142846
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "War",
    "weight": 13138049
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christopher Nolan",
    "weight": 13112845
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Music",
    "weight": 12874450
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "film editor",
    "weight": 12309812
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christian Bale",
    "weight": 12050004
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert De Niro",
    "weight": 12046334
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Samuel L. Jackson",
    "weight": 11809747
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "co-director",
    "weight": 11252962
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Quentin Tarantino",
    "weight": 11242958
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "John Williams",
    "weight": 10867148
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on characters created by",
    "weight": 10782925
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stan Lee",
    "weight": 10444364
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Charles Roven",
    "weight": 10305308
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Sport",
    "weight": 10216137
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "developed by",
    "weight": 9945597
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ian McKellen",
    "weight": 9759427
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bob Kane",
    "weight": 9719269
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steven Spielberg",
    "weight": 9687830
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batman",
    "weight": 9644754
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Thomas Newman",
    "weight": 9579153
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Cruise",
    "weight": 9422729
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Johnny Depp",
    "weight": 9348470
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bruce Wayne",
    "weight": 9340134
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Morgan Freeman",
    "weight": 9187609
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Matt Damon",
    "weight": 9149079
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David S. Goyer",
    "weight": 9085091
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the Marvel comics by",
    "weight": 9055763
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Peter Jackson",
    "weight": 8906644
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Orlando Bloom",
    "weight": 8895876
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bruce Willis",
    "weight": 8711181
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Martin Scorsese",
    "weight": 8700354
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bradley Cooper",
    "weight": 8425640
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stephen King",
    "weight": 8376793
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jonathan Nolan",
    "weight": 8343275
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Liam Neeson",
    "weight": 8209528
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lawrence Bender",
    "weight": 8205607
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Caine",
    "weight": 8183734
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hans Zimmer",
    "weight": 8120419
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emma Thomas",
    "weight": 8088224
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Fran Walsh",
    "weight": 7936766
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Philippa Boyens",
    "weight": 7770741
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "J.R.R. Tolkien",
    "weight": 7711979
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Richardson",
    "weight": 7709024
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Gandalf",
    "weight": 7631041
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "George Lucas",
    "weight": 7585197
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the book by",
    "weight": 7560659
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jack Kirby",
    "weight": 7535566
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lorne Orleans",
    "weight": 7371423
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Fincher",
    "weight": 7254941
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Sally Menke",
    "weight": 7239769
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Natalie Portman",
    "weight": 7173322
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mark Ruffalo",
    "weight": 7156155
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mark Wahlberg",
    "weight": 7152730
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ridley Scott",
    "weight": 6860931
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Horner",
    "weight": 6849235
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Edward Norton",
    "weight": 6729200
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hugh Jackman",
    "weight": 6674864
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Al Pacino",
    "weight": 6660396
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Roger Deakins",
    "weight": 6610720
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brian Grazer",
    "weight": 6556669
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Anne Hathaway",
    "weight": 6555118
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Chris Evans",
    "weight": 6534707
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Elijah Wood",
    "weight": 6483293
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jerry Bruckheimer",
    "weight": 6461054
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ben Affleck",
    "weight": 6453066
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Matthew McConaughey",
    "weight": 6445973
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kevin Feige",
    "weight": 6443135
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Bevan",
    "weight": 6428885
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Downey Jr.",
    "weight": 6406323
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Heyman",
    "weight": 6374985
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Scarlett Johansson",
    "weight": 6298000
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Will Smith",
    "weight": 6271538
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Chris Hemsworth",
    "weight": 6215489
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Cameron",
    "weight": 6208367
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Newton Howard",
    "weight": 6099103
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Danny Elfman",
    "weight": 6068336
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Harrison Ford",
    "weight": 6033086
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Cate Blanchett",
    "weight": 5991509
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ewan McGregor",
    "weight": 5913545
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mel Gibson",
    "weight": 5885534
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Zemeckis",
    "weight": 5866737
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Pete Docter",
    "weight": 5862141
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Eric Fellner",
    "weight": 5838440
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jason Blum",
    "weight": 5837515
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Neal H. Moritz",
    "weight": 5834656
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Richard Francis-Bruce",
    "weight": 5798818
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Willem Dafoe",
    "weight": 5769684
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Akiva Goldsman",
    "weight": 5752462
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Narrator",
    "weight": 5720979
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Frank Darabont",
    "weight": 5713504
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stephen McFeely",
    "weight": 5699459
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christopher Markus",
    "weight": 5699459
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joseph Gordon-Levitt",
    "weight": 5674867
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Bay",
    "weight": 5670145
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Sigourney Weaver",
    "weight": 5665390
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Clint Eastwood",
    "weight": 5641837
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ryan Reynolds",
    "weight": 5602598
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ralph Fiennes",
    "weight": 5575812
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Arnon Milchan",
    "weight": 5557076
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Frodo",
    "weight": 5556178
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Andrew Stanton",
    "weight": 5524664
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Legolas",
    "weight": 5521522
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jennifer Lawrence",
    "weight": 5512826
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Scott Rudin",
    "weight": 5471280
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jake Gyllenhaal",
    "weight": 5468399
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Simon Kinberg",
    "weight": 5448589
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Hardy",
    "weight": 5425192
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Jack",
    "weight": 5422655
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emma Watson",
    "weight": 5394356
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jonah Hill",
    "weight": 5386018
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ben Kingsley",
    "weight": 5381627
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "original story by",
    "weight": 5380885
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Burton",
    "weight": 5358801
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Russell Crowe",
    "weight": 5349512
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Anthony Hopkins",
    "weight": 5326430
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Denzel Washington",
    "weight": 5321895
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ceán Chaffin",
    "weight": 5242896
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joaquin Phoenix",
    "weight": 5229143
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Amy Adams",
    "weight": 5222095
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Vin Diesel",
    "weight": 5220720
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Western",
    "weight": 5174672
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Rick McCallum",
    "weight": 5131088
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steve Kloves",
    "weight": 5098652
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Woody Harrelson",
    "weight": 5066180
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Carter Burwell",
    "weight": 5061592
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Dwayne Johnson",
    "weight": 5042701
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Musical",
    "weight": 5029814
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Viggo Mortensen",
    "weight": 5026908
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Terry Rossio",
    "weight": 4976295
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steve Carell",
    "weight": 4955275
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kathleen Kennedy",
    "weight": 4913452
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ted Elliott",
    "weight": 4910223
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ken Watanabe",
    "weight": 4899180
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "J.K. Rowling",
    "weight": 4897178
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lauren Shuler Donner",
    "weight": 4882527
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Uma Thurman",
    "weight": 4841627
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "John Travolta",
    "weight": 4830018
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Paul Rudd",
    "weight": 4815940
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Captain America",
    "weight": 4804068
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Todd Phillips",
    "weight": 4803847
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "creator",
    "weight": 4790839
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Seth Rogen",
    "weight": 4747549
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Alexandre Desplat",
    "weight": 4733739
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Robbins",
    "weight": 4711106
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Marco Beltrami",
    "weight": 4690576
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Will Ferrell",
    "weight": 4685531
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stanley Kubrick",
    "weight": 4677865
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Giacchino",
    "weight": 4655719
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Alan Silvestri",
    "weight": 4652513
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Michael",
    "weight": 4618522
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "George Clooney",
    "weight": 4611238
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screen story",
    "weight": 4609247
  }
]

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