Reference

Endpoint Update documents

Name

update_documents

Description

Update documents

Related Tables

[Dynamic Table] , [Dynamic Table]

Parameters

Parameter Required Options
Name: Index

Label: Index

 YES
Name: Alias

Label: Alias (Deprecated - Use Index instead)

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
ErrorFound DT_BOOL bit
Result DT_WSTR nvarchar(4000) 4000
HttpStatus DT_I4 int
Id DT_WSTR nvarchar(50) 50
Index DT_WSTR nvarchar(150) 150
Type DT_WSTR nvarchar(50) 50
IndexUuid DT_WSTR nvarchar(50) 50
Reason DT_WSTR nvarchar(4000) 4000
ErrorType DT_WSTR nvarchar(250) 250
ErrorCausedByType DT_WSTR nvarchar(250) 250
ErrorCausedByreason DT_WSTR nvarchar(2000) 2000
Version DT_I4 int
SequenceNo DT_I4 int
PrimaryTerm DT_I4 int
ShardsFailed DT_I4 int
ShardsSuccessful DT_I4 int
ShardsTotal DT_I4 int
Took DT_I4 int
If the column you are looking for is missing, consider customizing ElasticSearch Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
_id DT_WSTR nvarchar(150) 150
[$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)
Required columns that you need to supply are bolded.

Examples

SSIS

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

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 update documents:

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
[Dynamic Table]
Update
Optional Parameters
DoNotAddMultiContentSeparator True
RowHeaderFooterContinueOnError True
RowHeader
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Update documents using various data types

This example shows how to update values for different datatype fields. Some fields can accept value as Raw JSON (e.g. nested, object, geo_point, geo_shape). Object field type can also accept value by nested field (e.g. [object_field.field1] ) 

UPDATE my_index
SET
	binary_field='SGVsbG8gd29ybGQ=', --base64 value of "Hello world"
	boolean_field=false, 
	byte_field=117, 
	date_field='2012-12-31T23:59:59.123', 
	double_field=1.123456789, 
	float_field=1.123456789, 
	--raw JSON must be in one line
	geo_point_field='{ "lat": 40.7128, "lon": -74.0060 }',
	--OR--
	--"geo_point_field.lat"=40.7128, 
	--"geo_point_field.lon"=-74.0060, 
		
	--raw JSON must be in one line
	geo_shape_field='{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }',
	
	integer_field=123,
	ip_field='127.0.0.1',
	keyword_field='thhi is text',
	long_field=1234567890,
	
	--raw JSON must be in one line
	nested_field='[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]',
	
	--raw JSON must be in one line
	object_field='{"field1":"A","field2":"B"}', 
	--OR--
	--[object_field.field1]='object field keyword 1',
	--[object_field.field1]=123,
	
	short_field=1, 
	text_field='text field '
WHERE _id=2 --user defined key
--OR-- use auto-generated key
--WHERE _id='MtsicZQBuOa42vmvEtWJ'

Update documents in index

Updates documents in index 

UPDATE MyIndex
  SET Col1 = 'NewValue-1', Col2 = 'NewValue-2'
  WHERE _Id = 'A1234'

Update raw document (_rawdoc_ usage)

This example shows how to update document(s) in a raw format. When you use column name _rawdoc_ then its treated as RAW body. Notice that we use @ before string literal in value. This allow to use escape sequence (in this case \n for new line). 

UPDATE shop 
		--raw JSON must be in one line
		SET _rawdoc_ = @'{"update": {"_index": "shop", "_id": "1"}}\n{ "doc": {"colors":["yellow","orange"] } }\n{"update": {"_index": "shop", "_id": "2"}}\n{ "doc": {"colors":["yellow","blue"] } }\n'

Update array or sub document

This example shows how to update Array / nested Sub-document by adding raw:: prefix infront of column name to treat column as json fragment 

UPDATE MyIndex
  --raw JSON must be in one line		
  SET name = 'abcd', [raw::colors]='["yellow","red"]', [raw::location]='{x:10, y:20}' 
  WHERE _id='1'

Update documents using various data types

This example shows how to update values for different datatype fields. Some fields can accept value as Raw JSON (e.g. nested, object, geo_point, geo_shape). Object field type can also accept value by nested field (e.g. [object_field.field1] ) 

UPDATE my_index
SET
	binary_field='SGVsbG8gd29ybGQ=', --base64 value of "Hello world"
	boolean_field=false, 
	byte_field=117, 
	date_field='2012-12-31T23:59:59.123', 
	double_field=1.123456789, 
	float_field=1.123456789, 
	--raw JSON must be in one line
	geo_point_field='{ "lat": 40.7128, "lon": -74.0060 }',
	--OR--
	--"geo_point_field.lat"=40.7128, 
	--"geo_point_field.lon"=-74.0060, 
		
	--raw JSON must be in one line
	geo_shape_field='{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }',
	
	integer_field=123,
	ip_field='127.0.0.1',
	keyword_field='thhi is text',
	long_field=1234567890,
	
	--raw JSON must be in one line
	nested_field='[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]',
	
	--raw JSON must be in one line
	object_field='{"field1":"A","field2":"B"}', 
	--OR--
	--[object_field.field1]='object field keyword 1',
	--[object_field.field1]=123,
	
	short_field=1, 
	text_field='text field '
WHERE _id=2 --user defined key
--OR-- use auto-generated key
--WHERE _id='MtsicZQBuOa42vmvEtWJ'

Update documents in index

Updates documents in index 

UPDATE MyIndex
  SET Col1 = 'NewValue-1', Col2 = 'NewValue-2'
  WHERE _Id = 'A1234'

Update raw document (_rawdoc_ usage)

This example shows how to update document(s) in a raw format. When you use column name _rawdoc_ then its treated as RAW body. Notice that we use @ before string literal in value. This allow to use escape sequence (in this case \n for new line). 

UPDATE shop 
		--raw JSON must be in one line
		SET _rawdoc_ = @'{"update": {"_index": "shop", "_id": "1"}}\n{ "doc": {"colors":["yellow","orange"] } }\n{"update": {"_index": "shop", "_id": "2"}}\n{ "doc": {"colors":["yellow","blue"] } }\n'

Update array or sub document

This example shows how to update Array / nested Sub-document by adding raw:: prefix infront of column name to treat column as json fragment 

UPDATE MyIndex
  --raw JSON must be in one line		
  SET name = 'abcd', [raw::colors]='["yellow","red"]', [raw::location]='{x:10, y:20}' 
  WHERE _id='1'

update_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:

Update documents using various data types

This example shows how to update values for different datatype fields. Some fields can accept value as Raw JSON (e.g. nested, object, geo_point, geo_shape). Object field type can also accept value by nested field (e.g. [object_field.field1] ) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE my_index
SET
	binary_field=''SGVsbG8gd29ybGQ='', --base64 value of "Hello world"
	boolean_field=false, 
	byte_field=117, 
	date_field=''2012-12-31T23:59:59.123'', 
	double_field=1.123456789, 
	float_field=1.123456789, 
	--raw JSON must be in one line
	geo_point_field=''{ "lat": 40.7128, "lon": -74.0060 }'',
	--OR--
	--"geo_point_field.lat"=40.7128, 
	--"geo_point_field.lon"=-74.0060, 
		
	--raw JSON must be in one line
	geo_shape_field=''{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }'',
	
	integer_field=123,
	ip_field=''127.0.0.1'',
	keyword_field=''thhi is text'',
	long_field=1234567890,
	
	--raw JSON must be in one line
	nested_field=''[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]'',
	
	--raw JSON must be in one line
	object_field=''{"field1":"A","field2":"B"}'', 
	--OR--
	--[object_field.field1]=''object field keyword 1'',
	--[object_field.field1]=123,
	
	short_field=1, 
	text_field=''text field ''
WHERE _id=2 --user defined key
--OR-- use auto-generated key
--WHERE _id=''MtsicZQBuOa42vmvEtWJ''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update documents in index

Updates documents in index 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE MyIndex
  SET Col1 = ''NewValue-1'', Col2 = ''NewValue-2''
  WHERE _Id = ''A1234''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update raw document (_rawdoc_ usage)

This example shows how to update document(s) in a raw format. When you use column name _rawdoc_ then its treated as RAW body. Notice that we use @ before string literal in value. This allow to use escape sequence (in this case \n for new line). 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE shop 
		--raw JSON must be in one line
		SET _rawdoc_ = @''{"update": {"_index": "shop", "_id": "1"}}\n{ "doc": {"colors":["yellow","orange"] } }\n{"update": {"_index": "shop", "_id": "2"}}\n{ "doc": {"colors":["yellow","blue"] } }\n''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update array or sub document

This example shows how to update Array / nested Sub-document by adding raw:: prefix infront of column name to treat column as json fragment 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE MyIndex
  --raw JSON must be in one line		
  SET name = ''abcd'', [raw::colors]=''["yellow","red"]'', [raw::location]=''{x:10, y:20}'' 
  WHERE _id=''1''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update documents using various data types

This example shows how to update values for different datatype fields. Some fields can accept value as Raw JSON (e.g. nested, object, geo_point, geo_shape). Object field type can also accept value by nested field (e.g. [object_field.field1] ) 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE my_index
SET
	binary_field=''SGVsbG8gd29ybGQ='', --base64 value of "Hello world"
	boolean_field=false, 
	byte_field=117, 
	date_field=''2012-12-31T23:59:59.123'', 
	double_field=1.123456789, 
	float_field=1.123456789, 
	--raw JSON must be in one line
	geo_point_field=''{ "lat": 40.7128, "lon": -74.0060 }'',
	--OR--
	--"geo_point_field.lat"=40.7128, 
	--"geo_point_field.lon"=-74.0060, 
		
	--raw JSON must be in one line
	geo_shape_field=''{ "type": "polygon", "coordinates": [[[-74.0060, 40.7128], [-73.9960, 40.7128], [-73.9960, 40.7028], [-74.0060, 40.7028], [-74.0060, 40.7128]]] }'',
	
	integer_field=123,
	ip_field=''127.0.0.1'',
	keyword_field=''thhi is text'',
	long_field=1234567890,
	
	--raw JSON must be in one line
	nested_field=''[{"nested_property_1":"nested text 1", "nested_property_2":100}, {"nested_property_1":"nested text 2", "nested_property_2":101}]'',
	
	--raw JSON must be in one line
	object_field=''{"field1":"A","field2":"B"}'', 
	--OR--
	--[object_field.field1]=''object field keyword 1'',
	--[object_field.field1]=123,
	
	short_field=1, 
	text_field=''text field ''
WHERE _id=2 --user defined key
--OR-- use auto-generated key
--WHERE _id=''MtsicZQBuOa42vmvEtWJ''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update documents in index

Updates documents in index 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE MyIndex
  SET Col1 = ''NewValue-1'', Col2 = ''NewValue-2''
  WHERE _Id = ''A1234''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update raw document (_rawdoc_ usage)

This example shows how to update document(s) in a raw format. When you use column name _rawdoc_ then its treated as RAW body. Notice that we use @ before string literal in value. This allow to use escape sequence (in this case \n for new line). 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE shop 
		--raw JSON must be in one line
		SET _rawdoc_ = @''{"update": {"_index": "shop", "_id": "1"}}\n{ "doc": {"colors":["yellow","orange"] } }\n{"update": {"_index": "shop", "_id": "2"}}\n{ "doc": {"colors":["yellow","blue"] } }\n''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

Update array or sub document

This example shows how to update Array / nested Sub-document by adding raw:: prefix infront of column name to treat column as json fragment 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE MyIndex
  --raw JSON must be in one line		
  SET name = ''abcd'', [raw::colors]=''["yellow","red"]'', [raw::location]=''{x:10, y:20}'' 
  WHERE _id=''1''';

EXEC (@MyQuery) AT [LS_TO_ELASTICSEARCH_IN_GATEWAY];

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