Reference

Endpoint Count documents

Name

count

Description

No description available

Parameters

Parameter Required Options
Name: Index

Label: Index (choose one --OR-- enter * --OR-- comma seperated names)

You can enter index name(s) for which you like to perform document count. Enter * (asterisk) to perform search across all indices or comma seperate list (i.e. myidx1,myidx2) or select one from the populated list. 		YES
Name: Query

Label: Enter Query (JSON Format)

 YES
Option Value
All Records {"match_all": { } }
Record where comment or name contains TV word {"query_string": {"query": "comment:TV OR name:TV"} }
Record with comment field (attribute exists) {"query_string": {"query": "_exists_:comment"} }

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
count DT_I8 bigint
If the column you are looking for is missing, consider customizing ElasticSearch Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use ElasticSearch Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - ElasticSearch
Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch
ElasticSearch
Count documents
Required Parameters
Index (choose one --OR-- enter * --OR-- comma seperated names) Fill-in the parameter...
Enter Query (JSON Format) Fill-in the parameter...
SSIS API Source - Read from table or endpoint

API Destination

API Destination - ElasticSearch
Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch
ElasticSearch
Count documents
Required Parameters
Index (choose one --OR-- enter * --OR-- comma seperated names) Fill-in the parameter...
Enter Query (JSON Format) Fill-in the parameter...
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Count ElasticSearch index documents using ElasticSearch Query language

Below example shows how to get just count of documents from Index (single, multiple or all index). Optionally you can supply expression to filter. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

SELECT * FROM count WITH(Index='MyIndexOrAliasName') --//get count of documents in index / alias named MyIndexOrAliasName
SELECT * FROM count WITH(Index='*') --//get count of documents in all indices (total distinct _id found across all indices + alias) 
SELECT * FROM count WITH(Index='MyIndex1,MyIndex2,MyAlias1,MyAlias2')--//get count of documents in indices named MyIndex1, MyIndex2 and Alias named MyAlias1,MyAlias2
SELECT * FROM count WITH(Index='MyIndexOrAliasName', Query='{"match": { "comment" : "TV" } }') --//get count of documents in MyIndex where comment field contains word "TV"

Count ElasticSearch alias documents using ElasticSearch Query language

Below example shows how to get just count of documents from Alias (single, multiple or all alias). Optionally you can supply expression to filter. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

SELECT * FROM count WITH(Index='MyIndexOrAliasName') --//get count of documents in index / alias named MyIndexOrAliasName
SELECT * FROM count WITH(Index='*') --//get count of documents in all indices (total distinct _id found across all indices + alias)
SELECT * FROM count WITH(Index='MyIndexOrAlias1,MyIndexOrAlias2') --//get count of documents in MyIndex1 and MyIndex2
SELECT * FROM count WITH(Index='MyIndex', Query='{"match": { "comment" : "TV" } }') --//get count of documents in Index named MyIndex where comment field contains word "TV"
SELECT * FROM count WITH(Index='MyAlias', Query='{"match": { "comment" : "TV" } }') --//get count of documents in Alias named MyAlias where comment field contains word "TV"

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Count ElasticSearch index documents using ElasticSearch Query language

Below example shows how to get just count of documents from Index (single, multiple or all index). Optionally you can supply expression to filter. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM count WITH(Index=''MyIndexOrAliasName'') --//get count of documents in index / alias named MyIndexOrAliasName
SELECT * FROM count WITH(Index=''*'') --//get count of documents in all indices (total distinct _id found across all indices + alias) 
SELECT * FROM count WITH(Index=''MyIndex1,MyIndex2,MyAlias1,MyAlias2'')--//get count of documents in indices named MyIndex1, MyIndex2 and Alias named MyAlias1,MyAlias2
SELECT * FROM count WITH(Index=''MyIndexOrAliasName'', Query=''{"match": { "comment" : "TV" } }'') --//get count of documents in MyIndex where comment field contains word "TV"';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Count ElasticSearch alias documents using ElasticSearch Query language

Below example shows how to get just count of documents from Alias (single, multiple or all alias). Optionally you can supply expression to filter. For more information on ElasticSearch Query expression check this link https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM count WITH(Index=''MyIndexOrAliasName'') --//get count of documents in index / alias named MyIndexOrAliasName
SELECT * FROM count WITH(Index=''*'') --//get count of documents in all indices (total distinct _id found across all indices + alias)
SELECT * FROM count WITH(Index=''MyIndexOrAlias1,MyIndexOrAlias2'') --//get count of documents in MyIndex1 and MyIndex2
SELECT * FROM count WITH(Index=''MyIndex'', Query=''{"match": { "comment" : "TV" } }'') --//get count of documents in Index named MyIndex where comment field contains word "TV"
SELECT * FROM count WITH(Index=''MyAlias'', Query=''{"match": { "comment" : "TV" } }'') --//get count of documents in Alias named MyAlias where comment field contains word "TV"';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];