---
title: "Query"
language: "en"
type: "Symbol"
summary: "Query[operator1, operator2, ...] represents a query that can be applied to a Dataset object, in which the successive operatori are applied at successively deeper levels."
keywords: 
- query data
- select from
- data model
- dataframe
- data frame
- hierarchical data
- hierarchical query
- SQL
- noSQL
- MongoDb
- data cube
- OLAP cube
canonical_url: "https://reference.wolfram.com/language/ref/Query.html"
source: "Wolfram Language Documentation"
related_guides: 
  - 
    title: "Computation with Structured Datasets"
    link: "https://reference.wolfram.com/language/guide/ComputationWithStructuredDatasets.en.md"
related_workflows: 
  - 
    title: "Analyze a Computable Dataset"
    link: "https://reference.wolfram.com/language/workflow/AnalyzeAComputableDataset.en.md"
  - 
    title: "Select Elements in a Dataset"
    link: "https://reference.wolfram.com/language/workflow/SelectElementsInADataset.en.md"
  - 
    title: "Extract Columns in a Dataset"
    link: "https://reference.wolfram.com/language/workflow/ExtractColumnsInADataset.en.md"
related_functions: 
  - 
    title: "Dataset"
    link: "https://reference.wolfram.com/language/ref/Dataset.en.md"
  - 
    title: "Part"
    link: "https://reference.wolfram.com/language/ref/Part.en.md"
  - 
    title: "FailureAction"
    link: "https://reference.wolfram.com/language/ref/FailureAction.en.md"
  - 
    title: "MissingBehavior"
    link: "https://reference.wolfram.com/language/ref/MissingBehavior.en.md"
  - 
    title: "PartBehavior"
    link: "https://reference.wolfram.com/language/ref/PartBehavior.en.md"
---
# Query

Query[operator1, operator2, …] represents a query that can be applied to a Dataset object, in which the successive operatori are applied at successively deeper levels.

## Details and Options

* ``Query`` can perform complex filtering, reorganization, and aggregation of arbitrary rectangular and hierarchical data, typically returning further rectangular or hierarchical data.

* ``Query`` can operate on a ``Dataset`` object or an arbitrary nested expression consisting of lists and associations.

* ``Query`` can be seen as a generalization of ``Part`` that allows computations to be performed in the process of traversing an expression or dataset.

* The ``operatori`` can be any of the following forms:

|                                 |                       |
| ------------------------------- | --------------------- |
| All, *i*, i ;; j, "key", Key[…] | part operators        |
| Select[…], MaximalBy[…], …      | filtering operators   |
| Counts, Total, Mean, Max, …     | aggregation operators |
| Query[…], {op1, op2, …}, …      | subquery operators    |
| Function[…], f                  | arbitrary functions   |

* In ``Query[operator1, …][expr]``, the ``operatori`` are applied at successively deeper levels in ``expr``, but any given one may be applied either while "descending" into ``expr`` or while "ascending" out of it. In general, part specifications and filtering operators are "descending" operators. Aggregation operators, subquery operators, and arbitrary functions are "ascending" operators. ``Query[][expr]`` returns ``expr``.

* A "descending" operator is applied to corresponding parts of the original dataset, before subsequent operators are applied at deeper levels. Descending operators have the feature that they do not change the structure of deeper levels of the data when applied at a certain level. This ensures that subsequent operators will encounter subexpressions whose structure is identical to the corresponding levels of the original dataset. The simplest descending operator is ``All``, which selects all parts at a given level and therefore leaves the structure of the data at that level unchanged.

* An "ascending" operator is applied after all subsequent operators have been applied to deeper levels. Whereas descending operators correspond to the levels of the original data, ascending operators correspond to the levels of the result. Unlike descending operators, ascending operators do not necessarily preserve the structure of the data on which they operate. Unless an operator is specifically recognized to be descending, it is assumed to be ascending.

* The "descending" part operators specify which elements to take at a level before applying any subsequent operators to deeper levels:

|                   |                                                                            |
| ----------------- | -------------------------------------------------------------------------- |
| All               | apply subsequent operators to each part of a list or association           |
| i ;; j            | take parts i through j and apply subsequent operators to each part         |
| i                 | take only part i and apply subsequent operators to it                      |
| "key", Key[key]   | take value of key in an association and apply subsequent operators to it   |
| Keys              | take keys of an association and apply subsequent operators to each key     |
| Values            | take values of an association and apply subsequent operators to each value |
| {part1, part2, …} | take given parts and apply subsequent operators to each part               |

* The "descending" filtering operators specify how to rearrange or filter elements at a level before applying subsequent operators to deeper levels:

|                                  |                                                                    |
| -------------------------------- | ------------------------------------------------------------------ |
| Select[test]                     | take only those parts of a list or association that satisfy test   |
| SelectFirst[test]                | take the first part that satisfies test                            |
| KeySelect[test]                  | take those parts of an association whose keys satisfy test         |
| MaximalBy[crit], MinimalBy[crit] | take the parts for which criteria crit is minimal or maximal       |
| SortBy[crit]                     | sort parts in order of crit                                        |
| KeySortBy[crit]                  | sort parts of an association based on their keys, in order of crit |
| DeleteDuplicatesBy[crit]         | take parts that are unique according to crit                       |
| DeleteMissing                    | drop elements with head Missing                                    |

* The "ascending" aggregation operators combine or summarize the results of applying subsequent operators to deeper levels:

|                           |                                                                      |
| ------------------------- | -------------------------------------------------------------------- |
| Total                     | total all quantities in the result                                   |
| Min, Max                  | give minimum, maximum quantity in the result                         |
| Mean, Median, Quantile, … | give statistical summary of the result                               |
| Histogram, ListPlot, …    | calculate a visualization on the result                              |
| Merge[f]                  | merge common keys of associations in the result using function f     |
| Catenate                  | catenate the elements of lists or associations together              |
| Counts                    | give association that counts occurrences of values in the result     |
| CountsBy[crit]            | give association that counts occurrences of values according to crit |
| CountDistinct             | give number of distinct values in the result                         |
| CountDistinctBy[crit]     | give number of distinct values in the result according to crit       |

* The "ascending" subquery operators perform a subquery after applying subsequent operators to deeper levels:

|                                 |                                                                                             |
| ------------------------------- | ------------------------------------------------------------------------------------------- |
| Query[…]                        | perform a subquery on the result                                                            |
| {op1, op2, …}                   | apply multiple operators at once to the result, yielding a list                             |
| op1 /* op2 /* …                 | apply op1, then apply op2 at the same level, etc.                                           |
| <\|key1 -> op1, key2 -> op2, …\|> | apply multiple operators at once to the result, yielding an association with the given keys |
| {key1 -> op1, key2 -> op2, …}     | apply different operators to specific parts in the result                                   |

* When one or more descending operators are composed with one or more ascending operators (e.g. ``desc /* asc``), the descending part will be applied, then subsequent operators will be applied to deeper levels, and lastly the ascending part will be applied to the result.

* The special descending operator ``GroupBy[spec]`` will introduce a new association at the level at which it appears and can be inserted or removed from an existing query without affecting the behavior of other operators.

* The syntax ``GroupBy["string"]`` can be used as a synonym for ``GroupBy[Key["string"]]``. The same syntax is also available for ``SortBy``, ``CountsBy``, ``MaximalBy``, ``MinimalBy``, and ``DeleteDuplicatesBy``.

* The following options can be given:

|                  |           |                                           |
| ---------------- | --------- | ----------------------------------------- |
| FailureAction    | "Abort"   | how to handle operators that fail         |
| MissingBehavior  | Automatic | how to treat operations involving Missing |
| PartBehavior     | Automatic | how to resolve missing parts              |

* Possible values for ``FailureAction`` include:

|               |                                                                              |
| ------------- | ---------------------------------------------------------------------------- |
| None          | ignore all messages and failures                                             |
| "Abort"       | abort the entire query when a message is encountered (default)               |
| "Drop"        | drop the results of operations that issue messages                           |
| "Encapsulate" | wrap operations that issue messages in a Failure object                      |
| "Replace"     | replace the results of operations that issue messages with Missing["Failed"] |

* The option ``MissingBehavior`` describes how numeric and other functions should treat expressions with head ``Missing``. Possible values include:

|           |                                            |
| --------- | ------------------------------------------ |
| None      | use ordinary behavior of Missing           |
| Automatic | invoke special rules for Mean, Total, etc. |

* The option ``PartBehavior`` describes how operators that refer to nonexistent parts are evaluated. Possible values include:

|           |                                                          |
| --------- | -------------------------------------------------------- |
| None      | use ordinary behavior of Part                            |
| Automatic | invoke special rules for invalid i ;; j, missing i, etc. |

---

## Examples (6)

### Basic Examples (1)

Construct tabular data on which to perform queries:

```wl
In[1]:=
data = {
	<|"a" -> 1, "b" -> "x", "c" -> {1}|>, 
	<|"a" -> 2, "b" -> "y", "c" -> {2, 3}|>, 
	<|"a" -> 3, "b" -> "z", "c" -> {3}|>, 
	<|"a" -> 4, "b" -> "x", "c" -> {4, 5}|>, 
	<|"a" -> 5, "b" -> "y", "c" -> {5, 6, 7}|>, 
	<|"a" -> 6, "b" -> "z", "c" -> {}|>};
```

Take a set of rows:

```wl
In[2]:= Query[1 ;; 3] @ data

Out[2]= {<|"a" -> 1, "b" -> "x", "c" -> {1}|>, <|"a" -> 2, "b" -> "y", "c" -> {2, 3}|>, <|"a" -> 3, "b" -> "z", "c" -> {3}|>}
```

Take a specific row:

```wl
In[3]:= Query[2] @ data

Out[3]= <|"a" -> 2, "b" -> "y", "c" -> {2, 3}|>
```

Take a specific element from a specific row:

```wl
In[4]:= Query[3, "a"] @ data

Out[4]= 3

In[5]:= Query[4, "b"] @ data

Out[5]= "x"

In[6]:= Query[5, "c"] @ data

Out[6]= {5, 6, 7}
```

Take the contents of a specific column:

```wl
In[7]:= Query[All, "a"] @ data

Out[7]= {1, 2, 3, 4, 5, 6}

In[8]:= Query[All, "b"] @ data

Out[8]= {"x", "y", "z", "x", "y", "z"}

In[9]:= Query[All, "c"] @ data

Out[9]= {{1}, {2, 3}, {3}, {4, 5}, {5, 6, 7}, {}}
```

Take a specific part within a column:

```wl
In[10]:= Query[All, "c", 1] @ data

Out[10]= {1, 2, 3, 4, 5, Missing["PartAbsent", 1]}
```

Take a subset of the rows and columns:

```wl
In[11]:= Query[1 ;; 4, {"a", "b"}] @ data

Out[11]= {<|"a" -> 1, "b" -> "x"|>, <|"a" -> 2, "b" -> "y"|>, <|"a" -> 3, "b" -> "z"|>, <|"a" -> 4, "b" -> "x"|>}
```

Apply a function to a specific column:

```wl
In[12]:= Query[Total, "a"] @ data

Out[12]= 21

In[13]:= Query[StringJoin, "b"] @ data

Out[13]= "xyzxyz"

In[14]:= Query[Catenate, "c"] @ data

Out[14]= {1, 2, 3, 3, 4, 5, 5, 6, 7}
```

Partition the data based on a column, applying the rest of the query to each group:

```wl
In[15]:= Query[GroupBy["b"], Catenate, "c"] @ data

Out[15]= <|"x" -> {1, 4, 5}, "y" -> {2, 3, 5, 6, 7}, "z" -> {3}|>
```

Apply a function to each row:

```wl
In[16]:= Query[All, Reverse] @ data

Out[16]= {<|"c" -> {1}, "b" -> "x", "a" -> 1|>, <|"c" -> {2, 3}, "b" -> "y", "a" -> 2|>, <|"c" -> {3}, "b" -> "z", "a" -> 3|>, <|"c" -> {4, 5}, "b" -> "x", "a" -> 4|>, <|"c" -> {5, 6, 7}, "b" -> "y", "a" -> 5|>, <|"c" -> {}, "b" -> "z", "a" -> 6|>}
```

Apply a function both to each row and to the entire result:

```wl
In[17]:= Query[Reverse, Reverse] @ data

Out[17]= {<|"c" -> {}, "b" -> "z", "a" -> 6|>, <|"c" -> {5, 6, 7}, "b" -> "y", "a" -> 5|>, <|"c" -> {4, 5}, "b" -> "x", "a" -> 4|>, <|"c" -> {3}, "b" -> "z", "a" -> 3|>, <|"c" -> {2, 3}, "b" -> "y", "a" -> 2|>, <|"c" -> {1}, "b" -> "x", "a" -> 1|>}
```

Apply a function ``f`` to every element in every row:

```wl
In[18]:= Query[All, All, f] @ data

Out[18]= {<|"a" -> f[1], "b" -> f["x"], "c" -> f[{1}]|>, <|"a" -> f[2], "b" -> f["y"], "c" -> f[{2, 3}]|>, <|"a" -> f[3], "b" -> f["z"], "c" -> f[{3}]|>, <|"a" -> f[4], "b" -> f["x"], "c" -> f[{4, 5}]|>, <|"a" -> f[5], "b" -> f["y"], "c" -> f[{5, 6, 7}]|>, <|"a" -> f[6], "b" -> f["z"], "c" -> f[{}]|>}
```

Apply specific operators to each column independently:

```wl
In[19]:= Query[All, {"a" -> f, "b" -> g, "c" -> h}] @ data

Out[19]= {<|"a" -> f[1], "b" -> g["x"], "c" -> h[{1}]|>, <|"a" -> f[2], "b" -> g["y"], "c" -> h[{2, 3}]|>, <|"a" -> f[3], "b" -> g["z"], "c" -> h[{3}]|>, <|"a" -> f[4], "b" -> g["x"], "c" -> h[{4, 5}]|>, <|"a" -> f[5], "b" -> g["y"], "c" -> h[{5, 6, 7}]|>, <|"a" -> f[6], "b" -> g["z"], "c" -> h[{}]|>}
```

Construct a new table by specifying operators that will compute each column:

```wl
In[20]:= Query[All, "c" /* <|"ctotal" -> Total, "clength" -> Length|>] @ data

Out[20]= {<|"ctotal" -> 1, "clength" -> 1|>, <|"ctotal" -> 5, "clength" -> 2|>, <|"ctotal" -> 3, "clength" -> 1|>, <|"ctotal" -> 9, "clength" -> 2|>, <|"ctotal" -> 18, "clength" -> 3|>, <|"ctotal" -> 0, "clength" -> 0|>}
```

Use the same technique to rename columns:

```wl
In[21]:= Query[All, <|"A" -> "a", "B" -> "b", "C" -> "c"|>] @ data

Out[21]= {<|"A" -> 1, "B" -> "x", "C" -> {1}|>, <|"A" -> 2, "B" -> "y", "C" -> {2, 3}|>, <|"A" -> 3, "B" -> "z", "C" -> {3}|>, <|"A" -> 4, "B" -> "x", "C" -> {4, 5}|>, <|"A" -> 5, "B" -> "y", "C" -> {5, 6, 7}|>, <|"A" -> 6, "B" -> "z", "C" -> {}|>}
```

Select specific rows based on a criterion:

```wl
In[22]:= Query[Select[#a < 5&]] @ data

Out[22]= {<|"a" -> 1, "b" -> "x", "c" -> {1}|>, <|"a" -> 2, "b" -> "y", "c" -> {2, 3}|>, <|"a" -> 3, "b" -> "z", "c" -> {3}|>, <|"a" -> 4, "b" -> "x", "c" -> {4, 5}|>}
```

Take the contents of a column after selecting the rows:

```wl
In[23]:= Query[Select[#a < 5&], "b"] @ data

Out[23]= {"x", "y", "z", "x"}
```

Take a subset of the available columns after selecting the rows:

```wl
In[24]:= Query[Select[#a < 5&], {"b", "c"}] @ data

Out[24]= {<|"b" -> "x", "c" -> {1}|>, <|"b" -> "y", "c" -> {2, 3}|>, <|"b" -> "z", "c" -> {3}|>, <|"b" -> "x", "c" -> {4, 5}|>}
```

Take a value from the first row satisfying a criterion:

```wl
In[25]:= Query[SelectFirst[#a > 5&], "b"] @ data

Out[25]= "z"
```

Sort the rows by a criterion:

```wl
In[26]:= Query[SortBy[Length[#c]&]] @ data

Out[26]= {<|"a" -> 6, "b" -> "z", "c" -> {}|>, <|"a" -> 1, "b" -> "x", "c" -> {1}|>, <|"a" -> 3, "b" -> "z", "c" -> {3}|>, <|"a" -> 2, "b" -> "y", "c" -> {2, 3}|>, <|"a" -> 4, "b" -> "x", "c" -> {4, 5}|>, <|"a" -> 5, "b" -> "y", "c" -> {5, 6, 7}|>}
```

Take the rows that give the maximal value of a scoring function:

```wl
In[27]:= Query[MaximalBy[Length[#c]&]] @ data

Out[27]= {<|"a" -> 5, "b" -> "y", "c" -> {5, 6, 7}|>}
```

Delete rows that duplicate a criterion:

```wl
In[28]:= Query[DeleteDuplicatesBy["b"]] @ data

Out[28]= {<|"a" -> 1, "b" -> "x", "c" -> {1}|>, <|"a" -> 2, "b" -> "y", "c" -> {2, 3}|>, <|"a" -> 3, "b" -> "z", "c" -> {3}|>}
```

Compose an ascending and a descending operator to aggregate values of a column after filtering the rows:

```wl
In[29]:= Query[Select[#b == "z"&] /* Total, "a"]  @ data

Out[29]= 9
```

Do the same thing by composing ``Total`` with the ``Query`` :

```wl
In[30]:= (Query[Select[#b == "z"&], "a"]  /* Total) @ data

Out[30]= 9
```

### Options (4)

#### FailureAction (2)

Create sample data:

```wl
In[1]:= list = Range[-2, 2]

Out[1]= {-2, -1, 0, 1, 2}
```

The setting ``FailureAction -> None`` takes no special action on the result:

```wl
In[2]:= Query[All, 1 / #&, FailureAction -> None][list]
```

Power::infy: Infinite expression 1/0 encountered.

```wl
Out[2]= {-(1/2), -1, ComplexInfinity, 1, (1/2)}
```

The setting ``FailureAction -> "Encapsulate"`` encapsulates failed results in a ``Failure`` object:

```wl
In[3]:= Query[All, 1 / #&, FailureAction -> "Encapsulate"][list]

Out[3]=
{-(1/2), -1, Failure[Power, Association["MessageTemplate" :> Power::infy, 
  "MessageParameters" -> {HoldForm[0^(-1)]}]], 1, (1/2)}
```

The setting ``FailureAction -> "Replace"`` replaces failed results with a placeholder. For the function ``Query``, this placeholder is a ``Missing`` object:

```wl
In[4]:= Query[All, 1 / #&, FailureAction -> "Replace"][list]

Out[4]= {-(1/2), -1, Missing["Failed"], 1, (1/2)}
```

The setting ``FailureAction -> "Drop"`` causes failed results to be dropped from the final expression:

```wl
In[5]:= Query[All, 1 / #&, FailureAction -> "Drop"][list]

Out[5]= {-(1/2), -1, 1, (1/2)}
```

The setting ``FailureAction -> "Abort"`` aborts the computation, returning a ``Failure`` object instead:

```wl
In[6]:= Query[All, 1 / #&, FailureAction -> "Abort"][list]

Out[6]=
Failure[Power, Association["MessageTemplate" :> Power::infy, 
  "MessageParameters" -> {HoldForm[0^(-1)]}]]
```

---

The setting ``FailureAction -> {"Drop", f}`` can be used to perform an action before dropping a failed result:

```wl
In[1]:= list = Range[-2, 2]

Out[1]= {-2, -1, 0, 1, 2}

In[2]:= Query[All, 1 / #&, FailureAction -> {"Drop", Print}][list]

During evaluation of In[2]:=
Failure[Power, Association["MessageTemplate" :> Power::infy, 
  "MessageParameters" -> {HoldForm[0^(-1)]}]]

Out[2]= {-(1/2), -1, 1, (1/2)}
```

The setting ``FailureAction -> {"Replace", f}`` can be used to specify a replacement that is a function of the failure:

```wl
In[3]:= Query[All, 1 / #&, FailureAction -> {"Replace", #["StyledMessage"]&}][list]

Out[3]=
{-(1/2), -1,                     1
Infinite expression - encountered.
                    0, 1, (1/2)}
```

#### MissingBehavior (1)

The default option value ``MissingBehavior -> Automatic`` applies special rules to operators that encounter ``Missing`` :

```wl
In[1]:= Query[Total] @ {1, 2, 3, Missing[]}

Out[1]= 6
```

Use ``MissingBehavior -> None`` to specify the ordinary behavior of ``Missing`` for all operators:

```wl
In[2]:= Query[Total, MissingBehavior -> None] @ {1, 2, 3, Missing[]}

Out[2]= 6 + Missing[]
```

#### PartBehavior (1)

The default option value ``PartBehavior -> Automatic`` invokes special behavior for operators that would otherwise fail:

```wl
In[1]:= Query[1 ;; 5] @ {1, 2, 3}

Out[1]= {1, 2, 3}
```

Using ``PartBehavior -> None`` specifies that the ordinary behavior of ``Part`` should be used:

```wl
In[2]:= Query[1 ;; 5, PartBehavior -> None] @ {1, 2, 3}

Out[2]=
Failure[Part, Association["MessageTemplate" :> Part::take, 
  "MessageParameters" :> {HoldForm[1], HoldForm[5], HoldForm[{1, 2, 3}]}]]
```

### Properties & Relations (1)

``Query`` is the operator form of the query language supported by ``Dataset`` :

```wl
In[1]:= Query["b", Total] @ <|"a" -> {1, 2}, "b" -> {3, 4}|>

Out[1]= 7

In[2]:= Dataset[<|"a" -> {1, 2}, "b" -> {3, 4}|>]["b", Total]

Out[2]= 7
```

Before being applied, ``Query`` expressions are "compiled" into ordinary compositions of ordinary Wolfram Language functions and their operator forms. To see the compiled form of a ``Query``, use ``Normal`` :

```wl
In[3]:= Query[All, f]//Normal

Out[3]= Map[f]

In[4]:= Query[f, g]//Normal

Out[4]= Map[g] /* f

In[5]:= Query[GroupBy["a"], Total]//Normal

Out[5]= GroupBy[Key["a"]] /* Map[Total]
```

## See Also

* [`Dataset`](https://reference.wolfram.com/language/ref/Dataset.en.md)
* [`Part`](https://reference.wolfram.com/language/ref/Part.en.md)
* [`FailureAction`](https://reference.wolfram.com/language/ref/FailureAction.en.md)
* [`MissingBehavior`](https://reference.wolfram.com/language/ref/MissingBehavior.en.md)
* [`PartBehavior`](https://reference.wolfram.com/language/ref/PartBehavior.en.md)

## Related Guides

* [Computation with Structured Datasets](https://reference.wolfram.com/language/guide/ComputationWithStructuredDatasets.en.md)

## Related Workflows

* [Analyze a Computable Dataset](https://reference.wolfram.com/language/workflow/AnalyzeAComputableDataset.en.md)
* [Select Elements in a Dataset](https://reference.wolfram.com/language/workflow/SelectElementsInADataset.en.md)
* [Extract Columns in a Dataset](https://reference.wolfram.com/language/workflow/ExtractColumnsInADataset.en.md)

## History

* [Introduced in 2014 (10.0)](https://reference.wolfram.com/language/guide/SummaryOfNewFeaturesIn100.en.md)