[BUG] Query workbench PPL 'parse' syntax doesn't work on nested/non-single line field values

[opensearch1]

Describe the bug

'parse' PPL syntax doesn't seem to extract nested/non-single line field values. Documentation examples are very limited (ref https://github.com/opensearch-project/sql/blob/main/docs/user/ppl/cmd/parse.rst)

Related component

Search:Query Capabilities

To Reproduce

1. Go to Query workbench then select PPL
2. Query indexes that have nested field values

Expected behavior

Parse syntax should return a field value

Additional Details

[YANG-DB]

Thanks for bringing this to our attention.

[acarbonetto]

I was able to run this query using parse on nested values, to get this result:

/_plugins/_ppl:
{
  "query" : "source=nested | parse message.info '.*a(?<name>.+)' | fields message.info, name"
}

Response: 
{
    "schema": [
        {
            "name": "message.info",
            "type": "string"
        },
        {
            "name": "name",
            "type": "string"
        }
    ],
    "datarows": [
        [
            "a",
            ""
        ],
        [
            "bab",
            "b"
        ],
        [
            "aba",
            "ba"
        ]
    ],
    "total": 3,
    "size": 3
}

The parse command doesn't work against multi-values, and results in an error:

/_plugins/_ppl:
{
  "query" : "source=nested | parse message.info '.*a(?<name>.+)' | fields message.info, name"
}

Response: 
{
    "schema": [
        {
            "name": "mv",
            "type": "string"
        }
    ],
    "datarows": [
        [
            [
                "aaab",
                "aab",
                "ab",
                "b"
            ]
        ],
        [
            "aaac"
        ],
        [
            [
                "d",
                "ad",
                "aad",
                "aaad"
            ]
        ]
    ],
    "total": 3,
    "size": 3
}

/_plugins/_ppl:
{
  "query" : "source=nested | parse mv '.*a(?<name>.+)' | fields mv, name"
}

Response: 
{
  "error": {
    "reason": "Invalid Query",
    "details": "failed to parse field \"mv\" with type [ARRAY]",
    "type": "SemanticCheckException"
  },
  "status": 400
}

[acarbonetto]

We could be more explicit about how the syntax works in the documentation: https://github.com/opensearch-project/sql/blob/main/docs/user/ppl/cmd/parse.rst

Also, add a limitation that multi-valued fields result in an error.

[YANG-DB]

@acarbonetto I've asked for details on the version this issue was discovered

[acarbonetto]

I think there's a problem with your match query: .+(?<name>.+) won't match anything for name. The .+ match is a wildcard matcher that will pick up everything leaving nothing to put into the name field. According to the screenshot, name is returning empty string results (I believe that's what's going on).

Depending on your data, you can try and include a pattern that matches more than one variable in the field.

[opensearch1]

Hi,

This is being used in OpenSearch 2.15 under query workbench.

[opensearch1]

@acarbonetto it looks that way since I just copied the unparsed values. it looks like a table (unsure if it's JSON or something else) that there's set of fields and values (see screenshot). The complete values appear when I hover the mouse or copy them.