ch09_term_level_queries.txt

DELETE movies
# Creating movies index with explicit mapping
PUT movies
{
  "mappings": {
    "properties": {
      "title": {
        "type": "text",
        "fields": {
          "original": {
            "type": "keyword"
          }
        }
      },
      "synopsis": {
        "type": "text",
        "fields": {
          "original": {
            "type": "keyword"
          }
        }
      },
      "actors": {
        "type": "text",
        "fields": {
          "original": {
            "type": "keyword"
          }
        }
      },
      "director": {
        "type": "text",
        "fields": {
          "original": {
            "type": "keyword"
          }
        }
      },
      "rating": {
        "type": "half_float"
      },
      "release_date": {
        "type": "date",
        "format": "dd-MM-yyyy"
      },
      "certificate": {
        "type": "keyword",
        "fields": {
          "original": {
            "type": "keyword"
          }
        }
      },
      "genre": {
        "type": "text",
        "index_prefixes":{},
        "fields": {
          "original": {
            "type": "keyword"
          }
        }
      }
    }
  }
}
# Insert the movies sample data
## Copy the contents of datasets/top-movies.json and index the movies using bulk API

POST _bulk
{"index":{"_index":"movies","_id":"1"}}
{"title": "The Shawshank Redemption","synopsis": "Two imprisoned men bond over a number of years, finding solace and eventual redemption through acts of common decency.","actors": ["Tim Robbins", "Morgan Freeman", "Bob Gunton", "William Sadler"] ,"director":" Frank Darabont ","rating":9.3,"certificate":"R","genre": "Drama ","release_date":"17-02-1995"}
{"index":{"_index":"movies","_id":"2"}}
{"title": "The Godfather","synopsis": "An organized crime dynasty's aging patriarch transfers control of his clandestine empire to his reluctant son.","actors": ["Marlon Brando", "Al Pacino", "James Caan", "Diane Keaton"] ,"director":" Francis Ford Coppola ","rating":9.2,"certificate":"R","genre": ["Crime", "Drama"] ,"release_date":"14-03-1972"}
{"index":{"_index":"movies","_id":"3"}}
{"title": "The Dark Knight","synopsis": "When the menace known as the Joker wreaks havoc and chaos on the people of Gotham, Batman must accept one of the greatest psychological and physical tests of his ability to fight injustice.","actors": ["Christian Bale", "Heath Ledger", "Aaron Eckhart", "Michael Caine"] ,"director":" Christopher Nolan ","rating":9.0,"certificate":"PG-13","genre": ["Action", "Crime", "Drama"] ,"release_date":"18-07-2008"}
{"index":{"_index":"movies","_id":"4"}}
{"title": "The Godfather: Part II","synopsis": "The early life and career of Vito Corleone in 1920s New York City is portrayed, while his son, Michael, expands and tightens his grip on the family crime syndicate.","actors": ["Al Pacino", "Robert De Niro", "Robert Duvall", "Diane Keaton"] ,"director":" Francis Ford Coppola ","rating":9.0,"certificate":"R","genre": ["Crime", "Drama"] ,"release_date":"20-12-1974"}
{"index":{"_index":"movies","_id":"5"}}
{"title": "12 Angry Men","synopsis": "A jury holdout attempts to prevent a miscarriage of justice by forcing his colleagues to reconsider the evidence.","actors": ["Henry Fonda", "Lee J. Cobb", "Martin Balsam", " John Fiedler"] ,"director":" Sidney Lumet ","rating":9.0,"certificate":"Approved","genre": ["Crime", "Drama"],"release_date":"10-04-1957" }
{"index":{"_index":"movies","_id":"6"}}
{"title": "The Lord of the Rings: The Return of the King","synopsis": "Gandalf and Aragorn lead the World of Men against Saurons's army to draw his gaze from Frodo and Sam as they approach Mount Doom with the One Ring.","actors": ["Elijah Wood", "Viggo Mortensen", "Ian McKellen", "Orlando Bloom"] ,"director":" Peter Jackson ","rating":8.9,"certificate":"PG-13","genre": ["Action", "Adventure", "Drama"] ,"release_date":"06-02-2004"}
{"index":{"_index":"movies","_id":"7"}}
{"title": "Pulp Fiction","synopsis": "The lives of two mob hitmen, a boxer, a gangster and his wife, and a pair of diner bandits intertwine in four tales of violence and redemption.","actors": ["John Travolta", "Uma Thurman", "Samuel L. Jackson", "Bruce Willis"] ,"director":" Quentin Tarantino ","rating":8.9,"certificate":"R","genre": ["Crime", "Drama"] ,"release_date":"14-10-1994"}
{"index":{"_index":"movies","_id":"8"}}
{"title": "Schindler's List","synopsis": "In German-occupied Poland during World War II, industrialist Oskar Schindler gradually becomes concerned for his Jewish workforce after witnessing their persecution by the Nazis.","actors": ["Liam Neeson", "Ralph Fiennes", "Ben Kingsley", "Caroline Goodall"] ,"director":" Steven Spielberg ","rating":8.9,"certificate":"R","genre": ["Biography", "Drama", "History"] ,"release_date":"04-07-1994"}
{"index":{"_index":"movies","_id":"9"}}
{"title": "Inception","synopsis": "A thief who steals corporate secrets through the use of dream-sharing technology is given the inverse task of planting an idea into the mind of a C.E.O.","actors": ["Leonardo DiCaprio", "Joseph Gordon-Levitt", "Elliot Page", "Ken Watanabe"] ,"director":" Christopher Nolan ","rating":8.8,"certificate":"PG-13","genre": ["Action", "Adventure", "Sci-Fi"] ,"release_date":"16-07-2010"}
{"index":{"_index":"movies","_id":"10"}}
{"title": "Fight Club","synopsis": "An insomniac office worker and a devil-may-care soap maker form an underground fight club that evolves into much more.","actors": ["Brad Pitt", "Edward Norton", "Meat Loaf", "Zach Grenier"] ,"director":" David Fincher ","rating":8.8,"certificate":"R","genre": "Drama","release_date":"11-11-1999"} 
{"index":{"_index":"movies","_id":"11"}}
{"title": "The Lord of the Rings: The Fellowship of the Ring","synopsis": "A meek Hobbit from the Shire and eight companions set out on a journey to destroy the powerful One Ring and save Middle-earth from the Dark Lord Sauron.","actors": ["Elijah Wood", "Ian McKellen", "Orlando Bloom", "Sean Bean"] ,"director":" Peter Jackson ","rating":8.8,"certificate":"PG-13","genre": ["Action", "Adventure", "Drama"] ,"release_date":"15-03-2002"}
{"index":{"_index":"movies","_id":"12"}}
{"title": "Forrest Gump","synopsis": "The presidencies of Kennedy and Johnson, the Vietnam War, the Watergate scandal and other historical events unfold from the perspective of an Alabama man with an IQ of 75, whose only desire is to be reunited with his childhood sweetheart.","actors": ["Tom Hanks", "Robin Wright", "Gary Sinise", "Sally Field"] ,"director":" Robert Zemeckis ","rating":8.8,"certificate":"PG-13","genre": ["Drama", "Romance"] ,"release_date":"06-07-1994"}
{"index":{"_index":"movies","_id":"13"}}
{"title": "The Good, the Bad and the Ugly","synopsis": "A bounty hunting scam joins two men in an uneasy alliance against a third in a race to find a fortune in gold buried in a remote cemetery.","actors": ["Clint Eastwood", "Eli Wallach", "Lee Van Cleef", "Aldo Giuffrè"] ,"director":" Sergio Leone ","rating":8.8,"certificate":"R","genre": "Western","release_date":"23-12-1966"}
{"index":{"_index":"movies","_id":"14"}}
{"title": "The Lord of the Rings: The Two Towers","synopsis": "While Frodo and Sam edge closer to Mordor with the help of the shifty Gollum, the divided fellowship makes a stand against Sauron's new ally, Saruman, and his hordes of Isengard.","actors": ["Elijah Wood", "Ian McKellen", "Viggo Mortensen", "Orlando Bloom"] ,"director":" Peter Jackson ","rating":8.7,"certificate":"PG-13","genre": ["Action", "Adventure", "Drama"],"release_date":"28-03-2003"} 
{"index":{"_index":"movies","_id":"15"}}
{"title": "The Matrix","synopsis": "When a beautiful stranger leads computer hacker Neo to a forbidding underworld, he discovers the shocking truth--the life he knows is the elaborate deception of an evil cyber-intelligence.","actors": ["Keanu Reeves", "Laurence Fishburne", "Carrie-Anne Moss", "Hugo Weaving"] ,"director":["Lana Wachowski", "Lilly Wachowski"] ,"rating":8.7,"certificate":"R","genre": ["Action", "Sci-Fi"] ,"release_date":"16-05-2003"}
{"index":{"_index":"movies","_id":"16"}}
{"title": "Goodfellas","synopsis": "The story of Henry Hill and his life in the mob, covering his relationship with his wife Karen Hill and his mob partners Jimmy Conway and Tommy DeVito in the Italian-American crime syndicate.","actors": ["Robert De Niro", "Ray Liotta", "Joe Pesci", "Lorraine Bracco"] ,"director":" Martin Scorsese ","rating":8.7,"certificate":"R","genre": ["Biography", "Drama", "Crime"],"release_date":"19-09-1990" }
{"index":{"_index":"movies","_id":"17"}}
{"title": "Star Wars: Episode V - The Empire Strikes Back","synopsis": "After the Rebels are brutally overpowered by the Empire on the ice planet Hoth, Luke Skywalker begins Jedi training with Yoda, while his friends are pursued across the galaxy by Darth Vader and bounty hunter Boba Fett.","actors": ["Mark Hamill", "Harrison Ford", "Carrie Fisher", "Billy Dee Williams"] ,"director":" Irvin Kershner ","rating":8.7,"certificate":"PG-13","genre": ["Action", "Adventure", "Fantasy"] ,"release_date":"21-05-1980"}
{"index":{"_index":"movies","_id":"18"}}
{"title": "One Flew Over the Cuckoo's Nest","synopsis": "A criminal pleads insanity and is admitted to a mental institution, where he rebels against the oppressive nurse and rallies up the scared patients.","actors": ["Jack Nicholson", "Louise Fletcher", "Michael Berryman", "Peter Brocco"] ,"director":" Milos Forman ","rating":8.7,"certificate":"R","genre": "Drama" ,"release_date":"19-11-1975"}
{"index":{"_index":"movies","_id":"19"}}
{"title": "Parasite","synopsis": "Greed and class discrimination threaten the newly formed symbiotic relationship between the wealthy Park family and the destitute Kim clan.","actors": ["Kang-ho Song", "Sun-kyun Lee", "Yeo-jeong Cho", "Woo-sik Choi"] ,"director":" Bong Joon Ho ","rating":8.6,"certificate":"R","genre": ["Comedy", "Drama", "Thriller"] ,"release_date":"30-05-2019"}
{"index":{"_index":"movies","_id":"20"}}
{"title": "Interstellar","synopsis": "A team of explorers travel through a wormhole in space in an attempt to ensure humanity's survival.","actors": ["Matthew McConaughey", "Anne Hathaway", "Jessica Chastain", "Mackenzie Foy"] ,"director":" Christopher Nolan ","rating":8.6,"certificate":"PG-13","genre": ["Adventure", "Drama", "Sci-Fi"] ,"release_date":"07-11-2014"}
{"index":{"_index":"movies","_id":"21"}}
{"title": "City of God","synopsis": "In the slums of Rio, two kids paths diverge as one struggles to become a photographer and the other a kingpin.","actors": ["Alexandre Rodrigues", "Leandro Firmino", "Matheus Nachtergaele", "Phellipe Haagensen"] ,"director": ["Fernando Meirelles", "Kátia Lund"] ,"rating":8.6,"certificate":"R","genre": ["Crime", "Drama"],"release_date":"30-08-2002" }
{"index":{"_index":"movies","_id":"22"}}
{"title": "Spirited Away","synopsis": "During her family's move to the suburbs, a sullen 10-year-old girl wanders into a world ruled by gods, witches, and spirits, and where humans are changed into beasts.","actors": ["Daveigh Chase", "Suzanne Pleshette", "Miyu Irino", "Rumi Hiiragi"] ,"director":" Hayao Miyazaki ","rating":8.6,"certificate":"PG-13","genre": ["Animation", "Adventure", "Family"],"release_date":"20-07-2001"} 
{"index":{"_index":"movies","_id":"23"}}
{"title": "Saving Private Ryan","synopsis": "Following the Normandy Landings, a group of U.S. soldiers go behind enemy lines to retrieve a paratrooper whose brothers have been killed in action.","actors": ["Tom Hanks", "Matt Damon", "Tom Sizemore", "Edward Burns"] ,"director":" Steven Spielberg ","rating":8.6,"certificate":"R","genre": ["Drama", "War"] ,"release_date":"21-07-1998"}
{"index":{"_index":"movies","_id":"24"}}
{"title": "The Green Mile","synopsis": "The lives of guards on Death Row are affected by one of their charges: a black man accused of child murder and rape, yet who has a mysterious gift.","actors": ["Tom Hanks", "Michael Clarke Duncan", "David Morse", "Bonnie Hunt"] ,"director":" Frank Darabont ","rating":8.6,"certificate":"R","genre": ["Crime", "Drama", "Fantasy"] ,"release_date":"06-12-1999"}
{"index":{"_index":"movies","_id":"25"}}
{"title": "Life Is Beautiful","synopsis": "When an open-minded Jewish librarian and his son become victims of the Holocaust, he uses a perfect mixture of will, humor, and imagination to protect his son from the dangers around their camp.","actors": ["Roberto Benigni", "Nicoletta Braschi", "Giorgio Cantarini", "Giustino Durano"] ,"director":" Roberto Benigni ","rating":8.6,"certificate":"PG-13","genre": ["Comedy", "Drama", "Romance"],"release_date":"14-09-2012"} 


# Fetching the movies with a specific rating
GET movies/_search
{
  "query": {
    "term": {
      "certificate": "R"
    }
  }
}

# Fetching the movies by lowercasing the rating
# No results returned
GET movies/_search
{
  "query": {
    "term": {
      "certificate": "r"
    }
  }
}
# This term query wouldn't return the results
GET movies/_search
{
  "query": {
    "term": {
      "title": "The Godfather" 
    }
  }
}

# Terms query
GET movies/_search
{
  "query": {
    "terms": { 
      "certificate": ["PG-13","R"]
    }
  }
}

# Change the max term count property
PUT movies/_settings
{
  "index":{
    "max_terms_count":10
  }
}

# Terms lookup
DELETE classic_movies

PUT classic_movies
{
  "mappings": {
    "properties": {
      "title": {
        "type": "text"
      },
      "director": {
        "type": "keyword"
      }
    }
  }
}

# Index three docs
PUT classic_movies/_doc/1
{
  "title":"Jaws",
  "director":"Steven Spielberg"
}

PUT classic_movies/_doc/2
{
  "title":"Jaws II",
  "director":"Jeannot Szwarc"
}

PUT classic_movies/_doc/3
{
  "title":"Ready Player One",
  "director":"Steven Spielberg"
}

# Search for terms with director matching with the "path" value 
# from "classic_movies" index for a document with an ID as 3 (which is "Steven Spielberg)
GET classic_movies/_search
{
  "query": {
    "terms": {
      "director": {
        "index":"classic_movies",
        "id":"3",
        "path":"director"
      }
    }
  }
}

# This query to fetches two movies directored by Speilberg

# IDs query
GET movies/_search
{
  "query": {
    "ids": {
      "values": [10,4,6,8]
    }
  }
}

# Terms query fetching IDs
GET movies/_search
{
  "query": {
    "terms": {
    "_id":[10,4,6,8]
    }
  }
}

# Exists query
GET movies/_search
{
  "query": {
    "exists": {
      "field": "_id"
    }
  }
}

GET movies/_search
{
  "query": {
    "exists": {
      "field": "title"
    }
  }
}

#Fetching documents that miss a field by using bool query

PUT top_secret_files/_doc/1
{
  "code":"Flying Bird",
  "confidential":true
}

PUT top_secret_files/_doc/2
{
  "code":"Cold Rock"
}

GET top_secret_files/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "confidential"
          }
        }
      ]
    }
  }
}

# Range query
GET movies/_search
{
  "query": {
    "range": {
      "rating": {
        "gte": 9.0,
        "lte": 9.5
      }
    }
  }
}

# Fetch movies after 1970 and sort them in asc order
GET movies/_search
{
  "query": {
    "range": {
      "release_date": {
        "gte": "01-01-1970"
      }
    }
  },
  "sort": [
    {
      "release_date": {
        "order": "asc"
      }
    }
  ]
}

# Range query with date math
# Before 13th Feb 1995
GET movies/_search
{
  "query": {
    "range": {
      "release_date": {
        "lte": "15-02-1995||-2d"
      }
    }
  }
}

# Released after 3rd march 2019
GET movies/_search
{
  "query": {
    "range": {
      "release_date": {
        "gte": "01-03-2019||-2d" 
      }
    }
  }
}
# Before 15-March-2022
GET movies/_search
{
  "query": {
    "range": {
      "release_date": {
        "lte": "17-03-2022||-2d"
      }
    }
  }
}

# Range query with date match using now 
# Movies from last 4 years
GET movies/_search
{
  "query": {
    "range": {
      "release_date": {
        "gte": "now-4y"
      }
    }
  }
}

# Between now and 4 years ago
GET movies/_search
{
  "query": {
    "range": {
      "release_date": {
        "gte": "now-4y",
        "lte":"now"
      }
    }
  }
}

# Wildcard Query with a wildcard at the end
GET movies/_search
{
  "query": {
    "wildcard": { 
      "title": {
        "value": "god*"
      }
    }
  }
}

# Wildcard Query with a wildcard in middle
GET movies/_search
{
  "_source": false, 
  "query": {
    "wildcard": { 
      "title": {
        "value": "g*d"
      }
    }
  },
  "highlight": {
    "fields": {
      "title": {}
    }
  }
}

GET movies/_search
{
  
  "query": {
    "wildcard": { 
      "title.original": {
        "value": "The God*"
      }
    }
  },
  "highlight": {
    "fields": {
      "title": {}
    }
  }
}

# Switch off the expensive queries
PUT _cluster/settings
{
  "transient": {
    "search.allow_expensive_queries": "false"
  }
}
GET movies/_mapping

# Prefix query 
GET movies/_search
{
  "query": {
    "prefix": {
      "genre.original": {
        "value": "Ad"
      }
    }
  },"highlight": {
   "fields": {"genre.original": {}} 
  }
}

# Searching for actors with "Mar" as prefix 
GET movies/_search
{
  "_source": false, 
  "query": {
    "prefix": {
      "actors.original": {
        "value": "Mar"
      }
    }
  },
  "highlight": {
    "fields": {
      "actors.original": {}
    }
  }
}

#Index prefixes
PUT boxoffice_hit_movies
{
  "mappings": {
    "properties": {
      "title":{
        "type": "text",
        "index_prefixes":{}
      }
    }
  }
}

PUT boxoffice_hit_movies/_doc/1
{
  "title":"Gladiator"
}

GET boxoffice_hit_movies/_search
{
  "query": {
    "prefix": {
      "title": {
        "value": "gla"
      }
    }
  }
}

#Index prefixes with custom prefix settings
PUT boxoffice_hit_movies_custom_prefix_sizes
{
  "mappings": {
    "properties": {
      "title":{
        "type": "text",
        "index_prefixes":{
          "min_chars":4, 
          "max_chars":10 
        }
      }
    }
  }
}

# Fuzzy query with edit distance of 1
GET movies/_search
{
  "query": {
    "fuzzy": {
      "genre": {
        "value": "rama",
        "fuzziness": 1
      }
    }
  },
  "highlight": {
    "fields": {
      "genre": {}
    }
  }
}

# Fuzzy query with dropping off middle letter
GET movies/_search
{
  "query": {
    "fuzzy": {
      "genre": {
        "value": "drma",
        "fuzziness": 1
      }
    }
  }
}

# Fuzzy query that has two letters missing
GET movies/_search
{
  "query": {
    "fuzzy": {
      "genre": {
        "value": "ama",
        "fuzziness": 2
      }
    }
  }
}