Select nodes of the specific content type using entity query

Let's start from the most simple example. Fetch all nodes of type article
 

$query = \Drupal::service('entity_type.manager')->getStorage('node')->getQuery()
  ->condition('type', 'article')
$results = $query->execute();

Select nodes by a simple field using entity query

Let's imagine our node have a simple field field_text and we want to fetch all nodes with some value.

$query = \Drupal::service('entity_type.manager')->getStorage('node')->getQuery()
  ->condition('field_text', 'my value')
$results = $query->execute();

Select nodes by a complex fields (containing several subvalues) using entity query

Let's imagine we have a field_link of type Link. Such fields contain uri and title. 

$query = \Drupal::service('entity_type.manager')->getStorage('node')->getQuery()
  ->condition('field_link', 'my value')
$results = $query->execute();

In this case by default uri will be used and we will get 

WHERE node__field_link.field_link_uri = '1'

To filter by the title of the link we need to use the next code

$results = \Drupal::service('entity_type.manager')->getStorage('node')->getQuery()
  ->condition('field_link.title', 'my value')
$results = $query->execute();

Select nodes by a reference fields using entity query

Let's imagine we have field_tags which has a reference to taxonomy tags.

$query = \Drupal::service('entity_type.manager')->getStorage('node')->getQuery()
  ->condition('type', 'article')
  ->condition('field_tag.entity:taxonomy_term.name', 'My tag')
$results = $query->execute();

As a result we will get such SQL query:

SELECT base_table.vid AS vid, base_table.nid AS nid FROM node base_table INNER JOIN node__field_tag node__field_tag ON node__field_tag.entity_id = base_table.nid LEFT OUTER JOIN taxonomy_term_data taxonomy_term_data ON taxonomy_term_data.tid = node__field_tag.field_tag_target_id INNER JOIN taxonomy_term_field_data taxonomy_term_field_data ON taxonomy_term_field_data.tid = taxonomy_term_data.tid WHERE taxonomy_term_field_data.name LIKE 'My tag' ESCAPE '\\'SELECT base_table.vid AS vid, base_table.nid AS nid FROM node base_table INNER JOIN node__field_tag node__field_tag ON node__field_tag.entity_id = base_table.nid LEFT OUTER JOIN taxonomy_term_data taxonomy_term_data ON taxonomy_term_data.tid = node__field_tag.field_tag_target_id INNER JOIN taxonomy_term_field_data taxonomy_term_field_data ON taxonomy_term_field_data.tid = taxonomy_term_data.tid WHERE taxonomy_term_field_data.name LIKE 'My tag' ESCAPE '\\'SELECT base_table.vid AS vid, base_table.nid AS nid FROM node base_table INNER JOIN node__field_tag node__field_tag ON node__field_tag.entity_id = base_table.nid LEFT OUTER JOIN taxonomy_term_data taxonomy_term_data ON taxonomy_term_data.tid = node__field_tag.field_tag_target_id INNER JOIN taxonomy_term_field_data taxonomy_term_field_data ON taxonomy_term_field_data.tid = taxonomy_term_data.tid WHERE taxonomy_term_field_data.name LIKE 'My tag' ESCAPE '\\'

We can the same way extract the field of referenced entities and their referenced entities.