How to implement support for multi-values fields (#1300)?

[Yury-Fridlyand]

I opened this topic to discuss how to implement fix for #1300. Unlike issue, a discussion allows open threads to post replies to every message, which could be more useful there.

---

I want to share different ways to implement that below and collect your opinions and votes. Before that let me clarify some requirements and describe why legacy engine works incorrect (given that we can't implement this in V2 the same ways as in V1).

Requirements

1. A column K in a table N can't change the type.
2. All values in a column K in a table N always should be in the same type.
3. New docs could be added or deleted from an OpenSearch index represented by a table N.
4. An OpenSearch index mapping cannot be changed.
5. The main (targeted) consumer of the SQL plugin is a third-party application like JDBC driver.

---

Sample data

An index called doesn't-matter-how dbg with 3 docs, all docs have only one field.
To simplify understanding the response I'll add _doc to the response even though V1 doesn't provide it.

The mapping

"mappings" :
{
  "properties" :
  {
    "myNum" :
    {
      "type" : "long"
    }
  }
}

The docs

[
  {
    "myNum" : 5
  },
  {
    "myNum" : [3, 4]
  },
  {
    "myNum" : [[1, 2], [3, 4], 5]
  }
]

The query

SELECT * FROM dbg

---

V1 response

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5
    ],
    [
      "doc_2",
      [
        3,
        4
      ]
    ],
    ...
  ],
  "size": 2,
  "status": 200
}

It is errorneous even though it represents data as is. It violates the second requirement. The schema in that response is also not applicable for all rows.

V2 response

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5
    ],
    [
      "doc_2",
      3
    ],
    [
      "doc_3",
      1
    ]
  ],
  "size": 2,
  "status": 200
}

Or as a table:

_doc	myNum
doc_1	5
doc_2	3
doc_3	1

This response is valid in terms of SQL, but loses the data.

[Yury-Fridlyand]

Option 1: flatten values

Flatten values as NESTED function does by adding extra rows.

Query:

SELECT * FROM dbg

Response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5
    ],
    [
      "doc_2",
      3
    ],
    [
      "doc_2",
      4
    ],
    [
      "doc_3",
      1
    ],
    [
      "doc_3",
      2
    ],
    [
      "doc_3",
      3
    ],
    [
      "doc_3",
      4
    ],
    [
      "doc_3",
      5
    ],
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum
doc_1	5
doc_2	3
doc_2	4
doc_3	1
doc_3	2
doc_3	3
doc_3	4
doc_3	5

Cons:

• This may add a significant amount of rows.
• Rows are added when user didn't request them.
• Unfortunately, grouping is lost unless extra info (as a column, for example) added to the response.
• This violates principle one row for one doc.
• Could be incompatible with other features, for example, pagination.

[Yury-Fridlyand]

Option 2: Expand values

Add extra columns to entire result set if a doc has a field with multiple values.

Query:

SELECT * FROM dbg

Response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    },
    {
      "name": "myNum[0]",
      "type": "long"
    },
    {
      "name": "myNum[1]",
      "type": "long"
    },
    {
      "name": "myNum[0][0]",
      "type": "long"
    },
    {
      "name": "myNum[0][1]",
      "type": "long"
    },
    {
      "name": "myNum[1][0]",
      "type": "long"
    },
    {
      "name": "myNum[1][1]",
      "type": "long"
    },
    {
      "name": "myNum[2]",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5,
      null,
      null,
      null,
      null,
      null,
      null,
      null,
    ],
    [
      "doc_2",
      null,
      3,
      4,
      null,
      null,
      null,
      null,
      null,
    ],
    [
      "doc_3",
      null,
      null,
      null,
      1,
      2,
      3,
      4,
      5
    ],
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum	myNum[0]	myNum[1]	myNum[0][0]	myNum[0][1]	myNum[1][0]	myNum[1][1]	myNum[2]
doc_1	5	null	null	null	null	null	null	null
doc_2	null	3	4	null	null	null	null	null
doc_3	null	null	null	1	2	3	4	5

Cons:

• This may add a significant amount of columns.
• Columns are added when user didn't request them.

[Yury-Fridlyand]

Option 3: provide value as a json

Add one extra column with a raw json, which contains the field.

Query:

SELECT * FROM dbg

Response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    },
    {
      "name": "myNum.json",
      "type": "string"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5,
      "{ \"myNum\": 5 }"
    ],
    [
      "doc_2",
      null,
      "{ \"myNum\": [3, 4] }"
    ],
    ,
    [
      "doc_3",
      null,
      "{ \"myNum\": [[1, 2], [3, 4], 5] }"
    ]
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum	myNum.json
doc_1	5	{ "myNum": 5 }
doc_2	null	{ "myNum": [3, 4] }
doc_3	null	{ "myNum": [[1, 2], [3, 4], 5] }

Cons:

• Extra column would be added to all fields.
• Extra column would be added those fields which have multiple values:
  • Requires sampling the entire response.
  • Response depends on data and may be changed.
  • Incompatible with pagination and probably, with some other features.

Procs:

• Behavior could be configurable and switchable by settings or query parameters/hints. See Option 5 and Option X below.

[Yury-Fridlyand]

Option 4: ignore not compatible values

Value [3, 4] can't be converted to a number, so should be skipped.
Don't hurry up to reject this option, see other options below.

Query:

SELECT * FROM dbg

Response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5
    ],
    [
      "doc_2",
      null
    ],
    [
      "doc_3",
      null
    ]
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum
doc_1	5
doc_2	null
doc_3	null

Cons:

• Some values are omitted.

Procs:

• Completely no ambiguity.

[Yury-Fridlyand]

Option 5: cast or convert

This implies Option 4 and Option 3, but allows user to use a function(s) to convert multi-value fields.

Query:

SELECT CAST(myNum AS JSON) AS `myNum.json`, myNum FROM dbg;

Response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    },
    {
      "name": "myNum.json",
      "type": "string"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5,
      "{ \"myNum\": 5 }"
    ],
    [
      "doc_2",
      null,
      "{ \"myNum\": [3, 4] }"
    ],
    ,
    [
      "doc_3",
      null,
      "{ \"myNum\": [[1, 2], [3, 4], 5] }"
    ]
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum	myNum.json
doc_1	5	{ "myNum": 5 }
doc_2	null	{ "myNum": [3, 4] }
doc_3	null	{ "myNum": [[1, 2], [3, 4], 5] }

Procs:

• No ambiguity.
• No cons from Option 3.

[Yury-Fridlyand]

Option 6: PartiQL

This implies Option 4, but allows user to use PartiQL syntax and define how to deal with multiple values.
A number of sub-options is possible here, syntax and behavior in each case should be dicsussed in further.

Query:

SELECT CASE WHEN (myNum IS ARRAY) THEN myNum[0]
       ELSE myNum END AS myNum
FROM dbg;

or

SELECT num AS myNum FROM dbg as d, d.myNum[0] as num

Example of the response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5
    ],
    [
      "doc_2",
      3
    ],
    ,
    [
      "doc_3",
      null
    ]
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum
doc_1	5
doc_2	3
doc_3	null

Cons:

• Complex implementation.

Procs:

• Allows users to twirl and represent their data as they want.
• Allows users to filter

[Yury-Fridlyand]

Option 7: painless script

This implies Option 4, but allows user to submit a painless script to process docs/fields. This script will be posted straight to the OpenSearch engine.

Query:

SELECT myNum, painless_script('...') ...

or

SELECT *, painless_script(myNum, '...') ...

or

{
  "query" : "SELECT * ...",
  "painless_script" : "..."
}

Response:

{
  "schema": [
    {
      "name": "_doc",
      "type": "string"
    },
    {
      "name": "myNum",
      "type": "long"
    },
    {
      "name": "myNum.scripted",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "doc_1",
      5,
      null
    ],
    [
      "doc_2",
      null,
      4
    ],
    ,
    [
      "doc_3",
      null,
      5
    ]
  ],
  "size": 2,
  "status": 200
}

Being converted to the table that reponse looks like:

_doc	myNum	myNum.scripted
doc_1	5	null
doc_2	null	4
doc_3	null	5

Cons:

• Potential security breach.
• Implementation could be complex.
• Script language is similar to javascript and could be inconvenient to use by SQL developers.
• Not-so-painless actually.

Procs:

• The user takes full responsibility of how to process the data.

[Yury-Fridlyand]

Option 8: error on multiple values

This is modification of Option 4, but once SQL engine meets a field with multiple values, like [3, 4], it returns a error to the user if no instruction given how to process multiple values.
This could be seamlessly combined with any Options 5 to 7, or with a query parameter/hint (see Option X) could be combined with all options.

Query:

SELECT * FROM dbg

Response:

{
  "error": {
    "reason": "Error occurred in SQL engine",
    "details": "...",
    "type": "SomeTypeOfException"
  },
  "status": 503
}

Cons

• May return a valid result set or an error on the same query, depending on the data in the index.

Procs

• Does not affect users who don't have multiple values.
• Clearly notifies a user and requests an action (changing request).

[Yury-Fridlyand]

Now the highlight of the program comes.

Option X: let user pick the option

This requires following steps:

1. Define a default option. This could be also configurable by plugin settings.
2. Define which options to support.
3. Define how user can pick an option per-query. Any (or all) could be implemented:
   1. As a function: SELECT flatten_values(myNum) ..., SELECT convert_to_json(myNum) ...
   2. As an engine hint: /*! flatten_values(myNum) */ SELECT * ..., SELECT /*! convert_to_json */ myNum ...
   3. As a request parameter: { "query": "SELECT * ... ", "flatten_values": "myNum, otherColumn" }, { "query": "SELECT myNum ... ", "convert_to_json": "*" }

[Yury-Fridlyand]

Please, feel free to update/edit, add options and comment them.

My choice is to implement (in order):

1. Option 4 or 8
2. Option 5
3. Option X
4. Options 1, 2 and 6 and maybe 3

[MaxKsyunz]

Thank you @Yury-Fridlyand for starting this discussion. I like this layout.

As to mutli-valued fields, from my perspective:

1. For SQL plugin to be trustworthy, it must return all the data requested.
2. For SQL plugin to be easy to use, the API user must be able to get the data in the shape it was indexed in.

If {"myNum" : [[1, 2], [3, 4], 5] } is what was indexed, then as application developer, I'd expect SELECT myNum FROM dbg to return [[1, 2], [3, 4], 5] in myNum field. Presumably that shape makes sense for my application.

If I'm using the REST endpoint, then I expect JSON response and dealing with this case should be straight-forward.

How to map [[1, 2], [3, 4], 5] as a value in JDBC (or ODBC) is a different question. Best I see right now is to return such values as user-defined types where we defined semantics.

The one thing I dislike about this is the schema could change based on the data returned but I don't see anything in the JDBC documentation that expects it to be the same.

This is where configurability could be helpful. If a client can say how to convert multi-valued field to a single value, then they can get a consistent schema from query to query with simple types.

[Yury-Fridlyand]

the schema could change based on the data returned

That violates first requirement I posted on the very top: a column K from a table N returned by any/all query should be always the same type, regardless of data returned or stored in the table.

1. User can re-create the table by changing the column type. That will be column K' from a table N' with the same rule applied.
2. User can do a convert/cast/transform, which would produce f(K) with the same rule applied.

[acarbonetto]

Suggestion is that we implement #5 first: #1300 (comment)
That way we have a work-around for any one interested in using data as arrays (rather than the current implementation in V2 that only supports non-array values).

[acarbonetto]

We can add support for arrays by including a meta tag in the mapping. Meta fields and properties can be added dynamically using the mapping API. See: PUT /<target>/_mapping endpoint. This will allow one to add field properties to a existing mapping.

For existing/new mappings, we could use the meta.asArray (RFC on naming) property as a trigger to determine if the field should be treated exclusively as an array overriding the existing V2 behaviour (which forces the data type to be a single element). Note that meta fields must be string settings - so a regular true value won't work here.

For example:

            "int0" : {
                "type" : "integer",
                "meta": {
                    "asArray": "true"
                }
            }

In PPL, the JSON array is output. We should consider an overriding meta property that would behave like V2 in all cases (which forces the data type to be a single element). We could use the same meta property with the false value to force behaviour.

For example:

            "int0" : {
                "type" : "integer",
                "meta": {
                    "asArray": "false"
                }
            }

Once multi-field support is available, we could have both options available to user, for example:

            "int0" : {
                "type" : "integer",
                "fields" : {
                    "array": {
                        "type": "integer",
                        "meta": {
                            "asArray": "true"
                        }
                    }
                 }
            }

[acarbonetto]

Note: in postgresql, the columns are strictly defined. We can use meta to strictly enforce the typing. It allows for multi-dimensional arrays too. For example:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

SELECT pay_by_quarter[3] FROM sal_emp;
 pay_by_quarter
----------------
          10000
          25000

Reference: https://www.postgresql.org/docs/current/arrays.html

[forestmvey]

Partiql also supports querying arrays using square parenthesis for arrays of literals.

{ 
    'root': {
        'array': << 
       [
            [1, 2], [3, 4], 5,
            'NA'
        ]
        >> 
    }
}

Query whole array:

PartiQL> select array from root;
==='
<<
  {
    'array': [
      [
        1,
        2
      ],
      [
        3,
        4
      ],
      5,
      'NA'
    ]
  }
>>
---

Query First Index Of Array:

PartiQL> SELECT array[0] FROM root;
==='
<<
  {
    '_1': [
      1,
      2
    ]
  }
>>
---

Query First Index of First Index Array:

PartiQL> SELECT array[0][0] FROM root;
==='
<<
  {
    '_1': 1
  }
>>
---

Query Third Index To Array:

PartiQL> SELECT array[2] FROM root;
==='
<<
  {
    '_1': 5
  }
>>
---

With this implementation in the V2 engine we can avoid a breaking change. Without the user specifying array indexing in their SQL query the current behaviour will be adhered to and the first value in the array will be returned. When the user uses square parenthesis the responsibility for that index to be valid falls on the user. Following are some examples and edge cases to show this implementation in the SQL plugin.

Arrays of Objects

"accounts": [{"id": 1}, {"id": 2}]

Query:

SELECT accounts[0].id FROM people;

Response:
1

---

Example query that fails due to not indexing array correctly and missing value is returned.

Query:

SELECT accounts.id[0] FROM people;

Response:
null

---

Example query that fails due to indexing array out of bounds and missing value is returned.

Query:

SELECT accounts[2].id FROM people;

Response:
null

---

Example query that returns whole array.

Query:

SELECT accounts[:] FROM people;

Response:
[{"id": 1}, {"id": 2}]

---

Example query that returns whole array without specifying ending parenthesis. Whole array support is returned by default when the user uses square parenthesis in any part of the paths of selected field.

Query:

SELECT accounts[0].id FROM people;

Response:
{"id": 1}

---

Arrays of Objects with inner arrays

"accounts": [{"id": [[1, 1], 2]}, {"id": [3, 4]}]

An example where we return the whole resulting array implicitly:

Query:

SELECT accounts[0].id FROM people;

Response:
[[1, 1], 2]

---

An example where we return the whole resulting array implicitly:

Query:

SELECT accounts[0].id[0][0] FROM people;

Response:
1

---

Limitations:
TBD