project-by-names operator

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Select and reorder a subset of columns from the input table by their names, dynamic arrays, or name patterns.

Syntax

T | project-by-names ColumnSpecifier [, ...]

Learn more about syntax conventions.

Parameters

Name Type Required Description
T string ✔️ The tabular input from which to remove columns.
ColumnSpecifier string ✔️ The name of the column, dynamic array of column names, or column wildcard pattern to keep and reorder.

Note

  • Columns in the result are ordered based on the sequence in which they're specified or matched.
  • Column names that don't match any existing column are safely ignored.
  • Wildcard characters (*) can be used to match multiple columns by name pattern.
  • ColumnSpecifier can include the result of a column_names_of(Table) expression, which returns a dynamic array of column names from the specified table.
  • Subqueries or scalar expressions like toscalar() aren't supported in the ColumnSpecifier parameter.
  • For related functionality:

Returns

A table containing only the columns specified in the ColumnSpecifier parameter, in the specified order. All unspecified columns are excluded from the result.

Examples

The following examples demonstrate how to use the project-by-names operator to select and reorder columns from a table. The input table has four columns: Name, Age, City, and Country.

Keep and reorder specific columns

Use the operator to keep only the Name and City columns.

datatable(Name:string, Age:int, City:string, Country:string) 
['Peter', 39, 'New York', 'USA']
| project-by-names "Name", "City"

Output

Name City
Peter New York

Keep columns using a dynamic array

Use the operator to select columns from the input table by providing a dynamic array of column names.

datatable(Name:string, Age:int, City:string, Country:string) 
['Peter', 39, 'New York', 'USA']
| project-by-names dynamic(["Name", "Country"])

Output

Name Country
Peter USA

Keep columns using column name patterns

Use the operator to select columns from the input table starting with C.

datatable(Name:string, Age:int, City:string, Country:string) 
['Peter', 39, 'New York', 'USA']
| project-by-names "C*"

Output

City Country
New York USA

Lookup and keeping lookup columns using a dynamic array

Use the operator to keep columns from a table after performing a lookup, specifying which columns to retain using a dynamic array.

let LookupTable = 
    datatable(Name:string, Age:int, City:string, Country:string)
    ['Peter', 39, 'New York', 'USA']; 
let LookupColumns = (Source:(Name:string), lookup_columns: dynamic) { 
    Source 
    | lookup LookupTable on Name
    | project-by-names column_names_of(Source), lookup_columns
};
datatable(Name:string, Data:string)
['Peter', 'Source-data']
| invoke LookupColumns(dynamic(['Country']))

Output

Name Data Country
Peter Source-data USA

Ignore nonexisting columns

Use the operator to keep columns from a table after performing a lookup, specifying which columns to retain using a dynamic array that includes a nonexisting column name.

let LookupTable = 
    datatable(Name:string, Age:int, City:string, Country:string)
    ['Peter', 39, 'New York', 'USA']; 
let LookupColumns = (Source:(Name:string), lookup_columns: dynamic) { 
    Source 
    | lookup LookupTable on Name
    | project-by-names column_names_of(Source), lookup_columns
};
datatable(Name:string, Data:string)
['Peter', 'Source-data']
| invoke LookupColumns(dynamic(['Country', 'NonExistent']))

Output

Name Data Country
Peter Source-data USA

Related content

  • Last updated on