SQL plugin only showing first 3 array elements

OpenSearch
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):
2.11.1, CentOS 7

Describe the issue:
I want to get the array elements out of a json as a recordset using SQL plugin. I follow the example as in here.
After posting the 3 rows, initially it didn’t work. It turned out in the mapping the type needs to be set to NESTED for any array. Then I could run the first example query that list all the security related projects. But if I add another row with 10 array elements the query only returns the first 3 elements for each row. See below.

Configuration:
default installation on a sandbox VM

Relevant Logs or Screenshots:

(I had to remove the exact url’s from the commands because as a new user I am not allowed to put in more than 2, but it is localhost on port 9200).

/* add another row with 10 security projects: */
curl -k -XPOST -H ‘Content-Type: application/json’ -u ‘admin:admin’ [url]/employees_nested2/_doc/4 -d’
{“id”:7,“name”:“John Doe”,“title”:“Software Eng 2”,“projects”:[{“name”:“security_1”,“started_year”:1998},{“name”:“security_2”,“started_year”:1998},{“name”:“security_3”,“started_year”:1998},{“name”:“security_4”,“started_year”:1998},{“name”:“security_5”,“started_year”:1998},{“name”:“security_6”,“started_year”:1998},{“name”:“security_7”,“started_year”:1998},{“name”:“security_8”,“started_year”:1998},{“name”:“security_9”,“started_year”:1998},{“name”:“security_10”,“started_year”:1998}]}’

/* run the example query: */
curl -k -XPOST -H ‘Content-Type: application/json’ -u ‘admin:admin’ [url]/_plugins/_sql -d’
{“query”:“SELECT e.name AS employeeName,p.name AS projectName FROM employees_nested2 AS e, e.projects AS p WHERE p.name LIKE ‘'’%security%‘'’”}’
{
“schema”: [
{
“name”: “name”,
“alias”: “employeeName”,
“type”: “text”
},
{
“name”: “projects.name”,
“alias”: “projectName”,
“type”: “text”
}
],
“total”: 7,
“datarows”: [
[
“Bob Smith”,
“SQL security”
],
[
“Bob Smith”,
“OpenSearch security”
],
[
“Jane Smith”,
“SQL security”
],
[
“Jane Smith”,
“Hello security”
],
[
“John Doe”,
“security_1”
],
[
“John Doe”,
“security_2”
],
[
“John Doe”,
“security_3”
]
],
“size”: 7,
“status”: 200
}

/* Note the query can find specific project that is not in above resultset so the index seems fine: */

curl -k -XPOST -H ‘Content-Type: application/json’ -u ‘admin:admin’ [url]/_plugins/_sql -d’
{“query”:“SELECT e.name AS employeeName,p.name AS projectName FROM employees_nested2 AS e, e.projects AS p WHERE p.name = ‘'‘security_10’'’”}’
{
“schema”: [
{
“name”: “name”,
“alias”: “employeeName”,
“type”: “text”
},
{
“name”: “projects.name”,
“alias”: “projectName”,
“type”: “text”
}
],
“total”: 1,
“datarows”: [[
“John Doe”,
“security_10”
]],
“size”: 1,
“status”: 200
}

2

The query plan in the example page show the following. I think this is the reason why it only returns 3 array elements. How can I increase theiis number?

 "inner_hits" : {
                    "ignore_unmapped" : false,
                    "from" : 0,
                    **"size" : 3,**