EndPoint Count documents

Name

count

Description

No description available

Parameters

Parameter

Label

Required

Options

Description

Index

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

YES

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.

Query

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"} }

[API reference]

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw	Description
count	`DT_I8`	`bigint`		`False`

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw	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 component to read data or in API Destination component to read/write data:

Count documents using API Source

ElasticSearch

Count documents

SSIS API Source - Read from table or endpoint

Count documents using API Destination

ElasticSearch

Count documents

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

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 [LINKED_SERVER_TO_ELASTICSEARCH_IN_DATA_GATEWAY];

Count ElasticSearch alias documents using ElasticSearch Query language

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 [LINKED_SERVER_TO_ELASTICSEARCH_IN_DATA_GATEWAY];