I’m losing my mind over here and OpenSearch’s documentation is completely unhelpful as everything is hardcoded values.

Anyways, I have a nested field that is supposed to be flattened into an array but it’s not working so I’m obviously missing something (ignore the goofy naming of the field; it’s so we don’t have to make drastic changes to a plugin we’re currently using)

Index is created with this command:

curl -XPUT -u "$OPENSEARCH_USER:$OPENSEARCH_PASSWORD" $OPENSEARCH_HOST_URL/mechanism-search -H "Content-Type: application/json" -d @/tmp/mechanism-search-index.json

mechanism-search-index.json

{
  "mappings": {
    "properties": {
      "mechanism": {
        "properties": {
          "mechanism_id" : {
            "type" : "integer"
          },
          "mechanism_name": {
            "type": "text"
          }
        }
      },
      "projects": {
        "type" : "nested",
        "include_in_parent": "true",
        "properties": {
          "project_id__type_list": {
            "type": "integer"
          },
          "project_name__type_list": {
            "type": "text"
          }
        }
      }
    }
  }

The Projects should be nested fields of the Mechanism so I’d expect something like this to be returned with GET mechanism-search/_search:

"mechanism_id" : "1111",
"mechanism_name" : "Test Mech",
"projects" : [
   {
     "project_id__type_list" : 1,
     "project_name__type_list" : "testing1"
   },
   {
     "project_id__type_list" : 2,
     "project_name__type_list" : "testing2"
   },
   {
     "project_id__type_list" : 3,
     "project_name__type_list" : "testing3"
   },
   {
     "project_id__type_list" : 4,
     "project_name__type_list" : "testing4"
   }
 ]

Instead I get:

"mechanism_id" : "1111",
"mechanism_name" : "Test Mech",
"project_id__type_list" : 1,
"project_name__type_list" : "testing1"

The field names in the mapping and the DB view match exactly. Why is it only grabbing a single Project for the Mechanism when there are multiple rows in the DB? Opensearch’s Documentation is all using PUTS and hardcoded values directly to a document; how am I supposed to make this automatic? I’m coming from SOLR and none of this is making any sense and seems massively overly complicated.

Mechanism’s and Projects are joined via a cross-table and the DB view OpenSearch uses points to it with a join.
Sample DB View Data: