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	04/1983	string	76
act_date	act_date	02/1985	string	42
act_date	act_date	12/1991	string	62
act_date	act_date	09/1983	string	25
act_date	act_date	04/1988	string	52
act_date	act_date	03/1993	string	127
act_date	act_date	08/1993	string	18
act_date	act_date	09/1988	string	14
act_date	act_date	04/1987	string	36
act_date	act_date	10/1984	string	69
act_date	act_date	08/1992	string	35
act_date	act_date	05/1995	string	22
act_date	act_date	10/1985	string	45
act_date	act_date	09/1984	string	11
act_date	act_date	01/1983	string	23
act_date	act_date	02/1990	string	37
act_date	act_date	11/2000	string	70
act_date	act_date	06/1993	string	52
act_date	act_date	08/1982	string	50
act_date	act_date	04/1993	string	145
act_date	act_date	03/1995	string	3
act_date	act_date	05/1989	string	59
act_date	act_date	06/1986	string	13
act_date	act_date	06/1989	string	56
act_date	act_date	05/1994	string	22
act_date	act_date	10/1988	string	5
act_date	act_date	05/1998	string	57
act_date	act_date	12/1982	string	48
act_date	act_date	07/1989	string	83
act_date	act_date	12/2000	string	13
act_date	act_date	07/1991	string	16
act_date	act_date	11/1983	string	51
act_date	act_date	11/1999	string	8
act_date	act_date	08/2000	string	19
act_date	act_date	12/1984	string	7
aero_cht	aero_cht	CAPE LISBURNE	string	20
aero_cht	aero_cht	MC GRATH	string	53
aero_cht	aero_cht	KODIAK	string	86
aero_cht	aero_cht	DAWSON	string	9
aero_cht	aero_cht	DUTCH HARBOR	string	5
aero_cht	aero_cht	COLD BAY	string	11
aero_cht	aero_cht	MEMPHIS	string	605
aero_cht	aero_cht	ALBUQUERQUE	string	174
cbd_dir	cbd_dir	N	string	3,694
cbd_dir	cbd_dir	∅	string	119
cbd_dir	cbd_dir	NW	string	2,438
cert	cert	BS 07/1973	string	2
cert	cert	LU 12/1981	string	2
cert	cert	BS 05/1973	string	81
cert	cert	LU 08/1976	string	7
cert	cert	EU 05/1973	string	1
cert	cert	AS 06/1989	string	2
cert	cert	AS 01/1989	string	1
cert	cert	BS 06/1973	string	1
cert	cert	AS 04/1988	string	1
cert	cert	AU 05/1973	string	25
city	city	UTIRIK ISLAND	string	1
city	city	ANGAUR ISLAND	string	1
city	city	KOSRAE ISLAND	string	1
city	city	POHNPEI ISLAND	string	1
city	city	MAJURO ATOLL	string	1
city	city	ANIAK	string	1
city	city	NORTH POLE	string	6
city	city	SKETNA	string	1
city	city	KENAI	string	8
city	city	BUCKLAND	string	1
city	city	KUPARUK	string	1
city	city	CHANDALAR LAKE	string	1
city	city	CANTWELL	string	3
city	city	CENTRAL	string	1
city	city	CHUATHBALUK	string	1
city	city	NELSON LAGOON	string	1
city	city	CHENEGA	string	1
city	city	TAYLOR MOUNTAIN MINE	string	1
city	city	ANVIK	string	2
city	city	RUSSIAN MISSION	string	2
city	city	BETHEL	string	8
city	city	OLD HARBOR	string	1
city	city	CIRCLE	string	2
city	city	ANCHOR POINT	string	1
city	city	HOUSTON	string	108
city	city	KOTLIK	string	1
city	city	NIKISKI	string	1
city	city	RED DOG	string	1
city	city	CHANDALAR CAMP	string	1
city	city	EMMONAK	string	1
city	city	YUKON CHARLEY RIVERS	string	1
city	city	CANDLE	string	1
city	city	GALBRAITH LAKE	string	1
city	city	CHIGNIK	string	3
city	city	PORT CLARENCE	string	1
city	city	UNALAKLEET	string	1
city	city	RAINY PASS	string	1
city	city	AKHIOK	string	1
city	city	GOLDEN HORN LODGE	string	1
city	city	SELDOVIA	string	2
city	city	CHITINA	string	1
city	city	NOATAK	string	1
city	city	PURKEYPILE	string	1
city	city	TRADING BAY	string	1

[
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1983",
    "weight": 76
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "02/1985",
    "weight": 42
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1991",
    "weight": 62
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1983",
    "weight": 25
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1988",
    "weight": 52
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "03/1993",
    "weight": 127
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "08/1993",
    "weight": 18
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1988",
    "weight": 14
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1987",
    "weight": 36
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1984",
    "weight": 69
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "08/1992",
    "weight": 35
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1995",
    "weight": 22
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1985",
    "weight": 45
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "09/1984",
    "weight": 11
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "01/1983",
    "weight": 23
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "02/1990",
    "weight": 37
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/2000",
    "weight": 70
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "06/1993",
    "weight": 52
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "08/1982",
    "weight": 50
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "04/1993",
    "weight": 145
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "03/1995",
    "weight": 3
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1989",
    "weight": 59
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "06/1986",
    "weight": 13
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "06/1989",
    "weight": 56
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1994",
    "weight": 22
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "10/1988",
    "weight": 5
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "05/1998",
    "weight": 57
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1982",
    "weight": 48
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "07/1989",
    "weight": 83
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/2000",
    "weight": 13
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "07/1991",
    "weight": 16
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1983",
    "weight": 51
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "11/1999",
    "weight": 8
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "08/2000",
    "weight": 19
  },
  {
    "fieldName": "act_date",
    "fieldPath": "act_date",
    "fieldType": "string",
    "fieldValue": "12/1984",
    "weight": 7
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "CAPE LISBURNE",
    "weight": 20
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "MC GRATH",
    "weight": 53
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "KODIAK",
    "weight": 86
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "DAWSON",
    "weight": 9
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "DUTCH HARBOR",
    "weight": 5
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "COLD BAY",
    "weight": 11
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "MEMPHIS",
    "weight": 605
  },
  {
    "fieldName": "aero_cht",
    "fieldPath": "aero_cht",
    "fieldType": "string",
    "fieldValue": "ALBUQUERQUE",
    "weight": 174
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "N",
    "weight": 3694
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": null,
    "weight": 119
  },
  {
    "fieldName": "cbd_dir",
    "fieldPath": "cbd_dir",
    "fieldType": "string",
    "fieldValue": "NW",
    "weight": 2438
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "BS 07/1973",
    "weight": 2
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LU 12/1981",
    "weight": 2
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "BS 05/1973",
    "weight": 81
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "LU 08/1976",
    "weight": 7
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "EU 05/1973",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "AS 06/1989",
    "weight": 2
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "AS 01/1989",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "BS 06/1973",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "AS 04/1988",
    "weight": 1
  },
  {
    "fieldName": "cert",
    "fieldPath": "cert",
    "fieldType": "string",
    "fieldValue": "AU 05/1973",
    "weight": 25
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "UTIRIK ISLAND",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ANGAUR ISLAND",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KOSRAE ISLAND",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "POHNPEI ISLAND",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "MAJURO ATOLL",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ANIAK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NORTH POLE",
    "weight": 6
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SKETNA",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KENAI",
    "weight": 8
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "BUCKLAND",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KUPARUK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHANDALAR LAKE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CANTWELL",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CENTRAL",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHUATHBALUK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NELSON LAGOON",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHENEGA",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "TAYLOR MOUNTAIN MINE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ANVIK",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "RUSSIAN MISSION",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "BETHEL",
    "weight": 8
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "OLD HARBOR",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CIRCLE",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "ANCHOR POINT",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "HOUSTON",
    "weight": 108
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "KOTLIK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NIKISKI",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "RED DOG",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHANDALAR CAMP",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "EMMONAK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "YUKON CHARLEY RIVERS",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CANDLE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "GALBRAITH LAKE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHIGNIK",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "PORT CLARENCE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "UNALAKLEET",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "RAINY PASS",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "AKHIOK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "GOLDEN HORN LODGE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SELDOVIA",
    "weight": 2
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "CHITINA",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "NOATAK",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "PURKEYPILE",
    "weight": 1
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "TRADING BAY",
    "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 YNEZ	3
city	city	string	SANTA MARIA	3
city	city	string	SANTA YSABEL	2
city	city	string	RANCHO SANTA MARGARITA	2
city	city	string	SANTA CRUZ	2
city	city	string	SANTA ELENA	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 YNEZ",
    "weight": 3
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SANTA MARIA",
    "weight": 3
  },
  {
    "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
  },
  {
    "fieldName": "city",
    "fieldPath": "city",
    "fieldType": "string",
    "fieldValue": "SANTA ELENA",
    "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
DAVIS FIELD	10
HILLTOP	10
WILSON	9
TAYLOR	9
WILLIAMS	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": "DAVIS FIELD",
        "weight": 10
      },
      {
        "fieldValue": "HILLTOP",
        "weight": 10
      },
      {
        "fieldValue": "WILSON",
        "weight": 9
      },
      {
        "fieldValue": "TAYLOR",
        "weight": 9
      },
      {
        "fieldValue": "WILLIAMS",
        "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,030
02/1982	40
03/1988	35
02/1987	32
11/1986	32
08/1988	30
04/1993	30
03/1993	29
11/1988	29
11/1990	28

aero_cht

fieldValue	weight
NEW YORK	401
CHICAGO	363
DETROIT	330
WASHINGTON	233
DALLAS-FT WORTH	219
HOUSTON	214
ST LOUIS	214
TWIN CITIES	200
SEATTLE	197
KANSAS CITY	193

c_ldg_rts

fieldValue	weight
∅	3,845
N	1,077
Y	78

cbd_dir

fieldValue	weight
N	942
SW	624
NW	605
NE	587
SE	585
S	559
E	550
W	502
∅	24
NNE	6

cbd_dist

fieldValue	weight
0 to 60	5,000

cert

fieldValue	weight
∅	4,840
AS 05/1973	24
BS 05/1973	22
CS 05/1973	19
LU 05/1973	10
LU 08/1990	10
DS 05/1973	5
LU 10/1974	4
AU 05/1973	3
LU 11/1974	3

city

fieldValue	weight
HOUSTON	34
WASHINGTON	13
LOS ANGELES	13
JACKSON	13
JACKSONVILLE	13
COLUMBUS	12
INDIANAPOLIS	12
GREENVILLE	11
CLINTON	11
ALBANY	11

cntl_twr

fieldValue	weight
N	4,837
Y	163

code

fieldValue	weight
NK77	1
12VA	1
5TA3	1
WV65	1
GGE	1
N93	1
23D	1
FDW	1
0AK8	1
WI65	1

county

fieldValue	weight
WASHINGTON	61
JACKSON	47
JEFFERSON	46
HARRIS	45
FRANKLIN	43
ORANGE	42
MONTGOMERY	38
LOS ANGELES	38
LINCOLN	36
MARION	35

cust_intl

fieldValue	weight
∅	3,845
N	1,137
Y	18

elevation

fieldValue	weight
-1 to 9940	5,000

faa_dist

fieldValue	weight
NONE	1,842
CHI	361
DET	328
SEA	306
MSP	283
NYC	247
ATL	228
ORL	220
HAR	203
DCA	173

faa_region

fieldValue	weight
AGL	1,122
ASW	878
ASO	705
AEA	648
ANM	532
ACE	420
AWP	350
ANE	224
AAL	114
∅	7

fac_type

fieldValue	weight
AIRPORT	3,508
HELIPORT	1,309
SEAPLANE BASE	131
ULTRALIGHT	21
STOLPORT	20
GLIDERPORT	10
BALLOONPORT	1

fac_use

fieldValue	weight
PR	3,665
PU	1,335

fed_agree

fieldValue	weight
∅	4,111
NGY	424
N	125
NGY3	57
NGPY	48
NY1	31
NGSY	30
1	25
NGPY3	22
N1	19

full_name

fieldValue	weight
JOHNSON	7
SMITH	5
DAVIS FIELD	5
MEMORIAL HOSPITAL	4
SKY RANCH	4
ST MARY'S HOSPITAL	4
BAPTIST MEDICAL CENTER	3
ST MARY MEDICAL CENTER	3
TENNESSEE GAS	3
FLYING S RANCH	3

fieldValue	weight
10001 to 19792	5,000

joint_use

fieldValue	weight
∅	3,780
N	1,176
Y	44

latitude

fieldValue	weight
10.28 to 70.57	5,000

longitude

fieldValue	weight
-100.0 to 171.27	5,000

major

fieldValue	weight
N	4,940
Y	60

mil_rts

fieldValue	weight
∅	3,754
Y	741
N	505

own_type

fieldValue	weight
PR	3,615
PU	1,309
MR	32
MA	29
MN	15

site_number

fieldValue	weight
11898.*A	1
07561.53*A	1
26424.31*A	1
07838.*A	1
27534.4*A	1
26637.*A	1
50308.01*H	1
27133.3*A	1
12895.1*A	1
50593.*A	1

state

fieldValue	weight
TX	494
CA	228
IL	217
FL	211
OH	199
PA	195
LA	151
MN	150
IN	146
NY	142

∅

fieldValue	weight
∅	5,000

[
  {
    "fieldName": "act_date",
    "values": [
      {
        "fieldValue": null,
        "weight": 3030
      },
      {
        "fieldValue": "02/1982",
        "weight": 40
      },
      {
        "fieldValue": "03/1988",
        "weight": 35
      },
      {
        "fieldValue": "02/1987",
        "weight": 32
      },
      {
        "fieldValue": "11/1986",
        "weight": 32
      },
      {
        "fieldValue": "08/1988",
        "weight": 30
      },
      {
        "fieldValue": "04/1993",
        "weight": 30
      },
      {
        "fieldValue": "03/1993",
        "weight": 29
      },
      {
        "fieldValue": "11/1988",
        "weight": 29
      },
      {
        "fieldValue": "11/1990",
        "weight": 28
      }
    ]
  },
  {
    "fieldName": "aero_cht",
    "values": [
      {
        "fieldValue": "NEW YORK",
        "weight": 401
      },
      {
        "fieldValue": "CHICAGO",
        "weight": 363
      },
      {
        "fieldValue": "DETROIT",
        "weight": 330
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 233
      },
      {
        "fieldValue": "DALLAS-FT WORTH",
        "weight": 219
      },
      {
        "fieldValue": "HOUSTON",
        "weight": 214
      },
      {
        "fieldValue": "ST LOUIS",
        "weight": 214
      },
      {
        "fieldValue": "TWIN CITIES",
        "weight": 200
      },
      {
        "fieldValue": "SEATTLE",
        "weight": 197
      },
      {
        "fieldValue": "KANSAS CITY",
        "weight": 193
      }
    ]
  },
  {
    "fieldName": "c_ldg_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3845
      },
      {
        "fieldValue": "N",
        "weight": 1077
      },
      {
        "fieldValue": "Y",
        "weight": 78
      }
    ]
  },
  {
    "fieldName": "cbd_dir",
    "values": [
      {
        "fieldValue": "N",
        "weight": 942
      },
      {
        "fieldValue": "SW",
        "weight": 624
      },
      {
        "fieldValue": "NW",
        "weight": 605
      },
      {
        "fieldValue": "NE",
        "weight": 587
      },
      {
        "fieldValue": "SE",
        "weight": 585
      },
      {
        "fieldValue": "S",
        "weight": 559
      },
      {
        "fieldValue": "E",
        "weight": 550
      },
      {
        "fieldValue": "W",
        "weight": 502
      },
      {
        "fieldValue": null,
        "weight": 24
      },
      {
        "fieldValue": "NNE",
        "weight": 6
      }
    ]
  },
  {
    "fieldName": "cbd_dist",
    "values": [
      {
        "fieldValue": "0 to 60",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "cert",
    "values": [
      {
        "fieldValue": null,
        "weight": 4840
      },
      {
        "fieldValue": "AS 05/1973",
        "weight": 24
      },
      {
        "fieldValue": "BS 05/1973",
        "weight": 22
      },
      {
        "fieldValue": "CS 05/1973",
        "weight": 19
      },
      {
        "fieldValue": "LU 05/1973",
        "weight": 10
      },
      {
        "fieldValue": "LU 08/1990",
        "weight": 10
      },
      {
        "fieldValue": "DS 05/1973",
        "weight": 5
      },
      {
        "fieldValue": "LU 10/1974",
        "weight": 4
      },
      {
        "fieldValue": "AU 05/1973",
        "weight": 3
      },
      {
        "fieldValue": "LU 11/1974",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "city",
    "values": [
      {
        "fieldValue": "HOUSTON",
        "weight": 34
      },
      {
        "fieldValue": "WASHINGTON",
        "weight": 13
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 13
      },
      {
        "fieldValue": "JACKSON",
        "weight": 13
      },
      {
        "fieldValue": "JACKSONVILLE",
        "weight": 13
      },
      {
        "fieldValue": "COLUMBUS",
        "weight": 12
      },
      {
        "fieldValue": "INDIANAPOLIS",
        "weight": 12
      },
      {
        "fieldValue": "GREENVILLE",
        "weight": 11
      },
      {
        "fieldValue": "CLINTON",
        "weight": 11
      },
      {
        "fieldValue": "ALBANY",
        "weight": 11
      }
    ]
  },
  {
    "fieldName": "cntl_twr",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4837
      },
      {
        "fieldValue": "Y",
        "weight": 163
      }
    ]
  },
  {
    "fieldName": "code",
    "values": [
      {
        "fieldValue": "NK77",
        "weight": 1
      },
      {
        "fieldValue": "12VA",
        "weight": 1
      },
      {
        "fieldValue": "5TA3",
        "weight": 1
      },
      {
        "fieldValue": "WV65",
        "weight": 1
      },
      {
        "fieldValue": "GGE",
        "weight": 1
      },
      {
        "fieldValue": "N93",
        "weight": 1
      },
      {
        "fieldValue": "23D",
        "weight": 1
      },
      {
        "fieldValue": "FDW",
        "weight": 1
      },
      {
        "fieldValue": "0AK8",
        "weight": 1
      },
      {
        "fieldValue": "WI65",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "county",
    "values": [
      {
        "fieldValue": "WASHINGTON",
        "weight": 61
      },
      {
        "fieldValue": "JACKSON",
        "weight": 47
      },
      {
        "fieldValue": "JEFFERSON",
        "weight": 46
      },
      {
        "fieldValue": "HARRIS",
        "weight": 45
      },
      {
        "fieldValue": "FRANKLIN",
        "weight": 43
      },
      {
        "fieldValue": "ORANGE",
        "weight": 42
      },
      {
        "fieldValue": "MONTGOMERY",
        "weight": 38
      },
      {
        "fieldValue": "LOS ANGELES",
        "weight": 38
      },
      {
        "fieldValue": "LINCOLN",
        "weight": 36
      },
      {
        "fieldValue": "MARION",
        "weight": 35
      }
    ]
  },
  {
    "fieldName": "cust_intl",
    "values": [
      {
        "fieldValue": null,
        "weight": 3845
      },
      {
        "fieldValue": "N",
        "weight": 1137
      },
      {
        "fieldValue": "Y",
        "weight": 18
      }
    ]
  },
  {
    "fieldName": "elevation",
    "values": [
      {
        "fieldValue": "-1 to 9940",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "faa_dist",
    "values": [
      {
        "fieldValue": "NONE",
        "weight": 1842
      },
      {
        "fieldValue": "CHI",
        "weight": 361
      },
      {
        "fieldValue": "DET",
        "weight": 328
      },
      {
        "fieldValue": "SEA",
        "weight": 306
      },
      {
        "fieldValue": "MSP",
        "weight": 283
      },
      {
        "fieldValue": "NYC",
        "weight": 247
      },
      {
        "fieldValue": "ATL",
        "weight": 228
      },
      {
        "fieldValue": "ORL",
        "weight": 220
      },
      {
        "fieldValue": "HAR",
        "weight": 203
      },
      {
        "fieldValue": "DCA",
        "weight": 173
      }
    ]
  },
  {
    "fieldName": "faa_region",
    "values": [
      {
        "fieldValue": "AGL",
        "weight": 1122
      },
      {
        "fieldValue": "ASW",
        "weight": 878
      },
      {
        "fieldValue": "ASO",
        "weight": 705
      },
      {
        "fieldValue": "AEA",
        "weight": 648
      },
      {
        "fieldValue": "ANM",
        "weight": 532
      },
      {
        "fieldValue": "ACE",
        "weight": 420
      },
      {
        "fieldValue": "AWP",
        "weight": 350
      },
      {
        "fieldValue": "ANE",
        "weight": 224
      },
      {
        "fieldValue": "AAL",
        "weight": 114
      },
      {
        "fieldValue": null,
        "weight": 7
      }
    ]
  },
  {
    "fieldName": "fac_type",
    "values": [
      {
        "fieldValue": "AIRPORT",
        "weight": 3508
      },
      {
        "fieldValue": "HELIPORT",
        "weight": 1309
      },
      {
        "fieldValue": "SEAPLANE BASE",
        "weight": 131
      },
      {
        "fieldValue": "ULTRALIGHT",
        "weight": 21
      },
      {
        "fieldValue": "STOLPORT",
        "weight": 20
      },
      {
        "fieldValue": "GLIDERPORT",
        "weight": 10
      },
      {
        "fieldValue": "BALLOONPORT",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "fac_use",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3665
      },
      {
        "fieldValue": "PU",
        "weight": 1335
      }
    ]
  },
  {
    "fieldName": "fed_agree",
    "values": [
      {
        "fieldValue": null,
        "weight": 4111
      },
      {
        "fieldValue": "NGY",
        "weight": 424
      },
      {
        "fieldValue": "N",
        "weight": 125
      },
      {
        "fieldValue": "NGY3",
        "weight": 57
      },
      {
        "fieldValue": "NGPY",
        "weight": 48
      },
      {
        "fieldValue": "NY1",
        "weight": 31
      },
      {
        "fieldValue": "NGSY",
        "weight": 30
      },
      {
        "fieldValue": "1",
        "weight": 25
      },
      {
        "fieldValue": "NGPY3",
        "weight": 22
      },
      {
        "fieldValue": "N1",
        "weight": 19
      }
    ]
  },
  {
    "fieldName": "full_name",
    "values": [
      {
        "fieldValue": "JOHNSON",
        "weight": 7
      },
      {
        "fieldValue": "SMITH",
        "weight": 5
      },
      {
        "fieldValue": "DAVIS FIELD",
        "weight": 5
      },
      {
        "fieldValue": "MEMORIAL HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "SKY RANCH",
        "weight": 4
      },
      {
        "fieldValue": "ST MARY'S HOSPITAL",
        "weight": 4
      },
      {
        "fieldValue": "BAPTIST MEDICAL CENTER",
        "weight": 3
      },
      {
        "fieldValue": "ST MARY MEDICAL CENTER",
        "weight": 3
      },
      {
        "fieldValue": "TENNESSEE GAS",
        "weight": 3
      },
      {
        "fieldValue": "FLYING S RANCH",
        "weight": 3
      }
    ]
  },
  {
    "fieldName": "id",
    "values": [
      {
        "fieldValue": "10001 to 19792",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "joint_use",
    "values": [
      {
        "fieldValue": null,
        "weight": 3780
      },
      {
        "fieldValue": "N",
        "weight": 1176
      },
      {
        "fieldValue": "Y",
        "weight": 44
      }
    ]
  },
  {
    "fieldName": "latitude",
    "values": [
      {
        "fieldValue": "10.28 to 70.57",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "longitude",
    "values": [
      {
        "fieldValue": "-100.0 to 171.27",
        "weight": 5000
      }
    ]
  },
  {
    "fieldName": "major",
    "values": [
      {
        "fieldValue": "N",
        "weight": 4940
      },
      {
        "fieldValue": "Y",
        "weight": 60
      }
    ]
  },
  {
    "fieldName": "mil_rts",
    "values": [
      {
        "fieldValue": null,
        "weight": 3754
      },
      {
        "fieldValue": "Y",
        "weight": 741
      },
      {
        "fieldValue": "N",
        "weight": 505
      }
    ]
  },
  {
    "fieldName": "own_type",
    "values": [
      {
        "fieldValue": "PR",
        "weight": 3615
      },
      {
        "fieldValue": "PU",
        "weight": 1309
      },
      {
        "fieldValue": "MR",
        "weight": 32
      },
      {
        "fieldValue": "MA",
        "weight": 29
      },
      {
        "fieldValue": "MN",
        "weight": 15
      }
    ]
  },
  {
    "fieldName": "site_number",
    "values": [
      {
        "fieldValue": "11898.*A",
        "weight": 1
      },
      {
        "fieldValue": "07561.53*A",
        "weight": 1
      },
      {
        "fieldValue": "26424.31*A",
        "weight": 1
      },
      {
        "fieldValue": "07838.*A",
        "weight": 1
      },
      {
        "fieldValue": "27534.4*A",
        "weight": 1
      },
      {
        "fieldValue": "26637.*A",
        "weight": 1
      },
      {
        "fieldValue": "50308.01*H",
        "weight": 1
      },
      {
        "fieldValue": "27133.3*A",
        "weight": 1
      },
      {
        "fieldValue": "12895.1*A",
        "weight": 1
      },
      {
        "fieldValue": "50593.*A",
        "weight": 1
      }
    ]
  },
  {
    "fieldName": "state",
    "values": [
      {
        "fieldValue": "TX",
        "weight": 494
      },
      {
        "fieldValue": "CA",
        "weight": 228
      },
      {
        "fieldValue": "IL",
        "weight": 217
      },
      {
        "fieldValue": "FL",
        "weight": 211
      },
      {
        "fieldValue": "OH",
        "weight": 199
      },
      {
        "fieldValue": "PA",
        "weight": 195
      },
      {
        "fieldValue": "LA",
        "weight": 151
      },
      {
        "fieldValue": "MN",
        "weight": 150
      },
      {
        "fieldValue": "IN",
        "weight": 146
      },
      {
        "fieldValue": "NY",
        "weight": 142
      }
    ]
  },
  {
    "fieldName": null,
    "values": [
      {
        "fieldValue": null,
        "weight": 5000
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT * from (SELECT * FROM '../data/airports.parquet' USING SAMPLE 5000) as x limit 100000 )
, __stage1 AS (
  SELECT
    group_set,
    CASE group_set
      WHEN 0 THEN '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	713,412,305
endYear	endYear	number	1964.0 to 2024.0	713,412,305
isAdult	isAdult	string	0	713,412,305
numVotes	numVotes	number	100038.0 to 2755400.0	713,412,305
runtimeMinutes	runtimeMinutes	number	100.0 to 780.0	713,412,305
startYear	startYear	number	1902.0 to 2023.0	713,412,305
∅	∅	∅	∅	713,412,305
principals.job	principals/job	string	\N	708,992,234
principals.characters.value	principals/characters/value	string	\N	700,768,445
principals.category	principals/category	string	actor	699,183,314
principals.category	principals/category	string	director	618,349,991
principals.category	principals/category	string	writer	605,802,222
principals.category	principals/category	string	actress	603,119,288
principals.category	principals/category	string	producer	535,359,219
principals.job	principals/job	string	producer	533,039,158
genres.value	genres/value	string	Drama	369,491,232
principals.category	principals/category	string	composer	322,100,121
genres.value	genres/value	string	Action	257,083,861
principals.category	principals/category	string	cinematographer	218,605,262
genres.value	genres/value	string	Adventure	211,856,074
genres.value	genres/value	string	Comedy	202,130,232
principals.job	principals/job	string	written by	170,630,049
principals.job	principals/job	string	screenplay	170,444,517
principals.job	principals/job	string	director of photography	161,989,333
genres.value	genres/value	string	Crime	149,725,589
principals.category	principals/category	string	editor	123,990,484
genres.value	genres/value	string	Thriller	122,437,077
principals.job	principals/job	string	screenplay by	106,282,343
genres.value	genres/value	string	Sci-Fi	95,214,735
genres.value	genres/value	string	Mystery	87,075,217
genres.value	genres/value	string	Romance	70,753,795
genres.value	genres/value	string	Fantasy	67,751,049
genres.value	genres/value	string	Horror	63,004,222
principals.job	principals/job	string	story	57,160,031
genres.value	genres/value	string	Animation	48,915,737
genres.value	genres/value	string	Biography	45,994,693
principals.job	principals/job	string	created by	44,140,628
principals.category	principals/category	string	production_designer	36,589,602
principals.job	principals/job	string	novel	32,811,023
genres.value	genres/value	string	Family	30,814,736
principals.job	principals/job	string	story by	30,794,392
principals.job	principals/job	string	characters	25,259,112
genres.value	genres/value	string	History	18,632,607
principals.job	principals/job	string	based on the novel by	16,816,181
principals.people.primaryName	principals/people/primaryName	string	John Williams	15,069,031
principals.people.primaryName	principals/people/primaryName	string	Brad Pitt	13,738,414
principals.people.primaryName	principals/people/primaryName	string	Tom Hanks	13,605,801
principals.people.primaryName	principals/people/primaryName	string	Stan Lee	13,522,620
principals.people.primaryName	principals/people/primaryName	string	Steven Spielberg	13,026,220
principals.job	principals/job	string	book	12,992,689
principals.job	principals/job	string	based on characters created by	12,836,985
genres.value	genres/value	string	War	12,766,741
genres.value	genres/value	string	Music	11,425,366
principals.people.primaryName	principals/people/primaryName	string	Matt Damon	11,199,816
principals.people.primaryName	principals/people/primaryName	string	George Lucas	11,068,023
principals.people.primaryName	principals/people/primaryName	string	Christian Bale	10,645,032
principals.job	principals/job	string	film editor	10,549,323
principals.job	principals/job	string	co-director	10,201,160
genres.value	genres/value	string	Sport	9,991,294
principals.people.primaryName	principals/people/primaryName	string	Robert De Niro	9,958,088
principals.people.primaryName	principals/people/primaryName	string	Jonathan Nolan	9,823,211
principals.people.primaryName	principals/people/primaryName	string	Robert Downey Jr.	9,663,572
principals.people.primaryName	principals/people/primaryName	string	Christopher Nolan	9,662,066
principals.job	principals/job	string	developed by	9,347,457
principals.people.primaryName	principals/people/primaryName	string	Tom Cruise	9,345,657
principals.people.primaryName	principals/people/primaryName	string	Samuel L. Jackson	9,307,508
principals.people.primaryName	principals/people/primaryName	string	Scott Rudin	9,303,560
principals.people.primaryName	principals/people/primaryName	string	Harrison Ford	9,058,701
principals.people.primaryName	principals/people/primaryName	string	Quentin Tarantino	8,741,484
principals.people.primaryName	principals/people/primaryName	string	Morgan Freeman	8,624,640
principals.people.primaryName	principals/people/primaryName	string	Roger Deakins	8,607,840
principals.people.primaryName	principals/people/primaryName	string	Ridley Scott	8,600,047
principals.people.primaryName	principals/people/primaryName	string	Bob Kane	8,391,526
principals.people.primaryName	principals/people/primaryName	string	Bruce Willis	8,197,142
principals.people.primaryName	principals/people/primaryName	string	Thomas Newman	8,109,370
principals.people.primaryName	principals/people/primaryName	string	Chris Evans	8,085,780
principals.people.primaryName	principals/people/primaryName	string	Ian McKellen	8,045,112
principals.people.primaryName	principals/people/primaryName	string	Leonardo DiCaprio	7,916,449
principals.people.primaryName	principals/people/primaryName	string	Scarlett Johansson	7,906,484
principals.people.primaryName	principals/people/primaryName	string	Peter Jackson	7,904,306
principals.people.primaryName	principals/people/primaryName	string	David Fincher	7,702,759
principals.people.primaryName	principals/people/primaryName	string	Kathleen Kennedy	7,662,013
principals.job	principals/job	string	based on the book by	7,539,137
principals.people.primaryName	principals/people/primaryName	string	James Newton Howard	7,517,793
principals.people.primaryName	principals/people/primaryName	string	Stephen King	7,468,924
principals.people.primaryName	principals/people/primaryName	string	Jack Kirby	7,346,095
principals.characters.value	principals/characters/value	string	Bruce Wayne	7,280,383
principals.people.primaryName	principals/people/primaryName	string	George Clooney	7,044,988
principals.people.primaryName	principals/people/primaryName	string	Hans Zimmer	7,032,934
principals.people.primaryName	principals/people/primaryName	string	David Benioff	7,026,424
principals.job	principals/job	string	based on the Marvel comics by	6,953,795
genres.value	genres/value	string	Western	6,907,326
principals.people.primaryName	principals/people/primaryName	string	Fran Walsh	6,886,296
principals.characters.value	principals/characters/value	string	Batman	6,843,554
principals.people.primaryName	principals/people/primaryName	string	Brian Grazer	6,727,510
principals.people.primaryName	principals/people/primaryName	string	Charles Roven	6,724,064
principals.people.primaryName	principals/people/primaryName	string	David S. Goyer	6,700,259
principals.people.primaryName	principals/people/primaryName	string	Willem Dafoe	6,652,533
principals.people.primaryName	principals/people/primaryName	string	Philippa Boyens	6,628,233
principals.job	principals/job	string	original story by	6,612,504
principals.people.primaryName	principals/people/primaryName	string	Ryan Reynolds	6,603,104
principals.people.primaryName	principals/people/primaryName	string	Hugh Jackman	6,580,292
principals.people.primaryName	principals/people/primaryName	string	Ben Affleck	6,472,409
principals.people.primaryName	principals/people/primaryName	string	Michael Bay	6,454,981
principals.people.primaryName	principals/people/primaryName	string	Mark Ruffalo	6,410,822
principals.characters.value	principals/characters/value	string	Tony Stark	6,376,595
principals.people.primaryName	principals/people/primaryName	string	Liam Neeson	6,368,475
principals.people.primaryName	principals/people/primaryName	string	James Horner	6,346,657
principals.people.primaryName	principals/people/primaryName	string	Simon Kinberg	6,345,804
principals.people.primaryName	principals/people/primaryName	string	Russell Crowe	6,286,871
principals.people.primaryName	principals/people/primaryName	string	David Heyman	6,190,765
principals.people.primaryName	principals/people/primaryName	string	Tim Bevan	6,143,130
principals.people.primaryName	principals/people/primaryName	string	Mark Wahlberg	6,128,463
principals.people.primaryName	principals/people/primaryName	string	Lena Headey	6,053,266
principals.people.primaryName	principals/people/primaryName	string	Jake Gyllenhaal	6,036,981
principals.people.primaryName	principals/people/primaryName	string	Alan Silvestri	6,027,718
principals.people.primaryName	principals/people/primaryName	string	J.R.R. Tolkien	6,022,586
principals.people.primaryName	principals/people/primaryName	string	Arnon Milchan	6,014,552
principals.people.primaryName	principals/people/primaryName	string	Natalie Portman	6,013,851
principals.people.primaryName	principals/people/primaryName	string	Anne Hathaway	5,979,537
principals.people.primaryName	principals/people/primaryName	string	Matthew McConaughey	5,966,433
principals.people.primaryName	principals/people/primaryName	string	Martin Scorsese	5,965,414
principals.people.primaryName	principals/people/primaryName	string	Edward Norton	5,957,019
principals.characters.value	principals/characters/value	string	Gandalf	5,923,041
principals.people.primaryName	principals/people/primaryName	string	Steve Carell	5,912,945
principals.people.primaryName	principals/people/primaryName	string	Chris Hemsworth	5,909,740
principals.people.primaryName	principals/people/primaryName	string	Michael Caine	5,871,920
principals.people.primaryName	principals/people/primaryName	string	Orlando Bloom	5,871,401
principals.people.primaryName	principals/people/primaryName	string	Stephen McFeely	5,854,040
principals.people.primaryName	principals/people/primaryName	string	Christopher Markus	5,854,040
principals.people.primaryName	principals/people/primaryName	string	Andrew Stanton	5,827,281
principals.people.primaryName	principals/people/primaryName	string	Rachel McAdams	5,819,261
principals.people.primaryName	principals/people/primaryName	string	Neal H. Moritz	5,796,407
principals.people.primaryName	principals/people/primaryName	string	Ewan McGregor	5,766,592
principals.people.primaryName	principals/people/primaryName	string	Will Smith	5,762,034
principals.people.primaryName	principals/people/primaryName	string	J.J. Abrams	5,760,887
principals.people.primaryName	principals/people/primaryName	string	Gary Oldman	5,759,266
principals.characters.value	principals/characters/value	string	Narrator	5,749,700
principals.people.primaryName	principals/people/primaryName	string	Kevin Feige	5,742,904
principals.people.primaryName	principals/people/primaryName	string	Eric Fellner	5,737,444
principals.characters.value	principals/characters/value	string	Captain America	5,707,245
principals.people.primaryName	principals/people/primaryName	string	Ceán Chaffin	5,690,714
principals.people.primaryName	principals/people/primaryName	string	Emma Thomas	5,666,891
principals.people.primaryName	principals/people/primaryName	string	Mel Gibson	5,659,939
principals.people.primaryName	principals/people/primaryName	string	Johnny Depp	5,642,498
principals.people.primaryName	principals/people/primaryName	string	Ed Harris	5,631,567
principals.people.primaryName	principals/people/primaryName	string	Ryan Gosling	5,629,440
principals.people.primaryName	principals/people/primaryName	string	Joel Coen	5,617,443
principals.people.primaryName	principals/people/primaryName	string	Akiva Goldsman	5,608,535
principals.people.primaryName	principals/people/primaryName	string	Jeremy Renner	5,584,773
principals.people.primaryName	principals/people/primaryName	string	Ethan Coen	5,580,532
principals.people.primaryName	principals/people/primaryName	string	Nicolas Cage	5,553,255
principals.people.primaryName	principals/people/primaryName	string	Carter Burwell	5,552,802
principals.people.primaryName	principals/people/primaryName	string	Kevin Spacey	5,551,554
principals.people.primaryName	principals/people/primaryName	string	Robert Richardson	5,550,036
principals.job	principals/job	string	screen story	5,549,684
principals.people.primaryName	principals/people/primaryName	string	Joaquin Phoenix	5,525,746
principals.people.primaryName	principals/people/primaryName	string	Emilia Clarke	5,501,831
principals.characters.value	principals/characters/value	string	Steve Rogers	5,472,223
principals.people.primaryName	principals/people/primaryName	string	Alexandre Desplat	5,451,704
principals.people.primaryName	principals/people/primaryName	string	Ian Bryce	5,447,770
principals.people.primaryName	principals/people/primaryName	string	Rick McCallum	5,397,561
principals.people.primaryName	principals/people/primaryName	string	D.B. Weiss	5,361,243
principals.people.primaryName	principals/people/primaryName	string	Peter Dinklage	5,310,182
principals.people.primaryName	principals/people/primaryName	string	Sigourney Weaver	5,276,356
principals.people.primaryName	principals/people/primaryName	string	Bryan Cranston	5,218,252
principals.people.primaryName	principals/people/primaryName	string	Woody Harrelson	5,205,583
principals.people.primaryName	principals/people/primaryName	string	John Goodman	5,203,881
principals.people.primaryName	principals/people/primaryName	string	Judd Apatow	5,185,765
principals.people.primaryName	principals/people/primaryName	string	Robert Zemeckis	5,178,545
principals.people.primaryName	principals/people/primaryName	string	Nikolaj Coster-Waldau	5,169,230
principals.people.primaryName	principals/people/primaryName	string	Jim Carrey	5,162,127
principals.people.primaryName	principals/people/primaryName	string	Adam Sandler	5,142,370
principals.people.primaryName	principals/people/primaryName	string	Jerry Bruckheimer	5,136,116
principals.people.primaryName	principals/people/primaryName	string	Lawrence Kasdan	5,121,888
principals.people.primaryName	principals/people/primaryName	string	Tom Hardy	5,115,341
principals.characters.value	principals/characters/value	string	Jack	5,094,328
principals.people.primaryName	principals/people/primaryName	string	Amy Adams	5,074,168
principals.people.primaryName	principals/people/primaryName	string	Robin Wright	5,032,891
principals.job	principals/job	string	creator	5,004,603
principals.people.primaryName	principals/people/primaryName	string	Al Pacino	5,002,731
principals.people.primaryName	principals/people/primaryName	string	Anthony Hopkins	4,986,085
principals.people.primaryName	principals/people/primaryName	string	Emma Watson	4,953,767
principals.people.primaryName	principals/people/primaryName	string	Owen Wilson	4,953,404
principals.people.primaryName	principals/people/primaryName	string	Steve Kloves	4,935,821
principals.people.primaryName	principals/people/primaryName	string	Howard Shore	4,911,631
principals.people.primaryName	principals/people/primaryName	string	Denzel Washington	4,867,886
principals.people.primaryName	principals/people/primaryName	string	Frank Marshall	4,848,595
principals.characters.value	principals/characters/value	string	Thor	4,843,702
principals.characters.value	principals/characters/value	string	Spider-Man	4,840,293
principals.characters.value	principals/characters/value	string	Peter Parker	4,840,293
principals.people.primaryName	principals/people/primaryName	string	∅	4,820,837
principals.characters.value	principals/characters/value	string	Tyrion Lannister	4,818,993
principals.people.primaryName	principals/people/primaryName	string	Lawrence Bender	4,817,351
principals.people.primaryName	principals/people/primaryName	string	Guillermo del Toro	4,810,864
principals.people.primaryName	principals/people/primaryName	string	Paul Rudd	4,807,253
principals.people.primaryName	principals/people/primaryName	string	Ron Howard	4,759,799
principals.category	principals/category	string	self	4,678,213
principals.people.primaryName	principals/people/primaryName	string	Luc Besson	4,665,178
principals.people.primaryName	principals/people/primaryName	string	Emma Stone	4,653,426

[
  {
    "fieldName": "averageRating",
    "fieldPath": "averageRating",
    "fieldType": "number",
    "fieldValue": "1.0 to 10.0",
    "weight": 713412305
  },
  {
    "fieldName": "endYear",
    "fieldPath": "endYear",
    "fieldType": "number",
    "fieldValue": "1964.0 to 2024.0",
    "weight": 713412305
  },
  {
    "fieldName": "isAdult",
    "fieldPath": "isAdult",
    "fieldType": "string",
    "fieldValue": "0",
    "weight": 713412305
  },
  {
    "fieldName": "numVotes",
    "fieldPath": "numVotes",
    "fieldType": "number",
    "fieldValue": "100038.0 to 2755400.0",
    "weight": 713412305
  },
  {
    "fieldName": "runtimeMinutes",
    "fieldPath": "runtimeMinutes",
    "fieldType": "number",
    "fieldValue": "100.0 to 780.0",
    "weight": 713412305
  },
  {
    "fieldName": "startYear",
    "fieldPath": "startYear",
    "fieldType": "number",
    "fieldValue": "1902.0 to 2023.0",
    "weight": 713412305
  },
  {
    "fieldName": null,
    "fieldPath": null,
    "fieldType": null,
    "fieldValue": null,
    "weight": 713412305
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "\\N",
    "weight": 708992234
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "\\N",
    "weight": 700768445
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "actor",
    "weight": 699183314
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "director",
    "weight": 618349991
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "writer",
    "weight": 605802222
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "actress",
    "weight": 603119288
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "producer",
    "weight": 535359219
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "producer",
    "weight": 533039158
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Drama",
    "weight": 369491232
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "composer",
    "weight": 322100121
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Action",
    "weight": 257083861
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "cinematographer",
    "weight": 218605262
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Adventure",
    "weight": 211856074
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Comedy",
    "weight": 202130232
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "written by",
    "weight": 170630049
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screenplay",
    "weight": 170444517
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "director of photography",
    "weight": 161989333
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Crime",
    "weight": 149725589
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "editor",
    "weight": 123990484
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Thriller",
    "weight": 122437077
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screenplay by",
    "weight": 106282343
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Sci-Fi",
    "weight": 95214735
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Mystery",
    "weight": 87075217
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Romance",
    "weight": 70753795
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Fantasy",
    "weight": 67751049
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Horror",
    "weight": 63004222
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "story",
    "weight": 57160031
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Animation",
    "weight": 48915737
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Biography",
    "weight": 45994693
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "created by",
    "weight": 44140628
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "production_designer",
    "weight": 36589602
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "novel",
    "weight": 32811023
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Family",
    "weight": 30814736
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "story by",
    "weight": 30794392
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "characters",
    "weight": 25259112
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "History",
    "weight": 18632607
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the novel by",
    "weight": 16816181
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "John Williams",
    "weight": 15069031
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brad Pitt",
    "weight": 13738414
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Hanks",
    "weight": 13605801
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stan Lee",
    "weight": 13522620
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steven Spielberg",
    "weight": 13026220
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "book",
    "weight": 12992689
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on characters created by",
    "weight": 12836985
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "War",
    "weight": 12766741
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Music",
    "weight": 11425366
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Matt Damon",
    "weight": 11199816
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "George Lucas",
    "weight": 11068023
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christian Bale",
    "weight": 10645032
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "film editor",
    "weight": 10549323
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "co-director",
    "weight": 10201160
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Sport",
    "weight": 9991294
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert De Niro",
    "weight": 9958088
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jonathan Nolan",
    "weight": 9823211
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Downey Jr.",
    "weight": 9663572
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christopher Nolan",
    "weight": 9662066
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "developed by",
    "weight": 9347457
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Cruise",
    "weight": 9345657
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Samuel L. Jackson",
    "weight": 9307508
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Scott Rudin",
    "weight": 9303560
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Harrison Ford",
    "weight": 9058701
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Quentin Tarantino",
    "weight": 8741484
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Morgan Freeman",
    "weight": 8624640
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Roger Deakins",
    "weight": 8607840
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ridley Scott",
    "weight": 8600047
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bob Kane",
    "weight": 8391526
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bruce Willis",
    "weight": 8197142
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Thomas Newman",
    "weight": 8109370
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Chris Evans",
    "weight": 8085780
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ian McKellen",
    "weight": 8045112
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Leonardo DiCaprio",
    "weight": 7916449
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Scarlett Johansson",
    "weight": 7906484
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Peter Jackson",
    "weight": 7904306
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Fincher",
    "weight": 7702759
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kathleen Kennedy",
    "weight": 7662013
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the book by",
    "weight": 7539137
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Newton Howard",
    "weight": 7517793
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stephen King",
    "weight": 7468924
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jack Kirby",
    "weight": 7346095
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Bruce Wayne",
    "weight": 7280383
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "George Clooney",
    "weight": 7044988
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hans Zimmer",
    "weight": 7032934
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Benioff",
    "weight": 7026424
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "based on the Marvel comics by",
    "weight": 6953795
  },
  {
    "fieldName": "genres.value",
    "fieldPath": "genres/value",
    "fieldType": "string",
    "fieldValue": "Western",
    "weight": 6907326
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Fran Walsh",
    "weight": 6886296
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Batman",
    "weight": 6843554
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Brian Grazer",
    "weight": 6727510
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Charles Roven",
    "weight": 6724064
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David S. Goyer",
    "weight": 6700259
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Willem Dafoe",
    "weight": 6652533
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Philippa Boyens",
    "weight": 6628233
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "original story by",
    "weight": 6612504
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ryan Reynolds",
    "weight": 6603104
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Hugh Jackman",
    "weight": 6580292
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ben Affleck",
    "weight": 6472409
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Bay",
    "weight": 6454981
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mark Ruffalo",
    "weight": 6410822
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Tony Stark",
    "weight": 6376595
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Liam Neeson",
    "weight": 6368475
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "James Horner",
    "weight": 6346657
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Simon Kinberg",
    "weight": 6345804
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Russell Crowe",
    "weight": 6286871
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "David Heyman",
    "weight": 6190765
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tim Bevan",
    "weight": 6143130
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mark Wahlberg",
    "weight": 6128463
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lena Headey",
    "weight": 6053266
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jake Gyllenhaal",
    "weight": 6036981
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Alan Silvestri",
    "weight": 6027718
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "J.R.R. Tolkien",
    "weight": 6022586
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Arnon Milchan",
    "weight": 6014552
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Natalie Portman",
    "weight": 6013851
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Anne Hathaway",
    "weight": 5979537
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Matthew McConaughey",
    "weight": 5966433
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Martin Scorsese",
    "weight": 5965414
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Edward Norton",
    "weight": 5957019
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Gandalf",
    "weight": 5923041
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steve Carell",
    "weight": 5912945
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Chris Hemsworth",
    "weight": 5909740
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Michael Caine",
    "weight": 5871920
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Orlando Bloom",
    "weight": 5871401
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Stephen McFeely",
    "weight": 5854040
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Christopher Markus",
    "weight": 5854040
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Andrew Stanton",
    "weight": 5827281
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Rachel McAdams",
    "weight": 5819261
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Neal H. Moritz",
    "weight": 5796407
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ewan McGregor",
    "weight": 5766592
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Will Smith",
    "weight": 5762034
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "J.J. Abrams",
    "weight": 5760887
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Gary Oldman",
    "weight": 5759266
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Narrator",
    "weight": 5749700
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kevin Feige",
    "weight": 5742904
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Eric Fellner",
    "weight": 5737444
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Captain America",
    "weight": 5707245
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ceán Chaffin",
    "weight": 5690714
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emma Thomas",
    "weight": 5666891
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Mel Gibson",
    "weight": 5659939
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Johnny Depp",
    "weight": 5642498
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ed Harris",
    "weight": 5631567
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ryan Gosling",
    "weight": 5629440
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joel Coen",
    "weight": 5617443
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Akiva Goldsman",
    "weight": 5608535
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jeremy Renner",
    "weight": 5584773
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ethan Coen",
    "weight": 5580532
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Nicolas Cage",
    "weight": 5553255
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Carter Burwell",
    "weight": 5552802
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Kevin Spacey",
    "weight": 5551554
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Richardson",
    "weight": 5550036
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "screen story",
    "weight": 5549684
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Joaquin Phoenix",
    "weight": 5525746
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emilia Clarke",
    "weight": 5501831
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Steve Rogers",
    "weight": 5472223
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Alexandre Desplat",
    "weight": 5451704
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ian Bryce",
    "weight": 5447770
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Rick McCallum",
    "weight": 5397561
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "D.B. Weiss",
    "weight": 5361243
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Peter Dinklage",
    "weight": 5310182
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Sigourney Weaver",
    "weight": 5276356
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Bryan Cranston",
    "weight": 5218252
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Woody Harrelson",
    "weight": 5205583
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "John Goodman",
    "weight": 5203881
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Judd Apatow",
    "weight": 5185765
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robert Zemeckis",
    "weight": 5178545
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Nikolaj Coster-Waldau",
    "weight": 5169230
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jim Carrey",
    "weight": 5162127
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Adam Sandler",
    "weight": 5142370
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Jerry Bruckheimer",
    "weight": 5136116
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lawrence Kasdan",
    "weight": 5121888
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Tom Hardy",
    "weight": 5115341
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Jack",
    "weight": 5094328
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Amy Adams",
    "weight": 5074168
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Robin Wright",
    "weight": 5032891
  },
  {
    "fieldName": "principals.job",
    "fieldPath": "principals/job",
    "fieldType": "string",
    "fieldValue": "creator",
    "weight": 5004603
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Al Pacino",
    "weight": 5002731
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Anthony Hopkins",
    "weight": 4986085
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emma Watson",
    "weight": 4953767
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Owen Wilson",
    "weight": 4953404
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Steve Kloves",
    "weight": 4935821
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Howard Shore",
    "weight": 4911631
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Denzel Washington",
    "weight": 4867886
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Frank Marshall",
    "weight": 4848595
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Thor",
    "weight": 4843702
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Spider-Man",
    "weight": 4840293
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Peter Parker",
    "weight": 4840293
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": null,
    "weight": 4820837
  },
  {
    "fieldName": "principals.characters.value",
    "fieldPath": "principals/characters/value",
    "fieldType": "string",
    "fieldValue": "Tyrion Lannister",
    "weight": 4818993
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Lawrence Bender",
    "weight": 4817351
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Guillermo del Toro",
    "weight": 4810864
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Paul Rudd",
    "weight": 4807253
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Ron Howard",
    "weight": 4759799
  },
  {
    "fieldName": "principals.category",
    "fieldPath": "principals/category",
    "fieldType": "string",
    "fieldValue": "self",
    "weight": 4678213
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Luc Besson",
    "weight": 4665178
  },
  {
    "fieldName": "principals.people.primaryName",
    "fieldPath": "principals/people/primaryName",
    "fieldType": "string",
    "fieldValue": "Emma Stone",
    "weight": 4653426
  }
]

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