This is an automated email from the ASF dual-hosted git repository. cgivre pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/drill-site.git
The following commit(s) were added to refs/heads/master by this push: new d04357f72 Create 050-collect-to-list.md d04357f72 is described below commit d04357f7263eeed9d433a0f33374e81c4bfca639 Author: Charles S. Givre <cgi...@apache.org> AuthorDate: Thu Mar 16 10:39:40 2023 -0400 Create 050-collect-to-list.md --- .../nested-data-functions/050-collect-to-list.md | 48 ++++++++++++++++++++++ 1 file changed, 48 insertions(+) diff --git a/_docs/en/sql-reference/nested-data-functions/050-collect-to-list.md b/_docs/en/sql-reference/nested-data-functions/050-collect-to-list.md new file mode 100644 index 000000000..6a02c6618 --- /dev/null +++ b/_docs/en/sql-reference/nested-data-functions/050-collect-to-list.md @@ -0,0 +1,48 @@ +--- +title: "COLLECT_LIST" +slug: "List Creation Functions" +parent: "Nested Data Functions" +--- +Drill has several functions which can create lists/arrays or maps from your data. + +## COLLECT_LIST +This function takes the values of an expression and converts them into a map + +### Syntax + + COLLECT_LIST(<key> <expr>) + +Where <key> is a literal containing a map key and <exper> is an expression. The function can contain many key/value pairs. + +### Example + + apache drill> SELECT collect_list('n_nationkey', n_nationkey, 'n_name', n_name) as newlist + FROM (SELECT * from cp.`tpch/nation.parquet` limit 2) group by 'a'; + +-------------------------------------------------------------------------------+ + | newlist | + +-------------------------------------------------------------------------------+ + | [{"n_nationkey":0,"n_name":"ALGERIA"},{"n_nationkey":1,"n_name":"ARGENTINA"}] | + +-------------------------------------------------------------------------------+ + 1 row selected (0.186 seconds) + + + +## COLLECT_TO_LIST_VARCHAR +This function takes the values of an expression and converts it to a list/array. + +### Syntax + + COLLECT_TO_LIST_VARCHAR(*column*) + +Where *column* is a column that will be converted to a list (array). + +### Example +The following query takes the `position_title` field and converts that field into a Drill list. This function does not remove duplicates, so if you use this on a column +containing non-unique values, it will simply convert all those values into a list. + + apache drill> SELECT collect_to_list_varchar(position_title) FROM (SELECT * FROM cp.`employee.json` LIMIT 5); + +----------------------------------------------------------------------------------+ + | EXPR$0 | + +----------------------------------------------------------------------------------+ + | ["President","VP Country Manager","VP Country Manager","VP Country Manager","VP Information Systems"] | + +----------------------------------------------------------------------------------+