opensearch-project
Nested() function with sql joins
How to use nested() function when using SQL JOIN FOR example:
SELECT * FROM nested_mapping_sql_one WHERE contact_id=1001 AND nested(section_data,(section_data.section_id=66 AND section_data.field_id=288 AND section_data.data='rr') AND (section_data.section_id=99 AND section_data.field_id=122 AND section_data.data='ddd'))
The above example show using nested() function in SQL without using SQL joins
The below example shows using nested() function in sql join, but iam not getting the expected results. can anyone help me on this.
SELECT * FROM nested_native_mapping INNER JOIN nested_mapping_sql_one ON nested_native_mapping.contact_id=nested_mapping_sql_one.contact_id WHERE nested(nested_mapping_sql_one.section_data,nested_mapping_sql_one.section_data.section_id=33);
@anirudha @macohen @dblock @seraphjiang @dblock
@Shivacharangoud would you be able to provide the data or the response to the above queries and I can take a look?
@acarbonetto here is the details:********************************* @anirudha @macohen @hyandell @seraphjiang @hyandell Index 1 mapping:
{
"nested_native_mapping" : {
"mappings" : {
"properties" : {
"contact_id" : {
"type" : "long"
},
"name" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"fname" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"id" : {
"type" : "integer"
},
"sname" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
}
}
}
}
}
}
}
Index 1 data:
{
"took" : 5,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "nested_native_mapping",
"_type" : "_doc",
"_id" : "2",
"_score" : 1.0,
"_source" : {
"contact_id" : 1001,
"name" : [
{
"id" : 1,
"fname" : "lenovo",
"sname" : "ryzen"
},
{
"id" : 2,
"fname" : "hp",
"sname" : "intel"
}
]
}
},
{
"_index" : "nested_native_mapping",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.0,
"_source" : {
"contact_id" : 1001,
"name" : [
{
"id" : 1,
"fname" : "shivacharan",
"sname" : "koyyada"
},
{
"id" : 2,
"fname" : "shiva",
"sname" : "omm"
}
]
}
}
]
}
}
Index 2 mapping:
{
"nested_mapping_sql_one" : {
"mappings" : {
"properties" : {
"contact_id" : {
"type" : "long",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"enterprise_id" : {
"type" : "integer",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"entity_id" : {
"type" : "integer",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"form_id" : {
"type" : "long",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"section_data" : {
"type" : "nested",
"include_in_root" : true,
"properties" : {
"created_at" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},
"created_by" : {
"type" : "integer"
},
"data" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"field_id" : {
"type" : "long",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"section_id" : {
"type" : "long",
"fields" : {
"keyword" : {
"type" : "keyword"
}
}
},
"update_by" : {
"type" : "long"
},
"updated_at" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss"
},
"updated_by" : {
"type" : "integer"
}
}
}
}
}
}
}
Index 2 data:
{
"took" : 14,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "nested_mapping_sql_one",
"_type" : "_doc",
"_id" : "2",
"_score" : 1.0,
"_source" : {
"contact_id" : 1001,
"form_id" : 100,
"enterprise_id" : 5431,
"entity_id" : 58,
"section_data" : [
{
"section_id" : 33,
"field_id" : 45,
"data" : "n",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:44:12",
"updated_at" : "2022-10-12 06:44:12"
},
{
"section_id" : 66,
"field_id" : 288,
"data" : "rr",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:43:12",
"updated_at" : "2022-10-12 06:43:12"
},
{
"section_id" : 99,
"field_id" : 122,
"data" : "ddd",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:47:12",
"updated_at" : "2022-10-12 06:47:12"
},
{
"section_id" : 90,
"field_id" : 555,
"data" : "yoon",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:47:12",
"updated_at" : "2022-10-12 06:47:12"
}
]
}
},
{
"_index" : "nested_mapping_sql_one",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.0,
"_source" : {
"contact_id" : 1001,
"form_id" : 22,
"enterprise_id" : 5431,
"entity_id" : 58,
"section_data" : [
{
"section_id" : 22,
"field_id" : 22,
"data" : "nzb",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:44:12",
"updated_at" : "2022-10-12 06:44:12"
},
{
"section_id" : 22,
"field_id" : 23,
"data" : "1",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:43:12",
"updated_at" : "2022-10-12 06:43:12"
},
{
"section_id" : 30,
"field_id" : 44,
"data" : "dent",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:47:12",
"updated_at" : "2022-10-12 06:47:12"
},
{
"section_id" : 30,
"field_id" : 55,
"data" : "pen",
"created_by" : 1500,
"update_by" : 1500,
"created_at" : "2022-10-12 06:47:12",
"updated_at" : "2022-10-12 06:47:12"
}
]
}
}
]
}
}
I want to join this two indices on contact_id field using SQL join query, and need to perform filtering,How can i do this. If i use nested() function for nested fields, iam not getting expected data. Iam running this below query:
SELECT * FROM nested_native_mapping INNER JOIN nested_mapping_sql_one ON nested_native_mapping.contact_id=nested_mapping_sql_one.contact_id WHERE nested(nested_mapping_sql_one.section_data,nested_mapping_sql_one.section_data.section_id=33);
Result: iam not getting expected out Response looks like this:
{
"took" : 65,
"timed_out" : false,
"_shards" : {
"total" : 10,
"successful" : 10,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 0,
"relation" : "EQUAL_TO"
},
"max_score" : 1.0,
"hits" : [ ]
}
}
======================================
Please clear my issue
@acarbonetto @acarbonetto @anirudha @macohen @hyandell please respond
@acarbonetto @acarbonetto @acarbonetto @ananzh @acarbonetto @anirudha @hyandell @macohen @opensearch-ci-bot @penghuo @CarlMeadows @macohen @camerski