Ordering ES Document Fields like SQL


(Kashi Pashoria Tonny) #1

I'm using elasticsearch version 6.6. I'm upgraded from version 2.x to 6.6. I want to store my document fields in a specific order as it was in elasticsearch version 2.x that fields are saved in exact same order as they are in sql. I'm using logstash version 6.2.4 to import data from sql to ES. I've already tried saving mapping prior to import data but of no use.
If not possible to save fields in specific order then if it would be feasible to get documents with their fields arranged.
If anybody can help.


(David Pilato) #2

What do you mean by "specific order"?
Do you have an example?


(Kashi Pashoria Tonny) #3

Thanx for your quick reply!

I have columns in sql in the order:
Affiliate, AffiliateID, SalesID, Client, ClientID, SaleDate etc.

when I insert the table in ES, the document fields Affiliate, AffiliateID, SalesID, Client, ClientID, SaleDate are in a random order LIKE SalesID, AffiliateID, Client, Affiliate,ClientID,SaleDate.
I want to get result in same sequence as it was in SQL or atleast show them in my desired order irrespective of the order in which thy are saved.


(David Pilato) #4

So I'm unsure I understand what problem do you have with elasticsearch.
I mean that if you save all that information using a JSON document, then _source field is stored exactly as you send it...

If my answer is incorrect, could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.


(Kashi Pashoria Tonny) #5

This is the arrangement of columns in my SQL table.
1- _id
2- ORDER
3- OAR_ORDERID
4- DGSAffiliateID
5- SFAffiliate
6- Affiliate
7- G2B_AffiliateID
8- SalesID
9- Lead_AffiliateID
10- Lead_Affiliate
11- DGSClientID
12- SFClient
13- Client
14- ODSClient
and So on.........

When I insert data to elasticsearch, each row from the table becomes a single document that has column names as its fields but the arrangement of fields is not as it was in the table of SQL. Following is a json Doc:
image

Here in ES doc, the arrangement of fields is not as it was in sql ORDER goes down and SFAffiliate goes up and all fields are not at their desired place.

I have searched on it and it says json object is like hash table and it doesn't matter in what order are your keys stored but I need to at least fetch results in specific order.

And a serious problem is that the json doc saves the keys all small letters. For example in SQL my column name was SFAffiliate and in ES doc it becomes sfaffiliate, i.e, SFA become small.
This issue occurs only when I dump data from SQL Server to ES via logstash and JDBC and it doesn't occr when I take my data from SQL to CSV and then dump that CSV file to ES.


(system) closed #6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.