# SQL plugin only showing first 3 array elements

**URL:** <https://forum.opensearch.org/t/sql-plugin-only-showing-first-3-array-elements/17177>\
**Category:** OpenSearch\
**Tags:** discuss\
**Created:** [December 20, 2023, 9:14am UTC](https://forum.opensearch.org/t/sql-plugin-only-showing-first-3-array-elements/17177 "2023-12-20T09:14:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![gerardq](https://avatars.discourse-cdn.com/v4/letter/g/c5a1d2/32.png) [@gerardq](https://forum.opensearch.org/u/gerardq)\
**Post date:** [December 20, 2023, 9:14am UTC](https://forum.opensearch.org/t/sql-plugin-only-showing-first-3-array-elements/17177/1 "2023-12-20T09:14:04Z")

</div>

**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](https://opensearch.org/docs/2.11/search-plugins/sql/sql/partiql).  
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  
}

---

<div class="post-metadata">

**Author:** ![gerardq](https://avatars.discourse-cdn.com/v4/letter/g/c5a1d2/32.png) [@gerardq](https://forum.opensearch.org/u/gerardq)\
**Post date:** [December 27, 2023, 12:05pm UTC](https://forum.opensearch.org/t/sql-plugin-only-showing-first-3-array-elements/17177/2 "2023-12-27T12:05:27Z")

</div>

The query plan in the [example page](https://opensearch.org/docs/2.11/search-plugins/sql/sql/partiql) show the following. I think this is the reason why it only returns 3 array elements. How can I increase theiis number?

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

```