field_name_not_in_query - Filter operators

Filters a query using the results of another query. Returns the rows that are not included in the other query’s results. In SQL, this generates a WHERE clause that looks like WHERE NOT IN (SELECT... where the SELECT clause is the filtering query.

To include data using the results of another query, use field_name_in_query.

Requirements

Only supported for use in query views. Refer to the Examples section for the complete syntax.

Syntax

<field_name>:
  field_name_not_in_query: <filtering_field_name>

Properties

string

required

The field to filter on, specified in the format view_name.field_name. Can be a time, numeric, or string field.

string

required

The name of the field in the filtering query, specified in the format view_name.field_name. This field must contain the same data and be the same data type as the field_name.For example, if you filter on a customers.country field, filtering_field_name should contain a field with country data.

Examples

Click the SQL tab in a code block to view the query’s underlying SQL definition.

The first query in this example - shown below - lists the top 5 countries with the highest number of customer sign ups:

| customers.country | customers.count |
|-------------------|-----------------|
| United States	    | 71              |
| Mexico            | 23              |
| Singapore         | 15              |
| Italy	            | 14              |
| Portugal          | 9               |

  SELECT "country" AS "customers.country",
         COUNT(*) AS "customers.count"
    FROM "blobs_r_us"."main"."customers" AS "customers"
GROUP BY 1
ORDER BY 2 DESC
   LIMIT 5

The second query applies a filter that uses the results of Query 1. Because the field_name_not_in_query excludes data, the results should not include United States, Mexico, Singapore, Italy, and Portugal:

| customers.country | orders.count |
|-------------------|--------------|
| Netherlands       | 12           |
| Denmark           | 11           |
| Japan             | 11           |
| South Africa	    | 9            |
| Finland           | 6            |

   SELECT "customers"."country" AS "customers.country",
           COUNT(*) AS "orders.count"
     FROM "blobs_r_us"."main"."orders" AS "orders"
LEFT JOIN "blobs_r_us"."main"."customers" AS "customers"
       ON "orders"."customer_id" = "customers"."id"
    WHERE "customers"."country"
   NOT IN (
              SELECT "country" AS "customers.country"
                FROM "blobs_r_us"."main"."customers" AS "customers"
            GROUP BY "country"
            ORDER BY COUNT(*) DESC
              LIMIT 5
           )
 GROUP BY 1
 ORDER BY 1 DESC
 LIMIT 5

The underlying YAML for a query view created from Query 2 would look like the following:

Query view filtered using the results of another query

label: Total orders - Excluding most popular countries

query:
  fields:
    customers.country: country
    orders.count: dim_count
  base_view: orders
  filters:
    customers.country:
      field_name_not_in_query: customers.country
      query_structure:
        fields: [ customers.country, customers.count ]
        base_view: customers
        limit: 5
        sorts:
          - field: customers.count
            desc: true
        offset: 0
  sorts:
    - field: customers.country
      desc: true

dimensions:
  dim_count: {}

  country:
    primary_key: true

measures:
  count:
    aggregate_type: count

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

field_name_not_in_query - Filter operators

Requirements

Syntax

Properties

Examples

​Requirements

​Syntax

​Properties

​Examples

Requirements

Syntax

Properties

Examples