10. FLWOR Expressions

For

Let

Where

Order By

Return

FLWOR expressions begin with a For or Let clause and must end with a Return clause.

For Clause

for $x in 1 to 3
return $x,

for $x in json-doc("collection-answers.json").answers[]
return $x.owner.display_name

produces

1
2
3
Ubiguchi
Rob Wells
Victor Nicollet
descent89

Two For-clauses

for $x in ( 1, 2, 3 )
for $y in ( 1, 2, 3 )
return 10 * $x + $y

produces

11
12
13
21
22
23
31
32
33

For-clause with two variables

for $x in ( 1, 2, 3 ), $y in ( 1, 2, 3 )
return 10 * $x + $y

produces same answer as previous expression!

Two For-clauses

for $x in ( [ 1, 2, 3 ],
            [ 4, 5, 6 ],
            [ 7, 8, 9 ] ),
    $y in $x[]
return $y,

for $x in json-doc("collection-faq.json").faqs[]
for $y in $x.tags[]
return 
  { 
    "id" : $x.question_id, 
    "tag": $y
  }

produces

1
2
3
4
5
6
7
8
9
{ "id" : 4419499, "tag" : "php" }
{ "id" : 4419499, "tag" : "mysql" }
{ "id" : 4419499, "tag" : "nosql" }
{ "id" : 4419499, "tag" : "cassandra" }
{ "id" : 282783, "tag" : "sql" }
{ "id" : 282783, "tag" : "database" }
{ "id" : 282783, "tag" : "nosql" }
{ "id" : 282783, "tag" : "non-relational-database" }

For-clause with a position variable

for $x at $position in json-doc("collection-answers.json").answers[]
return 
  {
    "old id" : $x.answer_id,
    "new id" : $position
  }

produces

{ "old id" : 37841, "new id" : 1 }
{ "old id" : 37844, "new id" : 2 }
{ "old id" : 4419542, "new id" : 3 }
{ "old id" : 4419578, "new id" : 4 }

A regular join

for $question in json-doc("collection-faq.json").faqs[],
    $answer   in json-doc("collection-answers.json").answers[]
      [$$.question_id eq $question.question_id]
return 
  { 
    "question" : $question.title,
    "answer score" : $answer.score
  }

produces

{ "question" : "MySQL and NoSQL: Help me to choose the right one", "answer score" : 17 }
{ "question" : "MySQL and NoSQL: Help me to choose the right one", "answer score" : 1 }

An outer join

for $question in json-doc("collection-faq.json").faqs[],
    $answer allowing empty in json-doc("collection-answers.json").answers[]
      [$$.question_id eq $question.question_id]
return 
  { 
    "question" : $question.title,
    "answer score" : $answer.score
  }

produces

{ "question" : "MySQL and NoSQL: Help me to choose the right one", "answer score" : 17 }
{ "question" : "MySQL and NoSQL: Help me to choose the right one", "answer score" : 1 }
{ "question" : "The Next-gen Databases", "answer score" : null }

Where Clause

Simple where condition

The following query corresponds to SELECT q.title as question, q.question_id as id FROM faq WHERE CONTAINS(question, ‘NoSQL’).

for $question in json-doc("collection-faq.json").faqs[]
where contains($question.title, "NoSQL")
return 
  { 
    "question" : $question.title,
    "id" : $question.question_id
  }

produces

{ 
  "question" : "MySQL and NoSQL: Help me to choose the right one", 
  "id" : 4419499
}

Previous join-query using where-clause:

for $question in json-doc("collection-faq.json").faqs[],
    $answer   in json-doc("collection-answers.json").answers[]
where $answer.question_id eq $question.question_id
return 
  { 
    "question" : $question.title,
    "answer score" : $answer.score 
  }

Order By Clause

for $answer in json-doc("collection-answers.json").answers[]
order by $answer.owner.display_name
return 
  {
    "owner" : $answer.owner.display_name,
    "score" : $answer.score
  }

produces

{ "owner" : "Rob Wells", "score" : 4 }
{ "owner" : "Ubiguchi", "score" : 7 }
{ "owner" : "Victor Nicollet", "score" : 17 }
{ "owner" : "descent89", "score" : 1 }

order by with two criteria

for $answer in json-doc("collection-answers.json").answers[]
order by $answer.owner.display_name, $answer.score
return 
  {
    "owner" : $answer.owner.display_name,
    "score" : $answer.score
  }

An order by clause with ordering options.

for $answer in json-doc("collection-answers.json").answers[]
order by $answer.owner.display_name descending empty greatest,
         $answer.score ascending
return 
  {
    "owner" : $answer.owner.display_name,
    "score" : $answer.score
  }

produces

{ "owner" : "descent89", "score" : 1 }
{ "owner" : "Victor Nicollet", "score" : 17 }
{ "owner" : "Ubiguchi", "score" : 7 }
{ "owner" : "Rob Wells", "score" : 4 }

Group By Clause

for $answer in json-doc("collection-answers.json").answers[]
group by $question := $answer.question_id
return 
  { 
    "question" : $question
  }

produces

{ "question" : 4419499 }
{ "question" : 37823 }

As for each other (non-grouping) variable, the original values of the variable within one group are all concatenated into a single sequence, which is bound to this variable in the output tuple for this group. Aggregations can be done on these variables.

A group by clause using count aggregation.

for $answer in json-doc("collection-answers.json").answers[]
group by $question := $answer.question_id
return 
  {
    "question" : $question,
    "count" : count($answer)
  }

produces

{ "question" : 4419499, "count" : 2 }
{ "question" : 37823, "count" : 2 }

A group by clause using average aggregation.

for $answer in json-doc("collection-answers.json").answers[]
group by $question := $answer.question_id
return 
  {
    "question" : $question,
    "average score" : avg($answer.score)
  }

produces

{ "question" : 4419499, "average score" : 9 }
{ "question" : 37823, "average score" : 5.5 }

A group by clause with a nested expression.

for $answer in json-doc("collection-answers.json").answers[]
group by $question := $answer.question_id
return 
  {
    "question" : $question,
    "scores" : [ $answer.score ]
  }

produces

{ "question" : 4419499, "scores" : [ 17, 1 ] }
{ "question" : 37823, "scores" : [ 7, 4 ] }

A group by clause with a post-grouping condition.

Unlike SQL, JSONiq does not need a having clause, because a where clause works perfectly after grouping as well.

for $answer in json-doc("collection-answers.json").answers[]
group by $question := $answer.question_id
where count($answer) gt 1
return 
  {
    "question" : $question,
    "count" : count($answer)
  }

Let Clauses

Let bindings can be used to define aliases for any sequence, for convenience.

for $answer in json-doc("collection-answers.json").answers[]
let $qid := $answer.question_id
group by $question := $qid
let $count := count($answer)
where $count gt 1
return 
  {
    "question" : $question,
    "count" : $count
  }

produces

{ "question" : 4419499, "count" : 2 }
{ "question" : 37823, "count" : 2 }

Let clause with redefinition of same variable!

for $answer in json-doc("collection-answers.json").answers[]
let $qid := $answer.question_id
group by $qid
let $count := count($answer)
where $count gt 1
let $count := size(json-doc("collection-faq.json").faqs[]
                     [ $$.question_id eq $qid ].tags
              )
return 
  {
    "question" : json-doc("collection-faq.json").faqs[]
                 [$$.question_id eq $qid].title,
    "count" : $count
}

produces

{ "question" : "MySQL and NoSQL: Help me to choose the right one", "count" : 4 }
{ "question" : null, "count" : null }

Nested FLWOR Expression

[
  for $answer in json-doc("collection-answers.json").answers[]
  let $oid := $answer.owner.user_id
  where count(
          for $question in json-doc("collection-faq.json").faqs[]
          where some $other-answer
                in json-doc("collection-answers.json").answers[]
                     [$$.question_id eq $question.question_id
                      and
                      $$.owner.user_id eq $oid]
                satisfies
                  $other-answer.score gt $answer.score
          return $question
        ) ge 2
  return 
    $answer.owner.display_name
]

This query produces [ ]!