Problem on using SELECT in LiteDB: only one item returned

Question

I'm trying to use LiteDB in a Powershell application, no problem to create a table from a JSON file:

$db = [LiteDB.LiteDatabase]::new("ITQuote.db")
$Collection = $db.GetCollection("Quotes",[LiteDB.BsonAutoId]::Int64)
$file = "InforCitazioni.json"
$json = (New-Object System.Net.WebClient).DownloadString($file)
$data = $json | ConvertFrom-Json
foreach ($d in $data ) {
    $BsonDoc = [LiteDB.BsonDocument]::new()
    $BsonDoc["Author"] = $d.Autore
    $BsonDoc["Where"] = $d.Dove
    $BsonDoc["Quote"] = $d.Citazione
    $Collection.Insert($BsonDoc)
}

Instead I have two problems using SQL:

$db.Execute("Select `$ FROM Quotes WHERE `$.Author = 'Alan Perlis'") | foreach {
     [Console]::WriteLine($_["Quote"].RawValue)
}

The above statement show only the first of eight quotes of Alan Perlis. The second problem is an error generated when the statemt below is executed:

$db.Execute("Select Author FROM Quotes GROUP BY Author") | foreach {
     [Console]::WriteLine($_["Author"].RawValue)
}

The error is:

Line |
  37 |  $db.Execute("Select Author FROM Quotes GROUP BY Author") | foreach {
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "Execute" with "1" argument(s): "Field 'Author' is invalid in the select list because it is
     | not contained in either an aggregate function or the GROUP BY clause."

Thank

Answer 1

From the LiteDB query API documentation:

Group By

If this clause is present, the results are grouped by an expression and the query returns a document for each group.

[...]

If this clause is present, only the special parameter @key (which returns the value in the grouping expression) or aggregate functions can be used in the SELECT clause.

So you need to use @key to reference the grouping key:

$reader = $db.Execute('SELECT @key AS AuthorName, COUNT(*) AS QuoteCount FROM Quotes GROUP BY Author')

while ($reader.Read()){
    Write-Host "Author '$($reader['AuthorName'].RawValue)' has $($reader['QuoteCount'].RawValue) quotes in the collection!"
}