Exists does not seem to work for Object type fields

Hi, so my query looks like this

{
    "query": {
        "bool": {
            "must_not": [
                {
                    "exists": {
                        "field": "custom_fields"
                    }
                }
            ]
        }
    }
}

Here is my mapping

{
    "accelq_testcase_v1": {
        "mappings": {
            "properties": {
                "custom_fields": {
                    "type": "nested",
                    "properties": {
                        "db_column_name": {
                            "type": "keyword",
                            "index": false
                        },
                        "is_required": {
                            "type": "boolean",
                            "index": false
                        },
                        "label": {
                            "type": "text"
                        },
                        "pid": {
                            "type": "integer",
                            "index": false
                        },
                        "unique_name": {
                            "type": "keyword"
                        },
                        "value": {
                            "type": "keyword"
                        }
                    }
                }
            }
        }
    }
}

Here's the response

{
    "took": 6,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 73,
            "relation": "eq"
        },
        "max_score": 0.0,
        "hits": [
            {
                "_index": "accelq_testcase_v1",
                "_id": "76",
                "_score": 0.0,
                "_source": {
                    "pid": 76,
                    "scenario": {
                        "pid": 75,
                        "name": "Test Scenario"
                    },
                    "name": "esfr",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "accelqProject",
                        "deleted_timestamp": null,
                        "project_display_name": "accelq Project",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "77",
                "_score": 0.0,
                "_source": {
                    "pid": 77,
                    "scenario": {
                        "pid": 32,
                        "name": "Verify Funds Transfer on QBank"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Application Module",
                            "pid": 8,
                            "label": "Application Module",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": "Retail Banking"
                        },
                        {
                            "unique_name": "Business Priority",
                            "pid": 13,
                            "label": "Business Priority",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "P2"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_5",
                            "value": "High"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": null
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": "Medium"
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "Sanity"
                        }
                    ],
                    "name": "test test case",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "1",
                "_score": 0.0,
                "_source": {
                    "pid": 1,
                    "scenario": {
                        "pid": 12,
                        "name": "Proceed Bill Payment"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Application Module",
                            "pid": 8,
                            "label": "Application Module",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": null
                        },
                        {
                            "unique_name": "Business Priority",
                            "pid": 13,
                            "label": "Business Priority",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "P2"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_5",
                            "value": "High"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "English"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": "High"
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "Smoke"
                        }
                    ],
                    "name": "Mobile bill",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "2",
                "_score": 0.0,
                "_source": {
                    "pid": 2,
                    "scenario": {
                        "pid": 12,
                        "name": "Proceed Bill Payment"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Application Module",
                            "pid": 8,
                            "label": "Application Module",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": null
                        },
                        {
                            "unique_name": "Business Priority",
                            "pid": 13,
                            "label": "Business Priority",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "P2"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_5",
                            "value": "High"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "English"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": "High"
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "Smoke"
                        }
                    ],
                    "name": "Electrical bill",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "3",
                "_score": 0.0,
                "_source": {
                    "pid": 3,
                    "scenario": {
                        "pid": 22,
                        "name": "Update User Info"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Application Module",
                            "pid": 8,
                            "label": "Application Module",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": null
                        },
                        {
                            "unique_name": "Business Priority",
                            "pid": 13,
                            "label": "Business Priority",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "P2"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_5",
                            "value": "High"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "English"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": "High"
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "Smoke"
                        }
                    ],
                    "name": "Update Email",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "4",
                "_score": 0.0,
                "_source": {
                    "pid": 4,
                    "scenario": {
                        "pid": 21,
                        "name": "Verify transaction History"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Application Module",
                            "pid": 8,
                            "label": "Application Module",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": null
                        },
                        {
                            "unique_name": "Business Priority",
                            "pid": 13,
                            "label": "Business Priority",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "P2"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_5",
                            "value": "High"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "English"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": "High"
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "Smoke"
                        }
                    ],
                    "name": "History",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "5",
                "_score": 0.0,
                "_source": {
                    "pid": 5,
                    "scenario": {
                        "pid": 34,
                        "name": "Verify Fund Transfer UI"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": "Low"
                        },
                        {
                            "unique_name": "Release",
                            "pid": 14,
                            "label": "Release",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "R1"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "Smoke"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": null
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "R1"
                        }
                    ],
                    "name": "Fund transfer from Business account to Mortagage bill",
                    "status": true,
                    "test_type": "MANUAL",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "6",
                "_score": 0.0,
                "_source": {
                    "pid": 6,
                    "scenario": {
                        "pid": 34,
                        "name": "Verify Fund Transfer UI"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": "Low"
                        },
                        {
                            "unique_name": "Release",
                            "pid": 14,
                            "label": "Release",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "R1"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "Smoke"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": null
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "R1"
                        }
                    ],
                    "name": "Fund transfer from Current account to Electricity bill",
                    "status": true,
                    "test_type": "MANUAL",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "7",
                "_score": 0.0,
                "_source": {
                    "pid": 7,
                    "scenario": {
                        "pid": 7,
                        "name": "Dynamic app URL example"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Application Module",
                            "pid": 8,
                            "label": "Application Module",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": "Financial Advisory"
                        },
                        {
                            "unique_name": "Business Priority",
                            "pid": 13,
                            "label": "Business Priority",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "P2"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_5",
                            "value": "Medium"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": "English"
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": "Medium"
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "Sanity"
                        }
                    ],
                    "name": "tc1",
                    "status": true,
                    "test_type": "AUTO",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            },
            {
                "_index": "accelq_testcase_v1",
                "_id": "8",
                "_score": 0.0,
                "_source": {
                    "pid": 8,
                    "scenario": {
                        "pid": 39,
                        "name": "Book Flight and Verify"
                    },
                    "scenario_custom_fields": [
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_3",
                            "value": "Low"
                        },
                        {
                            "unique_name": "Release",
                            "pid": 14,
                            "label": "Release",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "R1"
                        }
                    ],
                    "custom_fields": [
                        {
                            "unique_name": "Locale",
                            "pid": 11,
                            "label": "Locale",
                            "is_required": false,
                            "db_column_name": "field_3",
                            "value": null
                        },
                        {
                            "unique_name": "Priority",
                            "pid": 12,
                            "label": "Priority",
                            "is_required": true,
                            "db_column_name": "field_2",
                            "value": null
                        },
                        {
                            "unique_name": "Test Case Type",
                            "pid": 7,
                            "label": "Test Case Type",
                            "is_required": true,
                            "db_column_name": "field_1",
                            "value": "R1"
                        }
                    ],
                    "name": "platnium-Single City-NYC-Economy-af",
                    "status": true,
                    "test_type": "MANUAL",
                    "project": {
                        "project_name": "Qbank",
                        "deleted_timestamp": null,
                        "project_display_name": "Qbank",
                        "is_deleted": false
                    }
                }
            }
        ]
    }
}

As you see, it returns those docs where the field/key "custom_fields" is present

'custom_fields' is not an indexed field in any document, so you can not use an exists clause.

Could you please elaborate? New to EQL
The idea is to query all the docs where custom_field does not exists as a field

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