Как сделать правельно запро в котором исключаются одни записи из других

[vsemirmd (Vic)]

в таблице всего 6 записей

{
"query": {
"bool": {
"must": [
{ "term": { "meta_key.normalized": "Content" } }
]
}
}
}
{ "meta_key.normalized": "Content" } содержится только у некоторых поэтому получаю 4 записи

{ "query": {
"bool": {
"must": [
{ "term": { "meta_values.normalized": "Text" } }
]
}
}
}
{ "meta_values.normalized": "Text" } содержится у всех поэтому у меня по этому запросу получается 6 записей как мне получить все записи у которых есть { "meta_values.normalized": "Text" } но отсутствует { "term": { "meta_key.normalized": "Content" } }

[Kathleen_DeRusso]

Hi there, you can add both as individual must clauses to your boolean query.

[vsemirmd (Vic)]

how to make notes in the first request from the second

[Kathleen_DeRusso]

I assume you mean you want to exclude the content. Add an exists query as a must_not clause to the boolean query.

[vsemirmd (Vic)]

{
"query": {
"bool": {
"must": [
{ "term": { "meta_values.normalized": "Text" } }
],
"must_not": [
{ "term": { "meta_key.normalized": "Content" } }
]
}
}
}

I make a query like this and it returns all 6 records but there should be 2

[Kathleen_DeRusso]

Your must_not clause is a term query, don't you want an exists query?

[vsemirmd (Vic)]

{
"size": 0,
"query": {
"bool": {
"should": [
{
"bool": {
"must": [
{ "match": { "meta_key.normalized": "Asset Type" } },
{ "wildcard": { "meta_values.normalized": "Text" } }
]
}
},
{
"bool": {
"must_not": [
{ "match": { "meta_key.normalized": "Content" } }
]
}
}
]
}
},
"aggs": {
"id": {
"terms": {
"field": "id",
"size": 500
},
"aggs": {
"the_filter": {
"bucket_selector": {
"buckets_path": { "the_doc_count": "_count" },
"script": "params.the_doc_count == 2"
}
}
}
}
}
}

So I made the following request where I select all assets that have Asset Type Text and that do not have Content, but it doesn’t give me the same results

[Kathleen_DeRusso]

I don't understand why you're nesting your boolean must_not query under the must clauses instead of creatin a must_not clause in the same boolean query. The boolean query docs should be helpful here.

Also, you're doing a bunch of stuff with wildcards etc. that aren't related to the original question. Keep it as simple as possible. Also make sure that your mappings are correct (text vs. keyword).

[vsemirmd (Vic)]

{
"size": 0,
"aggs": {
"grouped_by_id": {
"terms": {
"field": "id",
"size": 100
},
"aggs": {

    "content_count": {
      "sum": {
        "script": {
          "source": "doc['meta_key'].value == 'Content' ? 1 : 0"
        }
      }
    },
    "filter_no_content": {
      "bucket_selector": {
        "buckets_path": {
          "contentCount": "content_count"
        },
        "script": "params.contentCount == 0"
      }
    }
  }
}

}
}

I made a working request as I need it

but if I try to play it in C#

SELECT * FROM mediaassets.assetmetadata WHERE es_query='{"size": 0, "aggs": { "grouped_by_id": { "terms": { "field": "id", "size": 1000 }, "aggs": { "content_count": { "sum": { "script": { "source": "doc['meta_key'].value == 'Content' ? 1 : 0" } } }, "filter_no_content": { "bucket_selector": { "buckets_path": { "contentCount": "content_count" }, "script": "params.contentCount == 0" } } } } AND es_options='indices=assetmetadata*' ALLOW FILTERING';

I get this error, tell me how to write it correctly

line 1:205 mismatched input 'meta_key' expecting EOF (... mediaassets.assetmetadata WHERE es_query='{"size": 0, "aggs": { "grouped_by_id": { "terms": { "field": "id", "size": 1000 }, "aggs": { "content_count": { "sum": { "script": { "source": "doc['[meta_key]...)

[Kathleen_DeRusso]

Not sure what to tell you about your calling code other than to point out that we have a .NET client.