CODEWITHSUNDEEP
databaseelasticsearch
Vikram Belde
Vikram Belde

Reputation: 1188

How to construct a JSON based query on nested object having array items with ES search?

[
    {
        "name": "Document 1",
        "tags": {
            "typeATags": ["a1"],
            "typeBTags": ["b1"],
            "typeCTags": ["c1"],
            "typeDTags": ["d1"]
        }
    },
    {
        "name": "Document 2",
        "tags": {
            "typeATags": ["a2"],
            "typeBTags": ["b1", "b2"],
            "typeCTags": ["c2"],
            "typeDTags": ["d1", "d2"]
        }
    },
    {
        "name": "Document 3",
        "tags": {
            "typeATags": ["a1", "a2", "a3"],
            "typeBTags": ["b1", "b2", "b3"],
            "typeCTags": ["c3"],
            "typeDTags": ["d1", "d2", "d3"]
        }
    }
]

How to build a query on ES 6.0, That will return all the records that has 'a1' and 'b1' tags ? // should return 1,3

That will return all the records that has 'a1' and 'a2' tags combined? // should return 3

That will return all the records that has 'a1' or 'a2' tags ? //should return 1,2,3

That will return all the records that has 'a1' AND ( 'c1' OR 'c3') tags ? //should return 1,2

Thanks @mickl for the Answer Edit 1:

Here is my actual Schema,

{
    "cmslocal": {
        "mappings": {
            "video": {
                "properties": {
                    "assetProps": {
                        "properties": {
                            "assetType": {
                                "type": "string"
                            },
                            "configPath": {
                                "type": "string",
                                "fields": {
                                    "raw": {
                                        "type": "string",
                                        "index": "not_analyzed"
                                    }
                                },
                                "analyzer": "standard"
                            },
                            "contentSha1": {
                                "type": "string"
                            },
                            "originalPath": {
                                "type": "string",
                                "fields": {
                                    "raw": {
                                        "type": "string",
                                        "index": "not_analyzed"
                                    }
                                },
                                "analyzer": "standard"
                            },
                            "path": {
                                "type": "string",
                                "fields": {
                                    "raw": {
                                        "type": "string",
                                        "index": "not_analyzed"
                                    }
                                },
                                "analyzer": "standard"
                            },
                            "thumbnailPath": {
                                "type": "string",
                                "fields": {
                                    "raw": {
                                        "type": "string",
                                        "index": "not_analyzed"
                                    }
                                },
                                "analyzer": "standard"
                            }
                        }
                    },
                    "channel": {
                        "type": "string"
                    },
                    "configProps": {
                        "properties": {
                            "events": {
                                "type": "nested",
                                "include_in_root": true,
                                "properties": {
                                    "Desc": {
                                        "type": "string"
                                    },
                                    "Tags": {
                                        "type": "string"
                                    },
                                    "UUID": {
                                        "type": "string"
                                    }
                                }
                            },
                            "roiUUID": {
                                "type": "string"
                            }
                        }
                    },
                    "contentSha1": {
                        "type": "string"
                    },
                    "eventDesc": {
                        "type": "string"
                    },
                    "ext": {
                        "type": "string"
                    },
                    "format": {
                        "type": "string"
                    },
                    "fovProps": {
                        "properties": {
                            "description": {
                                "type": "string"
                            },
                            "width": {
                                "type": "float"
                            }
                        }
                    },
                    "locationProps": {
                        "type": "nested",
                        "properties": {
                            "address": {
                                "type": "string"
                            },
                            "city": {
                                "type": "string"
                            },
                            "country": {
                                "type": "string"
                            },
                            "county": {
                                "type": "string"
                            },
                            "location": {
                                "type": "geo_point"
                            },
                            "postcode": {
                                "type": "string"
                            },
                            "state": {
                                "type": "string"
                            }
                        }
                    },
                    "nodeid": {
                        "type": "string"
                    },
                    "poleHeight": {
                        "type": "float"
                    },
                    "query": {
                        "properties": {
                            "bool": {
                                "properties": {
                                    "filter": {
                                        "properties": {
                                            "term": {
                                                "properties": {
                                                    "nodeid": {
                                                        "type": "string"
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    },
                    "retentionPolicy": {
                        "type": "string"
                    },
                    "siteScopeID": {
                        "type": "string"
                    },
                    "tagProps": {
                        "type": "nested",
                        "properties": {
                            "conditions": {
                                "type": "string"
                            },
                            "environment": {
                                "type": "string"
                            },
                            "events": {
                                "type": "string"
                            },
                            "lighting": {
                                "type": "string"
                            },
                            "objects": {
                                "type": "string"
                            },
                            "other": {
                                "type": "string"
                            },
                            "scenes": {
                                "type": "string"
                            },
                            "useCases": {
                                "type": "string"
                            },
                            "weather": {
                                "type": "string"
                            }
                        }
                    },
                    "test": {
                        "type": "string"
                    },
                    "title": {
                        "type": "string"
                    },
                    "uploadTime": {
                        "type": "date",
                        "format": "strict_date_optional_time||epoch_millis"
                    },
                    "videoProps": {
                        "properties": {
                            "bitrate": {
                                "type": "float"
                            },
                            "datetime": {
                                "type": "date",
                                "format": "date_hour_minute_second_millis"
                            },
                            "daySegments": {
                                "type": "string"
                            },
                            "duration": {
                                "type": "long"
                            },
                            "framerate": {
                                "type": "float"
                            },
                            "height": {
                                "type": "integer"
                            },
                            "overlaysOn": {
                                "type": "boolean"
                            },
                            "width": {
                                "type": "integer"
                            }
                        }
                    }
                }
            }
        }
    }
}

Please help to build the query so I can search for

  1. Only nodeId, Only Channel, Date Range
  2. Any of the Tags

I'm able to search for nodeId, Channel Id using

{
    "query": {
        "bool": {
            "filter": [
                { "match": { "nodeid": "N02cff15a" } },
                { "match": { "channel": "1" } }
            ]
        }
    }
}

and I can able to Search for tagProps using

{
    "nested": {
        "path": "tagProps",
        "query": {
            "bool": {
                "must": [
                    { "match": { "tagProps.objects": "car" } },
                    { "match": { "tagProps.objects": "truck" } }
                ]
            }
        }
    }
}

Help me combine two queries so I can search for NodeId with Combination of Tags.

Upvotes: 0

Views: 66

Answers (1)

mickl
mickl

Reputation: 49985

Since tags is a nested field you should define nested mapping before indexing your documents.

{
  "mappings": {
    "your_type": {
      "properties": {
        "tags": {
          "type": "nested" 
        }
      }
    }
  }
}

Now you can index your data and then you can utilize nested query syntax, for your first use case it's like:

{
  "query": {
    "nested": {
      "path": "tags",
      "query": {
        "bool": {
          "must": [
            { "match": { "tags.typeATags": "a1" }},           
            { "match": { "tags.typeBTags": "b1" }}          
          ]
        }
      }
    }
  }
}

Next queries can be composed of must and should, like for the last one:

{
  "query": {
    "nested": {
      "path": "tags",
      "query": {
        "bool": {
          "must": [
                { "match": { "tags.typeATags": "a1" }}
          ],
          "should": [
                {"match": {"tags.typeCTags": "c1"}},
                {"match": {"tags.typeCTags": "c3"}}
            ]
        }
      }
    }
  }
}

Upvotes: 1

Related Questions