Reference

Endpoint Get documents from Index or Alias

Name

get_documents

Description

Gets documents from Index or Alias [API reference]

Related Tables

[Dynamic Table] , [Dynamic Table]

Parameters

Parameter

Required

Options

Name: Index

Label: Index

YES

Name: Query

Label: Enter Query (JSON Format)

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

Name: CursorTimeout

Label: CursorTimeout (Minutes)

Enter this value carefully. Do not enter very long timeout else it will hold cursor longer on server memory space

Option	Value
1	1
2	2
5	5
10	10
60	60
180	180

Name: PageSize

Label: How many Record to fetch per request

Do not enter very large number or too small number to avoid performance issues.

Name: Alias

Label: Alias (Deprecated - Use Index instead)

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length
_id	`DT_WSTR`	`nvarchar(150)`	150
_score	`DT_R8`	`float`
[$parent.Pivot_Path$]	`DT_WSTR`	`nvarchar(4000)`	4000
[$parent.Pivot_Path$].lat	`DT_R8`	`float`
[$parent.Pivot_Path$].lon	`DT_R8`	`float`
[$parent.Pivot_Path$].type	`DT_WSTR`	`nvarchar(30)`	30
[$parent.Pivot_Path$].coordinates	`DT_TEXT`	`varchar(MAX)`

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

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - ElasticSearch

Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.

ElasticSearch

[Dynamic Table]

There are no parameters to configure.

SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get documents from index or alias:

API Destination - ElasticSearch

Read and write Elasticsearch data effortlessly. Integrate, manage, and automate indexes and documents — almost no coding required.

ElasticSearch

[Dynamic Table]

Select

There are no parameters to configure.

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read documents from index (all or with filter)

Returns documents from an index (or alias) by using the index name as the table name. For server-side filtering use the <code>Query</code> parameter in the <code>WITH</code> clause; <code>WHERE</code> runs client-side and is slower.

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Read documents from alias (all or with filter)

Returns documents from an alias. Use the alias name as the table name. For server-side filtering pass a query in the <code>WITH</code> clause.

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Search documents from index

Runs a full-text search on an index (or alias) using the Elasticsearch query DSL. Pass the query JSON in the <code>WITH</code> clause (e.g. <code>Query='{"match": { "comment" : "TV" } }'</code>). For more options see the Elasticsearch <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html">query DSL</a>.

SELECT * FROM MyIndexOrAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName', Query='{"match": { "comment" : "TV" } }')
		--SELECT * FROM search WITH(Index='MyIndexName', Alias='MyAliasName', Query='{"match": { "comment" : "TV" } }')

Search documents from alias

Runs a search on an alias (like a view over one or more indexes). Use the alias name in the <code>FROM</code> clause and pass the query in the <code>WITH</code> clause. For query syntax see the Elasticsearch query DSL documentation.

SELECT * FROM MyAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName',Index='MyAliasName', Query='{"match": { "comment" : "TV" } }')

Read documents from index (all or with filter)

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Read documents from alias (all or with filter)

Returns documents from an alias. Use the alias name as the table name. For server-side filtering pass a query in the <code>WITH</code> clause.

SELECT * FROM MyIndexOrAliasName --WITH(Query='{"match": { "PartNumber" : "P50" } }')

Search documents from index

SELECT * FROM MyIndexOrAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName', Query='{"match": { "comment" : "TV" } }')
		--SELECT * FROM search WITH(Index='MyIndexName', Alias='MyAliasName', Query='{"match": { "comment" : "TV" } }')

Search documents from alias

SELECT * FROM MyAliasName WITH(Query='{"match": { "comment" : "TV" } }')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index='MyIndexName',Index='MyAliasName', Query='{"match": { "comment" : "TV" } }')

get_documents endpoint belongs to [Dynamic Table] , [Dynamic Table] table(s), and can therefore be used via those table(s).

SQL Server

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

Read documents from index (all or with filter)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Read documents from alias (all or with filter)

Returns documents from an alias. Use the alias name as the table name. For server-side filtering pass a query in the <code>WITH</code> clause.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from index

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'', Query=''{"match": { "comment" : "TV" } }'')
		--SELECT * FROM search WITH(Index=''MyIndexName'', Alias=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from alias

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'',Index=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Read documents from index (all or with filter)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Read documents from alias (all or with filter)

Returns documents from an alias. Use the alias name as the table name. For server-side filtering pass a query in the <code>WITH</code> clause.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName --WITH(Query=''{"match": { "PartNumber" : "P50" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from index

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyIndexOrAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use below - slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'', Query=''{"match": { "comment" : "TV" } }'')
		--SELECT * FROM search WITH(Index=''MyIndexName'', Alias=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Search documents from alias

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM MyAliasName WITH(Query=''{"match": { "comment" : "TV" } }'')
		
		--or use search endpoint then you must supply both Index name and Alias name 
		--calling /search endpoint in FROM clause is slight faster (avoids table / alias list validation)
		--SELECT * FROM search WITH(Index=''MyIndexName'',Index=''MyAliasName'', Query=''{"match": { "comment" : "TV" } }'')';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

get_documents endpoint belongs to [Dynamic Table] , [Dynamic Table] table(s), and can therefore be used via those table(s).