Using an aggregated entity query to find duplicate taxonomy terms Due to a content migration bug, there are nodes in our site that reference the same taxonomy term multiple times with different deltas:

bundle  deleted entity_id   revision_id langcode    delta   field_term_target_id
article 0   1061    1062    en  0   307
article 0   1061    1062    en  1   307


I can find these with the following database query

SELECT
    entity_id, field_term_target_id, COUNT(*) as count
FROM node__field_industries
GROUP BY entity_id, field_industries_target_id
HAVING count &gt; 1;


For building a tool that lists these nodes within Drupal, I would like to use an entity query instead of accessing the database directly.

I've tried the following:

\Drupal::entityTypeManager()
  -&gt;getStorage('node')
  -&gt;getAggregateQuery()
  -&gt;groupBy('nid')
  -&gt;groupBy('field_industries')
  -&gt;aggregate('field_industries.delta', 'COUNT')
  -&gt;conditionAggregate('field_industries.delta', 'COUNT', 1, '&gt;')
  -&gt;execute();


But this results in an error Invalid specifier 'delta'. If I aggregate on 'field_industries' instead (which seems wrong, as I'm already grouping on that value), I get completely wrong results back (specifically, vastly inflated values for COUNT, which can be as high as 16 even for nodes that are not affected by the bug).

So I guess my question is: How do I aggregate on a non-default column in the field table (such as the delta column)?
You can use the Database abstraction layer:

$connection = Drupal::database();
$query = $connection-&gt;select('node__field_industries', 'i');
$query-&gt;fields('i', ['entity_id', 'field_term_target_id']);
$query-&gt;addExpression('COUNT(*)', 'count');
$query-&gt;groupBy('i.entity_id');
$query-&gt;groupBy('i.field_term_target_id');
$query-&gt;having('COUNT(*) &gt; 1');

$results = $query-&gt;execute()-&gt;fetchAll();


Hope this helps