Return field value from array of objects

Elastic Stack Elasticsearch
1

I have the following document that is being loaded into elasticseatch 6:

{
"DataChangeInfo" : "Archive Log Set archiveLogSet.2,
"documentsList" : [
{
  "commandScn": "25933758",
  "commandCommitScn": "0",
  "commandSequence": "3",
  "commandType": "INSERT",
  "commandTimestamp": "2017-12-07 05:09:54+03:00",
  "objectDBName": "D",
  "objectSchemaName": "CFTNAXDEV",
  "objectId": "NERNAL",
  "changedFieldsList": [
    {
      "fieldId": "PK_NEWJOURNAL",
      "fieldType": "NUMBER",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "DESCRIPTION",
      "fieldType": "VARCHAR2",
      "fieldValue": "hello there",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "STATUS",
      "fieldType": "VARCHAR2",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
    },
{
      "fieldId": "REFERENCE",
      "fieldType": "VARCHAR2",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "CITY",
      "fieldType": "VARCHAR",
      "fieldValue": "NY",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "ACTIONID2",
      "fieldType": "NUMBER",
      "fieldValue": "NULL",
      "fieldChanged": "Y"
    },
    {
      "fieldId": "LOG_DATE2",
      "fieldType": "TIMESTAMP(6) WITH TIME ZONE",
      "fieldValue": "2017-12-06 22:09:54.941-05:00",
      "fieldChanged": "Y"
    }
  ],
  "conditionFieldsList": []
}
]
}

I want to search for a specific field value, when certain fieldId condition is met.
for example, I want all the cities in all documents. Meaning: I want all fieldValue when fieldId is equal to "CITY".

What kind of mapping to I need to make this an option?
What kind of query will yeild the result I want?

2

If you have a limited number of fields like this, you should reconsider your model.
If you have a lot of possible fields (more than 1000), then may be. But be careful here because you will index all fields as text which is meaningless in some cases like numbers, dates.

To answer your question, you need to define changedFieldsList as a nested type.

3

Hi and thanks for the answer.
Even if I change the type to nested, how will the query look like?

4

A bool query with 1 filter clause on the fieldId and one must clause on the fieldValue I guess.

5

Hi, I'm pretty new to elasticsearch queries, can you give me an example in writing of how this looks like?
Thanks, and sorry for the trouble...

6

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.

7

Providing this is a bit tricky, because accualy I plan on running this queries from kibana dev tools.
Anyway, can you provide my with example?

8

Here is an example of a bool query: https://www.elastic.co/guide/en/elasticsearch/reference/6.2/query-dsl-bool-query.html

And in case you need it, an example of a nested query: https://www.elastic.co/guide/en/elasticsearch/reference/6.2/query-dsl-nested-query.html

1 Like
9

Thank you so much!
Cheers from Israel

10

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