FLWOR.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center>RumbleDB sandbox</center>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a RumbleDB sandbox that allows you to play with simple JSONiq queries.\n",
    "\n",
    "It is a jupyter notebook that you can also download and execute on your own machine, but if you arrived here from the RumbleDB website, it is likely to be shown within Google's Colab environment.\n",
    "\n",
    "To get started, you first need to execute the cell below to activate the RumbleDB magic (you do not need to understand what it does, this is just initialization Python code)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install rumbledb\n",
    "%load_ext rumbledb\n",
    "%env RUMBLEDB_SERVER=http://public.rumbledb.org:9090/jsoniq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, this notebook uses a small public backend provided by us. Each query runs on just one machine that is very limited in CPU: one core and memory: 1GB, and with only the http scheme activated. This is sufficient to discover RumbleDB and play a bit, but of course is not intended for any production use. If you need to use RumbleDB in production, you can use it with an installation of Spark either on your machine or on a cluster.\n",
    "\n",
    "This sandbox backend may occasionally break, especially if too many users use it at the same time, so please bear with us! The system is automatically restarted every day so, if it stops working, you can either try again in 24 hours or notify us.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is straightforward to execute your own RumbleDB server on your own Spark cluster (and then you can make full use of all the input file systems and file formats). In this case, just replace the above server with your own hostname and port. Note that if you run RumbleDB as a server locally, you will also need to download and use this notebook locally rather than in this Google Colab environment as, obviously, your personal computer cannot be accessed from the Web.\n",
    "\n",
    "Now we are all set! You can now start reading and executing the JSONiq queries as you go, and you can even edit them!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The dataset\n",
    "\n",
    "The dataset contains products:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 1.8291950225830078 ms\n",
      "{\"product\": \"blender\", \"store-number\": 20, \"quantity\": 920}\n",
      "{\"product\": \"shirt\", \"store-number\": 39, \"quantity\": 839}\n",
      "{\"product\": \"tv\", \"store-number\": 58, \"quantity\": 758}\n",
      "{\"product\": \"toaster\", \"store-number\": 77, \"quantity\": 677}\n",
      "{\"product\": \"socks\", \"store-number\": 96, \"quantity\": 596}\n",
      "{\"product\": \"phone\", \"store-number\": 15, \"quantity\": 515}\n",
      "{\"product\": \"broiler\", \"store-number\": 34, \"quantity\": 434}\n",
      "{\"product\": \"blender\", \"store-number\": 53, \"quantity\": 353}\n",
      "{\"product\": \"shirt\", \"store-number\": 72, \"quantity\": 272}\n",
      "{\"product\": \"tv\", \"store-number\": 91, \"quantity\": 191}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "\n",
    "json-file(\"https://www.rumbledb.org/samples/products-small.json\")[position() le 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As well as contries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.6905701160430908 ms\n",
      "{\"sid\": 1, \"country\": \"Afghanistan\"}\n",
      "{\"sid\": 2, \"country\": \"land Islands\"}\n",
      "{\"sid\": 3, \"country\": \"Albania\"}\n",
      "{\"sid\": 4, \"country\": \"Algeria\"}\n",
      "{\"sid\": 5, \"country\": \"American Samoa\"}\n",
      "{\"sid\": 6, \"country\": \"AndorrA\"}\n",
      "{\"sid\": 7, \"country\": \"Angola\"}\n",
      "{\"sid\": 8, \"country\": \"Anguilla\"}\n",
      "{\"sid\": 9, \"country\": \"Antarctica\"}\n",
      "{\"sid\": 10, \"country\": \"Antigua and Barbuda\"}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "\n",
    "json-file(\"https://www.rumbledb.org/samples/countries.json\")[position() le 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## For clauses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For clauses bind their variable in turn to each item of the provided expression. Here is an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.05975675582885742 ms\n",
      "{\"number\": 1, \"square\": 1}\n",
      "{\"number\": 2, \"square\": 4}\n",
      "{\"number\": 3, \"square\": 9}\n",
      "{\"number\": 4, \"square\": 16}\n",
      "{\"number\": 5, \"square\": 25}\n",
      "{\"number\": 6, \"square\": 36}\n",
      "{\"number\": 7, \"square\": 49}\n",
      "{\"number\": 8, \"square\": 64}\n",
      "{\"number\": 9, \"square\": 81}\n",
      "{\"number\": 10, \"square\": 100}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for$x in 1 to 10\n",
    "return\n",
    "  {\n",
    "    \"number\": $x,\n",
    "    \"square\": $x * $x\n",
    "  }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above query, the variable $x is bound with 1, then with 2, then with 3, etc, and finally with 10. It is always bound with a sequence of exactly one item. It is, however, possible to bind it with an empty sequence if the expression returns no items. This is done with “allowing empty”."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.01749706268310547 ms\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x allowing empty in () return count($x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that, without “allowing empty”, if the expression in the for clause evaluates to an empty sequence, the variable would not bind to anything at all and the FLWOR expression would simply return an empty sequence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.01473093032836914 ms\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in () return count($x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each variable binding is also more generally called a tuple. In this above examples, there is only one variable binding in each tuple (x), but it is possible to build larger tuples with more clauses. For example, this FLWOR expression involves two for clauses. The tuples after the first for clause and before the second one only bind variable x (to 1, then to 2, then to 3), but the tuple after the second for clause and before the return clause bind variables x and y. There are six tuples in total, because the second for clause expands each incoming tuple to zero, one or more tuples (think of a flatMap transformation in Spark for an analogy)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.017578125 ms\n",
      "[1, 1]\n",
      "[2, 1]\n",
      "[2, 2]\n",
      "[3, 1]\n",
      "[3, 2]\n",
      "[3, 3]\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for$x in 1 to 3\n",
    "for $y in 1 to $x\n",
    "return [ $x, $y ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now if we use our small example dataset, we can iterate on all objects, say, products:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 2.0165750980377197 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "return $product.product"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It can thus be seen that the for clause is akin to the FROM clause in SQL, and the return is akin to the SELECT clause.\n",
    "Projection in JSONiq can be made with a project() function call, with the keys to keep:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 1.7290990352630615 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "{\"product\": \"blender\", \"store-number\": 20}\n",
      "{\"product\": \"shirt\", \"store-number\": 39}\n",
      "{\"product\": \"tv\", \"store-number\": 58}\n",
      "{\"product\": \"toaster\", \"store-number\": 77}\n",
      "{\"product\": \"socks\", \"store-number\": 96}\n",
      "{\"product\": \"phone\", \"store-number\": 15}\n",
      "{\"product\": \"broiler\", \"store-number\": 34}\n",
      "{\"product\": \"blender\", \"store-number\": 53}\n",
      "{\"product\": \"shirt\", \"store-number\": 72}\n",
      "{\"product\": \"tv\", \"store-number\": 91}\n",
      "{\"product\": \"toaster\", \"store-number\": 10}\n",
      "{\"product\": \"socks\", \"store-number\": 29}\n",
      "{\"product\": \"phone\", \"store-number\": 48}\n",
      "{\"product\": \"broiler\", \"store-number\": 67}\n",
      "{\"product\": \"blender\", \"store-number\": 86}\n",
      "{\"product\": \"shirt\", \"store-number\": 5}\n",
      "{\"product\": \"tv\", \"store-number\": 24}\n",
      "{\"product\": \"toaster\", \"store-number\": 43}\n",
      "{\"product\": \"socks\", \"store-number\": 62}\n",
      "{\"product\": \"phone\", \"store-number\": 81}\n",
      "{\"product\": \"broiler\", \"store-number\": 100}\n",
      "{\"product\": \"blender\", \"store-number\": 19}\n",
      "{\"product\": \"shirt\", \"store-number\": 38}\n",
      "{\"product\": \"tv\", \"store-number\": 57}\n",
      "{\"product\": \"toaster\", \"store-number\": 76}\n",
      "{\"product\": \"socks\", \"store-number\": 95}\n",
      "{\"product\": \"phone\", \"store-number\": 14}\n",
      "{\"product\": \"broiler\", \"store-number\": 33}\n",
      "{\"product\": \"blender\", \"store-number\": 52}\n",
      "{\"product\": \"shirt\", \"store-number\": 71}\n",
      "{\"product\": \"tv\", \"store-number\": 90}\n",
      "{\"product\": \"toaster\", \"store-number\": 9}\n",
      "{\"product\": \"socks\", \"store-number\": 28}\n",
      "{\"product\": \"phone\", \"store-number\": 47}\n",
      "{\"product\": \"broiler\", \"store-number\": 66}\n",
      "{\"product\": \"blender\", \"store-number\": 85}\n",
      "{\"product\": \"shirt\", \"store-number\": 4}\n",
      "{\"product\": \"tv\", \"store-number\": 23}\n",
      "{\"product\": \"toaster\", \"store-number\": 42}\n",
      "{\"product\": \"socks\", \"store-number\": 61}\n",
      "{\"product\": \"phone\", \"store-number\": 80}\n",
      "{\"product\": \"broiler\", \"store-number\": 99}\n",
      "{\"product\": \"blender\", \"store-number\": 18}\n",
      "{\"product\": \"shirt\", \"store-number\": 37}\n",
      "{\"product\": \"tv\", \"store-number\": 56}\n",
      "{\"product\": \"toaster\", \"store-number\": 75}\n",
      "{\"product\": \"socks\", \"store-number\": 94}\n",
      "{\"product\": \"phone\", \"store-number\": 13}\n",
      "{\"product\": \"broiler\", \"store-number\": 32}\n",
      "{\"product\": \"blender\", \"store-number\": 51}\n",
      "{\"product\": \"shirt\", \"store-number\": 70}\n",
      "{\"product\": \"tv\", \"store-number\": 89}\n",
      "{\"product\": \"toaster\", \"store-number\": 8}\n",
      "{\"product\": \"socks\", \"store-number\": 27}\n",
      "{\"product\": \"phone\", \"store-number\": 46}\n",
      "{\"product\": \"broiler\", \"store-number\": 65}\n",
      "{\"product\": \"blender\", \"store-number\": 84}\n",
      "{\"product\": \"shirt\", \"store-number\": 3}\n",
      "{\"product\": \"tv\", \"store-number\": 22}\n",
      "{\"product\": \"toaster\", \"store-number\": 41}\n",
      "{\"product\": \"socks\", \"store-number\": 60}\n",
      "{\"product\": \"phone\", \"store-number\": 79}\n",
      "{\"product\": \"broiler\", \"store-number\": 98}\n",
      "{\"product\": \"blender\", \"store-number\": 17}\n",
      "{\"product\": \"shirt\", \"store-number\": 36}\n",
      "{\"product\": \"tv\", \"store-number\": 55}\n",
      "{\"product\": \"toaster\", \"store-number\": 74}\n",
      "{\"product\": \"socks\", \"store-number\": 93}\n",
      "{\"product\": \"phone\", \"store-number\": 12}\n",
      "{\"product\": \"broiler\", \"store-number\": 31}\n",
      "{\"product\": \"blender\", \"store-number\": 50}\n",
      "{\"product\": \"shirt\", \"store-number\": 69}\n",
      "{\"product\": \"tv\", \"store-number\": 88}\n",
      "{\"product\": \"toaster\", \"store-number\": 7}\n",
      "{\"product\": \"socks\", \"store-number\": 26}\n",
      "{\"product\": \"phone\", \"store-number\": 45}\n",
      "{\"product\": \"broiler\", \"store-number\": 64}\n",
      "{\"product\": \"blender\", \"store-number\": 83}\n",
      "{\"product\": \"shirt\", \"store-number\": 2}\n",
      "{\"product\": \"tv\", \"store-number\": 21}\n",
      "{\"product\": \"toaster\", \"store-number\": 40}\n",
      "{\"product\": \"socks\", \"store-number\": 59}\n",
      "{\"product\": \"phone\", \"store-number\": 78}\n",
      "{\"product\": \"broiler\", \"store-number\": 97}\n",
      "{\"product\": \"blender\", \"store-number\": 16}\n",
      "{\"product\": \"shirt\", \"store-number\": 35}\n",
      "{\"product\": \"tv\", \"store-number\": 54}\n",
      "{\"product\": \"toaster\", \"store-number\": 73}\n",
      "{\"product\": \"socks\", \"store-number\": 92}\n",
      "{\"product\": \"phone\", \"store-number\": 11}\n",
      "{\"product\": \"broiler\", \"store-number\": 30}\n",
      "{\"product\": \"blender\", \"store-number\": 49}\n",
      "{\"product\": \"shirt\", \"store-number\": 68}\n",
      "{\"product\": \"tv\", \"store-number\": 87}\n",
      "{\"product\": \"toaster\", \"store-number\": 6}\n",
      "{\"product\": \"socks\", \"store-number\": 25}\n",
      "{\"product\": \"phone\", \"store-number\": 44}\n",
      "{\"product\": \"broiler\", \"store-number\": 63}\n",
      "{\"product\": \"blender\", \"store-number\": 82}\n",
      "{\"product\": \"shirt\", \"store-number\": 1}\n",
      "{\"product\": \"tv\", \"store-number\": 20}\n",
      "{\"product\": \"toaster\", \"store-number\": 39}\n",
      "{\"product\": \"socks\", \"store-number\": 58}\n",
      "{\"product\": \"phone\", \"store-number\": 77}\n",
      "{\"product\": \"broiler\", \"store-number\": 96}\n",
      "{\"product\": \"blender\", \"store-number\": 15}\n",
      "{\"product\": \"shirt\", \"store-number\": 34}\n",
      "{\"product\": \"tv\", \"store-number\": 53}\n",
      "{\"product\": \"toaster\", \"store-number\": 72}\n",
      "{\"product\": \"socks\", \"store-number\": 91}\n",
      "{\"product\": \"phone\", \"store-number\": 10}\n",
      "{\"product\": \"broiler\", \"store-number\": 29}\n",
      "{\"product\": \"blender\", \"store-number\": 48}\n",
      "{\"product\": \"shirt\", \"store-number\": 67}\n",
      "{\"product\": \"tv\", \"store-number\": 86}\n",
      "{\"product\": \"toaster\", \"store-number\": 5}\n",
      "{\"product\": \"socks\", \"store-number\": 24}\n",
      "{\"product\": \"phone\", \"store-number\": 43}\n",
      "{\"product\": \"broiler\", \"store-number\": 62}\n",
      "{\"product\": \"blender\", \"store-number\": 81}\n",
      "{\"product\": \"shirt\", \"store-number\": 100}\n",
      "{\"product\": \"tv\", \"store-number\": 19}\n",
      "{\"product\": \"toaster\", \"store-number\": 38}\n",
      "{\"product\": \"socks\", \"store-number\": 57}\n",
      "{\"product\": \"phone\", \"store-number\": 76}\n",
      "{\"product\": \"broiler\", \"store-number\": 95}\n",
      "{\"product\": \"blender\", \"store-number\": 14}\n",
      "{\"product\": \"shirt\", \"store-number\": 33}\n",
      "{\"product\": \"tv\", \"store-number\": 52}\n",
      "{\"product\": \"toaster\", \"store-number\": 71}\n",
      "{\"product\": \"socks\", \"store-number\": 90}\n",
      "{\"product\": \"phone\", \"store-number\": 9}\n",
      "{\"product\": \"broiler\", \"store-number\": 28}\n",
      "{\"product\": \"blender\", \"store-number\": 47}\n",
      "{\"product\": \"shirt\", \"store-number\": 66}\n",
      "{\"product\": \"tv\", \"store-number\": 85}\n",
      "{\"product\": \"toaster\", \"store-number\": 4}\n",
      "{\"product\": \"socks\", \"store-number\": 23}\n",
      "{\"product\": \"phone\", \"store-number\": 42}\n",
      "{\"product\": \"broiler\", \"store-number\": 61}\n",
      "{\"product\": \"blender\", \"store-number\": 80}\n",
      "{\"product\": \"shirt\", \"store-number\": 99}\n",
      "{\"product\": \"tv\", \"store-number\": 18}\n",
      "{\"product\": \"toaster\", \"store-number\": 37}\n",
      "{\"product\": \"socks\", \"store-number\": 56}\n",
      "{\"product\": \"phone\", \"store-number\": 75}\n",
      "{\"product\": \"broiler\", \"store-number\": 94}\n",
      "{\"product\": \"blender\", \"store-number\": 13}\n",
      "{\"product\": \"shirt\", \"store-number\": 32}\n",
      "{\"product\": \"tv\", \"store-number\": 51}\n",
      "{\"product\": \"toaster\", \"store-number\": 70}\n",
      "{\"product\": \"socks\", \"store-number\": 89}\n",
      "{\"product\": \"phone\", \"store-number\": 8}\n",
      "{\"product\": \"broiler\", \"store-number\": 27}\n",
      "{\"product\": \"blender\", \"store-number\": 46}\n",
      "{\"product\": \"shirt\", \"store-number\": 65}\n",
      "{\"product\": \"tv\", \"store-number\": 84}\n",
      "{\"product\": \"toaster\", \"store-number\": 3}\n",
      "{\"product\": \"socks\", \"store-number\": 22}\n",
      "{\"product\": \"phone\", \"store-number\": 41}\n",
      "{\"product\": \"broiler\", \"store-number\": 60}\n",
      "{\"product\": \"blender\", \"store-number\": 79}\n",
      "{\"product\": \"shirt\", \"store-number\": 98}\n",
      "{\"product\": \"tv\", \"store-number\": 17}\n",
      "{\"product\": \"toaster\", \"store-number\": 36}\n",
      "{\"product\": \"socks\", \"store-number\": 55}\n",
      "{\"product\": \"phone\", \"store-number\": 74}\n",
      "{\"product\": \"broiler\", \"store-number\": 93}\n",
      "{\"product\": \"blender\", \"store-number\": 12}\n",
      "{\"product\": \"shirt\", \"store-number\": 31}\n",
      "{\"product\": \"tv\", \"store-number\": 50}\n",
      "{\"product\": \"toaster\", \"store-number\": 69}\n",
      "{\"product\": \"socks\", \"store-number\": 88}\n",
      "{\"product\": \"phone\", \"store-number\": 7}\n",
      "{\"product\": \"broiler\", \"store-number\": 26}\n",
      "{\"product\": \"blender\", \"store-number\": 45}\n",
      "{\"product\": \"shirt\", \"store-number\": 64}\n",
      "{\"product\": \"tv\", \"store-number\": 83}\n",
      "{\"product\": \"toaster\", \"store-number\": 2}\n",
      "{\"product\": \"socks\", \"store-number\": 21}\n",
      "{\"product\": \"phone\", \"store-number\": 40}\n",
      "{\"product\": \"broiler\", \"store-number\": 59}\n",
      "{\"product\": \"blender\", \"store-number\": 78}\n",
      "{\"product\": \"shirt\", \"store-number\": 97}\n",
      "{\"product\": \"tv\", \"store-number\": 16}\n",
      "{\"product\": \"toaster\", \"store-number\": 35}\n",
      "{\"product\": \"socks\", \"store-number\": 54}\n",
      "{\"product\": \"phone\", \"store-number\": 73}\n",
      "{\"product\": \"broiler\", \"store-number\": 92}\n",
      "{\"product\": \"blender\", \"store-number\": 11}\n",
      "{\"product\": \"shirt\", \"store-number\": 30}\n",
      "{\"product\": \"tv\", \"store-number\": 49}\n",
      "{\"product\": \"toaster\", \"store-number\": 68}\n",
      "{\"product\": \"socks\", \"store-number\": 87}\n",
      "{\"product\": \"phone\", \"store-number\": 6}\n",
      "{\"product\": \"broiler\", \"store-number\": 25}\n",
      "{\"product\": \"blender\", \"store-number\": 44}\n",
      "{\"product\": \"shirt\", \"store-number\": 63}\n",
      "{\"product\": \"tv\", \"store-number\": 82}\n",
      "{\"product\": \"toaster\", \"store-number\": 1}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "return project($product, (\"product\", \"store-number\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us look closer at the key called \"type\". What values does it take? We can use dot-based navigation to navigate down to these values. This will work nicely on the entire dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 1.7029812335968018 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "{\"store-number\": 20}\n",
      "{\"store-number\": 39}\n",
      "{\"store-number\": 58}\n",
      "{\"store-number\": 77}\n",
      "{\"store-number\": 96}\n",
      "{\"store-number\": 15}\n",
      "{\"store-number\": 34}\n",
      "{\"store-number\": 53}\n",
      "{\"store-number\": 72}\n",
      "{\"store-number\": 91}\n",
      "{\"store-number\": 10}\n",
      "{\"store-number\": 29}\n",
      "{\"store-number\": 48}\n",
      "{\"store-number\": 67}\n",
      "{\"store-number\": 86}\n",
      "{\"store-number\": 5}\n",
      "{\"store-number\": 24}\n",
      "{\"store-number\": 43}\n",
      "{\"store-number\": 62}\n",
      "{\"store-number\": 81}\n",
      "{\"store-number\": 100}\n",
      "{\"store-number\": 19}\n",
      "{\"store-number\": 38}\n",
      "{\"store-number\": 57}\n",
      "{\"store-number\": 76}\n",
      "{\"store-number\": 95}\n",
      "{\"store-number\": 14}\n",
      "{\"store-number\": 33}\n",
      "{\"store-number\": 52}\n",
      "{\"store-number\": 71}\n",
      "{\"store-number\": 90}\n",
      "{\"store-number\": 9}\n",
      "{\"store-number\": 28}\n",
      "{\"store-number\": 47}\n",
      "{\"store-number\": 66}\n",
      "{\"store-number\": 85}\n",
      "{\"store-number\": 4}\n",
      "{\"store-number\": 23}\n",
      "{\"store-number\": 42}\n",
      "{\"store-number\": 61}\n",
      "{\"store-number\": 80}\n",
      "{\"store-number\": 99}\n",
      "{\"store-number\": 18}\n",
      "{\"store-number\": 37}\n",
      "{\"store-number\": 56}\n",
      "{\"store-number\": 75}\n",
      "{\"store-number\": 94}\n",
      "{\"store-number\": 13}\n",
      "{\"store-number\": 32}\n",
      "{\"store-number\": 51}\n",
      "{\"store-number\": 70}\n",
      "{\"store-number\": 89}\n",
      "{\"store-number\": 8}\n",
      "{\"store-number\": 27}\n",
      "{\"store-number\": 46}\n",
      "{\"store-number\": 65}\n",
      "{\"store-number\": 84}\n",
      "{\"store-number\": 3}\n",
      "{\"store-number\": 22}\n",
      "{\"store-number\": 41}\n",
      "{\"store-number\": 60}\n",
      "{\"store-number\": 79}\n",
      "{\"store-number\": 98}\n",
      "{\"store-number\": 17}\n",
      "{\"store-number\": 36}\n",
      "{\"store-number\": 55}\n",
      "{\"store-number\": 74}\n",
      "{\"store-number\": 93}\n",
      "{\"store-number\": 12}\n",
      "{\"store-number\": 31}\n",
      "{\"store-number\": 50}\n",
      "{\"store-number\": 69}\n",
      "{\"store-number\": 88}\n",
      "{\"store-number\": 7}\n",
      "{\"store-number\": 26}\n",
      "{\"store-number\": 45}\n",
      "{\"store-number\": 64}\n",
      "{\"store-number\": 83}\n",
      "{\"store-number\": 2}\n",
      "{\"store-number\": 21}\n",
      "{\"store-number\": 40}\n",
      "{\"store-number\": 59}\n",
      "{\"store-number\": 78}\n",
      "{\"store-number\": 97}\n",
      "{\"store-number\": 16}\n",
      "{\"store-number\": 35}\n",
      "{\"store-number\": 54}\n",
      "{\"store-number\": 73}\n",
      "{\"store-number\": 92}\n",
      "{\"store-number\": 11}\n",
      "{\"store-number\": 30}\n",
      "{\"store-number\": 49}\n",
      "{\"store-number\": 68}\n",
      "{\"store-number\": 87}\n",
      "{\"store-number\": 6}\n",
      "{\"store-number\": 25}\n",
      "{\"store-number\": 44}\n",
      "{\"store-number\": 63}\n",
      "{\"store-number\": 82}\n",
      "{\"store-number\": 1}\n",
      "{\"store-number\": 20}\n",
      "{\"store-number\": 39}\n",
      "{\"store-number\": 58}\n",
      "{\"store-number\": 77}\n",
      "{\"store-number\": 96}\n",
      "{\"store-number\": 15}\n",
      "{\"store-number\": 34}\n",
      "{\"store-number\": 53}\n",
      "{\"store-number\": 72}\n",
      "{\"store-number\": 91}\n",
      "{\"store-number\": 10}\n",
      "{\"store-number\": 29}\n",
      "{\"store-number\": 48}\n",
      "{\"store-number\": 67}\n",
      "{\"store-number\": 86}\n",
      "{\"store-number\": 5}\n",
      "{\"store-number\": 24}\n",
      "{\"store-number\": 43}\n",
      "{\"store-number\": 62}\n",
      "{\"store-number\": 81}\n",
      "{\"store-number\": 100}\n",
      "{\"store-number\": 19}\n",
      "{\"store-number\": 38}\n",
      "{\"store-number\": 57}\n",
      "{\"store-number\": 76}\n",
      "{\"store-number\": 95}\n",
      "{\"store-number\": 14}\n",
      "{\"store-number\": 33}\n",
      "{\"store-number\": 52}\n",
      "{\"store-number\": 71}\n",
      "{\"store-number\": 90}\n",
      "{\"store-number\": 9}\n",
      "{\"store-number\": 28}\n",
      "{\"store-number\": 47}\n",
      "{\"store-number\": 66}\n",
      "{\"store-number\": 85}\n",
      "{\"store-number\": 4}\n",
      "{\"store-number\": 23}\n",
      "{\"store-number\": 42}\n",
      "{\"store-number\": 61}\n",
      "{\"store-number\": 80}\n",
      "{\"store-number\": 99}\n",
      "{\"store-number\": 18}\n",
      "{\"store-number\": 37}\n",
      "{\"store-number\": 56}\n",
      "{\"store-number\": 75}\n",
      "{\"store-number\": 94}\n",
      "{\"store-number\": 13}\n",
      "{\"store-number\": 32}\n",
      "{\"store-number\": 51}\n",
      "{\"store-number\": 70}\n",
      "{\"store-number\": 89}\n",
      "{\"store-number\": 8}\n",
      "{\"store-number\": 27}\n",
      "{\"store-number\": 46}\n",
      "{\"store-number\": 65}\n",
      "{\"store-number\": 84}\n",
      "{\"store-number\": 3}\n",
      "{\"store-number\": 22}\n",
      "{\"store-number\": 41}\n",
      "{\"store-number\": 60}\n",
      "{\"store-number\": 79}\n",
      "{\"store-number\": 98}\n",
      "{\"store-number\": 17}\n",
      "{\"store-number\": 36}\n",
      "{\"store-number\": 55}\n",
      "{\"store-number\": 74}\n",
      "{\"store-number\": 93}\n",
      "{\"store-number\": 12}\n",
      "{\"store-number\": 31}\n",
      "{\"store-number\": 50}\n",
      "{\"store-number\": 69}\n",
      "{\"store-number\": 88}\n",
      "{\"store-number\": 7}\n",
      "{\"store-number\": 26}\n",
      "{\"store-number\": 45}\n",
      "{\"store-number\": 64}\n",
      "{\"store-number\": 83}\n",
      "{\"store-number\": 2}\n",
      "{\"store-number\": 21}\n",
      "{\"store-number\": 40}\n",
      "{\"store-number\": 59}\n",
      "{\"store-number\": 78}\n",
      "{\"store-number\": 97}\n",
      "{\"store-number\": 16}\n",
      "{\"store-number\": 35}\n",
      "{\"store-number\": 54}\n",
      "{\"store-number\": 73}\n",
      "{\"store-number\": 92}\n",
      "{\"store-number\": 11}\n",
      "{\"store-number\": 30}\n",
      "{\"store-number\": 49}\n",
      "{\"store-number\": 68}\n",
      "{\"store-number\": 87}\n",
      "{\"store-number\": 6}\n",
      "{\"store-number\": 25}\n",
      "{\"store-number\": 44}\n",
      "{\"store-number\": 63}\n",
      "{\"store-number\": 82}\n",
      "{\"store-number\": 1}\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to implement a join with a sequence of two for clauses and a predicate (note that newlines in JSONiq are irrelevant, so we spread the for clause on two lines in order to fit the query on this page):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 11.249583959579468 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"socks\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"phone\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"blender\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bhutan\"}\n",
      "{\"product\": \"tv\", \"country\": \"Botswana\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"socks\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"broiler\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"shirt\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"toaster\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"phone\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"tv\", \"country\": \"El Salvador\"}\n",
      "{\"product\": \"blender\", \"country\": \"Bangladesh\"}\n",
      "{\"product\": \"tv\", \"country\": \"Cote D\\\"Ivoire\"}\n",
      "{\"product\": \"tv\", \"country\": \"Bangladesh\"}\n",
      "{\"product\": \"socks\", \"country\": \"Cote D\\\"Ivoire\"}\n",
      "{\"product\": \"socks\", \"country\": \"Bangladesh\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Cote D\\\"Ivoire\"}\n",
      "{\"product\": \"broiler\", \"country\": \"Bangladesh\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Cote D\\\"Ivoire\"}\n",
      "{\"product\": \"shirt\", \"country\": \"Bangladesh\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Cote D\\\"Ivoire\"}\n",
      "{\"product\": \"toaster\", \"country\": \"Bangladesh\"}\n",
      "{\"product\": \"phone\", \"country\": \"Cote D\\\"Ivoire\"}\n",
      "{\"product\": \"phone\", \"country\": \"Bangladesh\"}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "for $store in json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "                    [$$.sid eq $product.store-number]\n",
    "return {\n",
    "  \"product\" : $product.product,\n",
    "  \"country\" : $store.country\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to implement a join with a sequence of two for clauses and a predicate (note that newlines in JSONiq are irrelevant, so we spread the for clause on two lines in order to fit the query on this page):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 12.377734184265137 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "{\"product\": \"blender\", \"country\": [\"Barbados\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Canada\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Czech Republic\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"French Southern Territories\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Holy See (Vatican City State)\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Austria\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bulgaria\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Costa Rica\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Fiji\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guinea\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Antigua and Barbuda\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Botswana\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Colombia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Eritrea\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Grenada\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"American Samoa\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Benin\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Chad\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Dominican Republic\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Germany\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Iceland\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Bangladesh\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Cameroon\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cyprus\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"French Polynesia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Heard Island and Mcdonald Islands\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Australia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Brunei Darussalam\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Cook Islands\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Faroe Islands\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guernsey\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Antarctica\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bosnia and Herzegovina\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Cocos (Keeling) Islands\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Equatorial Guinea\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Greenland\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Algeria\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Belize\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Central African Republic\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Dominica\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Georgia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Hungary\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Bahrain\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Cambodia\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cuba\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"French Guiana\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Haiti\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Aruba\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"British Indian Ocean Territory\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Congo, The Democratic Republic of the\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Falkland Islands (Malvinas)\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guatemala\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Anguilla\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bolivia\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Christmas Island\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"El Salvador\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Greece\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Albania\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Belgium\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cayman Islands\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Djibouti\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Gambia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Hong Kong\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Bahamas\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Burundi\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Croatia\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"France\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guyana\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Armenia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Brazil\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Congo\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Ethiopia\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guam\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Angola\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bhutan\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"China\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Egypt\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Gibraltar\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"land Islands\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Belarus\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cape Verde\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Denmark\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Gabon\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Honduras\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Azerbaijan\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Burkina Faso\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cote D\\\"Ivoire\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Finland\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guinea-Bissau\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Argentina\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bouvet Island\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Comoros\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Estonia\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guadeloupe\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"AndorrA\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bermuda\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Chile\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Ecuador\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Ghana\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Afghanistan\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Barbados\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Canada\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Czech Republic\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"French Southern Territories\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Holy See (Vatican City State)\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Austria\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Bulgaria\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Costa Rica\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Fiji\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guinea\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Antigua and Barbuda\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Botswana\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Colombia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Eritrea\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Grenada\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"American Samoa\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Benin\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Chad\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Dominican Republic\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Germany\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Iceland\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Bangladesh\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cameroon\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Cyprus\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"French Polynesia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Heard Island and Mcdonald Islands\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Australia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Brunei Darussalam\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cook Islands\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Faroe Islands\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guernsey\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Antarctica\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bosnia and Herzegovina\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Cocos (Keeling) Islands\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Equatorial Guinea\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Greenland\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Algeria\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Belize\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Central African Republic\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Dominica\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Georgia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Hungary\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Bahrain\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cambodia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Cuba\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"French Guiana\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Haiti\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Aruba\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"British Indian Ocean Territory\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Congo, The Democratic Republic of the\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Falkland Islands (Malvinas)\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guatemala\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Anguilla\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bolivia\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Christmas Island\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"El Salvador\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Greece\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Albania\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Belgium\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Cayman Islands\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Djibouti\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Gambia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Hong Kong\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Bahamas\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Burundi\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Croatia\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"France\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Guyana\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Armenia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Brazil\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Congo\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Ethiopia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guam\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Angola\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bhutan\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"China\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Egypt\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Gibraltar\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"land Islands\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Belarus\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Cape Verde\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Denmark\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Gabon\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Honduras\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Azerbaijan\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Burkina Faso\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Cote D\\\"Ivoire\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Finland\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Guinea-Bissau\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Argentina\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Bouvet Island\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Comoros\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Estonia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guadeloupe\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"AndorrA\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bermuda\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Chile\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Ecuador\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Ghana\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Afghanistan\"]}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "for $store allowing empty in json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "                       [$$.sid eq $product.store-number]\n",
    "return {\n",
    "  \"product\" : $product.product,\n",
    "  \"country\" : [ $store.country ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the case of the last product, no matching record in stores.json is found and store is bound to the empty sequence for that tuple. When constructing the object in the return clause’s expression, the empty sequence obtained from store.country is automatically replaced with a null value (because an object value cannot be empty). But if we add an array constructor around the country, we will notice the empty sequence:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 5.071000099182129 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "{\"product\": \"blender\", \"country\": [\"Barbados\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Canada\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Czech Republic\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"French Southern Territories\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Holy See (Vatican City State)\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Austria\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bulgaria\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Costa Rica\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Fiji\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guinea\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Antigua and Barbuda\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Botswana\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Colombia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Eritrea\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Grenada\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"American Samoa\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Benin\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Chad\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Dominican Republic\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Germany\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Iceland\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Bangladesh\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Cameroon\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cyprus\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"French Polynesia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Heard Island and Mcdonald Islands\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Australia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Brunei Darussalam\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Cook Islands\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Faroe Islands\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guernsey\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Antarctica\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bosnia and Herzegovina\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Cocos (Keeling) Islands\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Equatorial Guinea\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Greenland\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Algeria\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Belize\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Central African Republic\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Dominica\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Georgia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Hungary\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Bahrain\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Cambodia\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cuba\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"French Guiana\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Haiti\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Aruba\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"British Indian Ocean Territory\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Congo, The Democratic Republic of the\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Falkland Islands (Malvinas)\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guatemala\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Anguilla\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bolivia\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Christmas Island\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"El Salvador\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Greece\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Albania\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Belgium\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cayman Islands\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Djibouti\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Gambia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Hong Kong\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Bahamas\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Burundi\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Croatia\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"France\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guyana\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Armenia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Brazil\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Congo\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Ethiopia\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guam\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Angola\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bhutan\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"China\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Egypt\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Gibraltar\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"land Islands\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Belarus\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cape Verde\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Denmark\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Gabon\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Honduras\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Azerbaijan\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Burkina Faso\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cote D\\\"Ivoire\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Finland\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guinea-Bissau\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Argentina\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bouvet Island\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Comoros\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Estonia\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Guadeloupe\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"AndorrA\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Bermuda\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Chile\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Ecuador\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Ghana\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Afghanistan\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Barbados\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Canada\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Czech Republic\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"French Southern Territories\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Holy See (Vatican City State)\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Austria\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Bulgaria\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Costa Rica\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Fiji\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guinea\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Antigua and Barbuda\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Botswana\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Colombia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Eritrea\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Grenada\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"American Samoa\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Benin\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Chad\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Dominican Republic\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Germany\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Iceland\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Bangladesh\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cameroon\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Cyprus\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"French Polynesia\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Heard Island and Mcdonald Islands\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Australia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Brunei Darussalam\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Cook Islands\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Faroe Islands\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guernsey\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Antarctica\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bosnia and Herzegovina\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Cocos (Keeling) Islands\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Equatorial Guinea\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Greenland\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Algeria\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Belize\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Central African Republic\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Dominica\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Georgia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Hungary\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Bahrain\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Cambodia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Cuba\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"French Guiana\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Haiti\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Aruba\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"British Indian Ocean Territory\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Congo, The Democratic Republic of the\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Falkland Islands (Malvinas)\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guatemala\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Anguilla\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bolivia\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Christmas Island\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"El Salvador\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Greece\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Albania\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Belgium\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Cayman Islands\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Djibouti\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Gambia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Hong Kong\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Bahamas\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Burundi\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Croatia\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"France\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Guyana\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Armenia\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Brazil\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Congo\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Ethiopia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guam\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Angola\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bhutan\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"China\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Egypt\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Gibraltar\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"land Islands\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Belarus\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Cape Verde\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Denmark\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Gabon\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Honduras\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Azerbaijan\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Burkina Faso\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Cote D\\\"Ivoire\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"Finland\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Guinea-Bissau\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Argentina\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Bouvet Island\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Comoros\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Estonia\"]}\n",
      "{\"product\": \"socks\", \"country\": [\"Guadeloupe\"]}\n",
      "{\"product\": \"phone\", \"country\": [\"AndorrA\"]}\n",
      "{\"product\": \"broiler\", \"country\": [\"Bermuda\"]}\n",
      "{\"product\": \"blender\", \"country\": [\"Chile\"]}\n",
      "{\"product\": \"shirt\", \"country\": [\"Ecuador\"]}\n",
      "{\"product\": \"tv\", \"country\": [\"Ghana\"]}\n",
      "{\"product\": \"toaster\", \"country\": [\"Afghanistan\"]}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "for $store allowing empty in json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "                       [$$.sid eq $product.store-number]\n",
    "return {\n",
    "  \"product\" : $product.product,\n",
    "  \"country\" : [ $store.country ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let clauses\n",
    "\n",
    "As seen before, the let clause can be used to bind a variable with any sequence of items, also more than one. FLWOR expressions with just a cascade of let clauses are quite popular."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.016152143478393555 ms\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "let $x:=2\n",
    "return $x * $x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, let clauses can also appear after other clauses, for example, after a for clause. Then, they will bind a sequence of items for each previous binding (tuple), like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.015926837921142578 ms\n",
      "{\"number\": 1, \"square\": 1}\n",
      "{\"number\": 2, \"square\": 4}\n",
      "{\"number\": 3, \"square\": 9}\n",
      "{\"number\": 4, \"square\": 16}\n",
      "{\"number\": 5, \"square\": 25}\n",
      "{\"number\": 6, \"square\": 36}\n",
      "{\"number\": 7, \"square\": 49}\n",
      "{\"number\": 8, \"square\": 64}\n",
      "{\"number\": 9, \"square\": 81}\n",
      "{\"number\": 10, \"square\": 100}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 10\n",
    "let $square := $x * $x\n",
    "return {\n",
    "    \"number\": $x,\n",
    "    \"square\": $square\n",
    "  }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above example, $square is only bound with one item. Here is another example where it is bound with more than one:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.020356178283691406 ms\n",
      "{\"number\": 1, \"square\": 1, \"cube\": 1}\n",
      "{\"number\": 2, \"square\": 4, \"cube\": 8}\n",
      "{\"number\": 3, \"square\": 9, \"cube\": 27}\n",
      "{\"number\": 4, \"square\": 16, \"cube\": 64}\n",
      "{\"number\": 5, \"square\": 25, \"cube\": 125}\n",
      "{\"number\": 6, \"square\": 36, \"cube\": 216}\n",
      "{\"number\": 7, \"square\": 49, \"cube\": 343}\n",
      "{\"number\": 8, \"square\": 64, \"cube\": 512}\n",
      "{\"number\": 9, \"square\": 81, \"cube\": 729}\n",
      "{\"number\": 10, \"square\": 100, \"cube\": 1000}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 10\n",
    "let $square-and-cube := ($x * $x, $x * $x * $x)\n",
    "return\n",
    "  {\n",
    "    \"number\": $x,\n",
    "    \"square\": $square-and-cube[1],\n",
    "    \"cube\": $square-and-cube[2]\n",
    "  }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.019574880599975586 ms\n",
      "{\"number\": 1, \"square or cube\": 1}\n",
      "{\"number\": 1, \"square or cube\": 1}\n",
      "{\"number\": 2, \"square or cube\": 4}\n",
      "{\"number\": 2, \"square or cube\": 8}\n",
      "{\"number\": 3, \"square or cube\": 9}\n",
      "{\"number\": 3, \"square or cube\": 27}\n",
      "{\"number\": 4, \"square or cube\": 16}\n",
      "{\"number\": 4, \"square or cube\": 64}\n",
      "{\"number\": 5, \"square or cube\": 25}\n",
      "{\"number\": 5, \"square or cube\": 125}\n",
      "{\"number\": 6, \"square or cube\": 36}\n",
      "{\"number\": 6, \"square or cube\": 216}\n",
      "{\"number\": 7, \"square or cube\": 49}\n",
      "{\"number\": 7, \"square or cube\": 343}\n",
      "{\"number\": 8, \"square or cube\": 64}\n",
      "{\"number\": 8, \"square or cube\": 512}\n",
      "{\"number\": 9, \"square or cube\": 81}\n",
      "{\"number\": 9, \"square or cube\": 729}\n",
      "{\"number\": 10, \"square or cube\": 100}\n",
      "{\"number\": 10, \"square or cube\": 1000}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 10\n",
    "for $square-or-cube in ($x * $x, $x * $x * $x)\n",
    "return\n",
    "   {\n",
    "     \"number\": $x,\n",
    "     \"square or cube\": $square-or-cube\n",
    "   }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A let clause outputs exactly one outgoing tuple for each incoming tuple (think of a map transformation in Spark). Unlike the for clause, it does not modify the number of tuples.\n",
    "Let us now showcase the use of a let clause with our dataset.\n",
    "Now if we use our small example dataset, we can iterate on all objects, say, products:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.6827859878540039 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n",
      "\"socks\"\n",
      "\"phone\"\n",
      "\"broiler\"\n",
      "\"blender\"\n",
      "\"shirt\"\n",
      "\"tv\"\n",
      "\"toaster\"\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "let $type := $product.product\n",
    "return $type"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let clauses also allow for joining the two datasets and denormalizing them by nesting the stores into the products. This would be consider- ably more difficult to do with (Spark) SQL, even with extensions. The results are pretty-printed for ease of read."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 1.1218500137329102 ms\n",
      "{\"store\": \"Angola\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"AndorrA\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Antarctica\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"American Samoa\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Afghanistan\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Antigua and Barbuda\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Albania\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Armenia\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Anguilla\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Argentina\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"land Islands\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Algeria\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Bangladesh\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Belgium\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Bermuda\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Bahamas\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Aruba\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Bahrain\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Australia\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Belarus\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Austria\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Belize\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Barbados\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Azerbaijan\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Benin\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Bhutan\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Botswana\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Bulgaria\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"British Indian Ocean Territory\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Brazil\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Bolivia\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Bosnia and Herzegovina\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Brunei Darussalam\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Cambodia\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Burkina Faso\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Burundi\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Bouvet Island\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Congo\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Chad\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Canada\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Cayman Islands\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Colombia\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Chile\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Comoros\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Cameroon\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Central African Republic\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Christmas Island\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Cape Verde\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"China\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Cocos (Keeling) Islands\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Cote D\\\"Ivoire\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Cyprus\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Czech Republic\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Congo, The Democratic Republic of the\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Cuba\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Cook Islands\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Dominica\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Croatia\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Dominican Republic\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Denmark\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Djibouti\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Costa Rica\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"El Salvador\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Faroe Islands\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Estonia\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Fiji\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Ecuador\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Eritrea\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"France\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Equatorial Guinea\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"French Guiana\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Ethiopia\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Finland\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Falkland Islands (Malvinas)\", \"available products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"store\": \"Egypt\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"French Southern Territories\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Greece\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Guadeloupe\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Gambia\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Greenland\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Gibraltar\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Georgia\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"French Polynesia\", \"available products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"store\": \"Gabon\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Germany\", \"available products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"store\": \"Ghana\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Grenada\", \"available products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"store\": \"Haiti\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Hong Kong\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Heard Island and Mcdonald Islands\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Guam\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Holy See (Vatican City State)\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Guatemala\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Iceland\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Guinea-Bissau\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Honduras\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Guernsey\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Hungary\", \"available products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"store\": \"Guyana\", \"available products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"store\": \"Guinea\", \"available products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"store\": \"Jordan\", \"available products\": []}\n",
      "{\"store\": \"Japan\", \"available products\": []}\n",
      "{\"store\": \"Israel\", \"available products\": []}\n",
      "{\"store\": \"Iran, Islamic Republic Of\", \"available products\": []}\n",
      "{\"store\": \"Iraq\", \"available products\": []}\n",
      "{\"store\": \"Isle of Man\", \"available products\": []}\n",
      "{\"store\": \"Jersey\", \"available products\": []}\n",
      "{\"store\": \"Ireland\", \"available products\": []}\n",
      "{\"store\": \"Italy\", \"available products\": []}\n",
      "{\"store\": \"India\", \"available products\": []}\n",
      "{\"store\": \"Indonesia\", \"available products\": []}\n",
      "{\"store\": \"Jamaica\", \"available products\": []}\n",
      "{\"store\": \"Kazakhstan\", \"available products\": []}\n",
      "{\"store\": \"Kyrgyzstan\", \"available products\": []}\n",
      "{\"store\": \"Korea, Democratic People\\\"S Republic of\", \"available products\": []}\n",
      "{\"store\": \"Liberia\", \"available products\": []}\n",
      "{\"store\": \"Kenya\", \"available products\": []}\n",
      "{\"store\": \"Kiribati\", \"available products\": []}\n",
      "{\"store\": \"Lao People\\\"S Democratic Republic\", \"available products\": []}\n",
      "{\"store\": \"Lesotho\", \"available products\": []}\n",
      "{\"store\": \"Kuwait\", \"available products\": []}\n",
      "{\"store\": \"Libyan Arab Jamahiriya\", \"available products\": []}\n",
      "{\"store\": \"Lebanon\", \"available products\": []}\n",
      "{\"store\": \"Korea, Republic of\", \"available products\": []}\n",
      "{\"store\": \"Latvia\", \"available products\": []}\n",
      "{\"store\": \"Macedonia, The Former Yugoslav Republic of\", \"available products\": []}\n",
      "{\"store\": \"Liechtenstein\", \"available products\": []}\n",
      "{\"store\": \"Malta\", \"available products\": []}\n",
      "{\"store\": \"Luxembourg\", \"available products\": []}\n",
      "{\"store\": \"Madagascar\", \"available products\": []}\n",
      "{\"store\": \"Malawi\", \"available products\": []}\n",
      "{\"store\": \"Lithuania\", \"available products\": []}\n",
      "{\"store\": \"Maldives\", \"available products\": []}\n",
      "{\"store\": \"Marshall Islands\", \"available products\": []}\n",
      "{\"store\": \"Mali\", \"available products\": []}\n",
      "{\"store\": \"Macao\", \"available products\": []}\n",
      "{\"store\": \"Malaysia\", \"available products\": []}\n",
      "{\"store\": \"Morocco\", \"available products\": []}\n",
      "{\"store\": \"Moldova, Republic of\", \"available products\": []}\n",
      "{\"store\": \"Monaco\", \"available products\": []}\n",
      "{\"store\": \"Micronesia, Federated States of\", \"available products\": []}\n",
      "{\"store\": \"Mozambique\", \"available products\": []}\n",
      "{\"store\": \"Mongolia\", \"available products\": []}\n",
      "{\"store\": \"Mauritania\", \"available products\": []}\n",
      "{\"store\": \"Martinique\", \"available products\": []}\n",
      "{\"store\": \"Mexico\", \"available products\": []}\n",
      "{\"store\": \"Mayotte\", \"available products\": []}\n",
      "{\"store\": \"Mauritius\", \"available products\": []}\n",
      "{\"store\": \"Montenegro\", \"available products\": []}\n",
      "{\"store\": \"Montserrat\", \"available products\": []}\n",
      "{\"store\": \"Netherlands\", \"available products\": []}\n",
      "{\"store\": \"New Caledonia\", \"available products\": []}\n",
      "{\"store\": \"Nigeria\", \"available products\": []}\n",
      "{\"store\": \"New Zealand\", \"available products\": []}\n",
      "{\"store\": \"Nauru\", \"available products\": []}\n",
      "{\"store\": \"Netherlands Antilles\", \"available products\": []}\n",
      "{\"store\": \"Niger\", \"available products\": []}\n",
      "{\"store\": \"Nepal\", \"available products\": []}\n",
      "{\"store\": \"Niue\", \"available products\": []}\n",
      "{\"store\": \"Myanmar\", \"available products\": []}\n",
      "{\"store\": \"Nicaragua\", \"available products\": []}\n",
      "{\"store\": \"Namibia\", \"available products\": []}\n",
      "{\"store\": \"Pakistan\", \"available products\": []}\n",
      "{\"store\": \"Philippines\", \"available products\": []}\n",
      "{\"store\": \"Panama\", \"available products\": []}\n",
      "{\"store\": \"Palau\", \"available products\": []}\n",
      "{\"store\": \"Papua New Guinea\", \"available products\": []}\n",
      "{\"store\": \"Oman\", \"available products\": []}\n",
      "{\"store\": \"Northern Mariana Islands\", \"available products\": []}\n",
      "{\"store\": \"Pitcairn\", \"available products\": []}\n",
      "{\"store\": \"Norfolk Island\", \"available products\": []}\n",
      "{\"store\": \"Paraguay\", \"available products\": []}\n",
      "{\"store\": \"Peru\", \"available products\": []}\n",
      "{\"store\": \"Norway\", \"available products\": []}\n",
      "{\"store\": \"Palestinian Territory, Occupied\", \"available products\": []}\n",
      "{\"store\": \"Saint Helena\", \"available products\": []}\n",
      "{\"store\": \"Saint Pierre and Miquelon\", \"available products\": []}\n",
      "{\"store\": \"Romania\", \"available products\": []}\n",
      "{\"store\": \"Puerto Rico\", \"available products\": []}\n",
      "{\"store\": \"Saint Kitts and Nevis\", \"available products\": []}\n",
      "{\"store\": \"Portugal\", \"available products\": []}\n",
      "{\"store\": \"Qatar\", \"available products\": []}\n",
      "{\"store\": \"Russian Federation\", \"available products\": []}\n",
      "{\"store\": \"Saint Lucia\", \"available products\": []}\n",
      "{\"store\": \"Poland\", \"available products\": []}\n",
      "{\"store\": \"Reunion\", \"available products\": []}\n",
      "{\"store\": \"RWANDA\", \"available products\": []}\n",
      "{\"store\": \"Sao Tome and Principe\", \"available products\": []}\n",
      "{\"store\": \"Slovakia\", \"available products\": []}\n",
      "{\"store\": \"Sierra Leone\", \"available products\": []}\n",
      "{\"store\": \"Saint Vincent and the Grenadines\", \"available products\": []}\n",
      "{\"store\": \"San Marino\", \"available products\": []}\n",
      "{\"store\": \"Senegal\", \"available products\": []}\n",
      "{\"store\": \"Singapore\", \"available products\": []}\n",
      "{\"store\": \"Saudi Arabia\", \"available products\": []}\n",
      "{\"store\": \"Solomon Islands\", \"available products\": []}\n",
      "{\"store\": \"Slovenia\", \"available products\": []}\n",
      "{\"store\": \"Serbia\", \"available products\": []}\n",
      "{\"store\": \"Seychelles\", \"available products\": []}\n",
      "{\"store\": \"Samoa\", \"available products\": []}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $store in  json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "let $product :=  json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "                [$store.sid eq $$.store-number]\n",
    "return {\n",
    "  \"store\" : $store.country,\n",
    "  \"available products\" : [\n",
    "   distinct-values($product.product)\n",
    " ]\n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where clauses\n",
    "\n",
    "\n",
    "Where clauses are used to filter variable bindings (tuples) based on a predicate on these variables. They are the equivalent to a WHERE clause in SQL.\n",
    "This is a simple example of its use in conjunction with a for clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.014545917510986328 ms\n",
      "{\"number\": 8, \"square\": 64}\n",
      "{\"number\": 9, \"square\": 81}\n",
      "{\"number\": 10, \"square\": 100}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 10\n",
    "where $x gt 7\n",
    "return {\n",
    "  \"number\": $x,\n",
    "  \"square\": $x * $x\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A where clause can appear anywhere in a FLWOR expression, ex- cept that it cannot be the first clause (always for or let) or the last clause (always return)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.021066904067993164 ms\n",
      "{\"number\": 8, \"y\": 64}\n",
      "{\"number\": 8, \"y\": 65}\n",
      "{\"number\": 9, \"y\": 81}\n",
      "{\"number\": 9, \"y\": 82}\n",
      "{\"number\": 10, \"y\": 100}\n",
      "{\"number\": 10, \"y\": 101}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 10\n",
    "let $square := $x * $x\n",
    "where $square > 60\n",
    "for $y in $square to $square + 1\n",
    "return {\n",
    "  \"number\": $x,\n",
    "  \"y\": $y\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A where clause always outputs a subset (or all) of its incoming tuples, without any alteration. In the case that the predicate always evaluates to true, it forwards all tuples, as if there had been no where clause at all. In the case that the predicate always evaluates to false, it outputs no tuple and the FLWOR expression will then return the empty sequence, with no need to further evaluate any of the remaining clauses.\n",
    "Here is another example of use of the where clause with our datasets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 1.835021734237671 ms\n",
      "\"Warning! The output sequence contains 1000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n",
      "\"broiler\"\n",
      "\"shirt\"\n",
      "\"toaster\"\n",
      "\"phone\"\n",
      "\"blender\"\n",
      "\"tv\"\n",
      "\"socks\"\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "let $store := json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "                [$$.sid eq $product.store-number]\n",
    "where $store.country = \"Germany\"\n",
    "return $product.product"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Order by clauses\n",
    "\n",
    "Order by clauses are used to reorganize the order of the tuples, but without altering them. They are the same as ORDER BY clauses in SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.01975703239440918 ms\n",
      "{\"number\": 0, \"square\": 0}\n",
      "{\"number\": -1, \"square\": 1}\n",
      "{\"number\": 1, \"square\": 1}\n",
      "{\"number\": -2, \"square\": 4}\n",
      "{\"number\": 2, \"square\": 4}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in -2 to 2\n",
    "let $square := $x * $x\n",
    "order by $square\n",
    "return {\n",
    "  \"number\": $x,\n",
    "  \"square\": $square\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is also possible, like in SQL, to specify an ascending or a descend- ing order. By default, the order is ascending.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.013797283172607422 ms\n",
      "{\"number\": 0, \"square\": 0}\n",
      "{\"number\": -1, \"square\": 1}\n",
      "{\"number\": 1, \"square\": 1}\n",
      "{\"number\": -2, \"square\": 4}\n",
      "{\"number\": 2, \"square\": 4}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in -2 to 2\n",
    "let $square := $x * $x\n",
    "order by $square ascending\n",
    "return {\n",
    "  \"number\": $x,\n",
    "  \"square\": $square\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.01792001724243164 ms\n",
      "{\"number\": -2, \"square\": 4}\n",
      "{\"number\": 2, \"square\": 4}\n",
      "{\"number\": -1, \"square\": 1}\n",
      "{\"number\": 1, \"square\": 1}\n",
      "{\"number\": 0, \"square\": 0}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in -2 to 2\n",
    "let $square := $x * $x\n",
    "order by $square descending\n",
    "return {\n",
    "  \"number\": $x,\n",
    "  \"square\": $square\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In case of ties between tuples, the order is arbitrary. But it is possible to sort on another variable in case there is a tie with the first one (compound sorting keys):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.014029979705810547 ms\n",
      "{\"number\": -2, \"square\": 4}\n",
      "{\"number\": 2, \"square\": 4}\n",
      "{\"number\": -1, \"square\": 1}\n",
      "{\"number\": 1, \"square\": 1}\n",
      "{\"number\": 0, \"square\": 0}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in -2 to 2\n",
    "let $square := $x * $x\n",
    "order by $square descending, $x ascending return {\n",
    "   \"number\": $x,\n",
    "   \"square\": $square\n",
    " }\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is possible to control what to do with empty sequences: they can be considered smallest or greatest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.014728307723999023 ms\n",
      "[1]\n",
      "[3]\n",
      "[5]\n",
      "[]\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 5\n",
    "let $y := $x[$$ mod 2 = 1]\n",
    "order by $y ascending empty greatest\n",
    "return [ $y ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.013755083084106445 ms\n",
      "[]\n",
      "[]\n",
      "[1]\n",
      "[3]\n",
      "[5]\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 5\n",
    "let $y := $x[$$ mod 2 = 1]\n",
    "order by $y ascending empty least\n",
    "return [ $y ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is another example of use of the order by clause with our datasets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 4.2881550788879395 ms\n",
      "\"Warning! The output sequence contains 100000 items but its materialization was capped at 200 items. This value can be configured with the result-size parameter in the query string of the HTTP request.\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n",
      "\"tv\"\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "let $store := json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "                [$$.sid eq $product.store-number]\n",
    "order by count($store) descending,\n",
    "         string-length($product.product) ascending\n",
    "return $product.product"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group by clauses\n",
    "\n",
    "Group by clauses organize tuples in groups based on matching keys, and then output only one tuple for each group, aggregating other variables (count, sum, max, min...). This is similar to GROUP BY clauses in SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.01671290397644043 ms\n",
      "{\"grouping key\": 0, \"count of x\": 2}\n",
      "{\"grouping key\": 1, \"count of x\": 3}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 5\n",
    "let $y := $x mod 2\n",
    "group by $y\n",
    "return {\n",
    "  \"grouping key\" : $y,\n",
    "  \"count of x\" : count($x)\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, JSONiq’s group by clauses are more powerful and expres- sive than SQL GROUP BY clauses: indeed, it is also possible to opt out of aggregating other (non-grouping-key) variables. Then, for a non- aggregated variable, the sequence of all its values of within a group will be rebound to this same variable as a single binding in the outcoming tuple. It is thus possible to write many more queries than SQL would allow, which is one of the reasons why a language like JSONiq should be preferred for nested datasets.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 0.01664900779724121 ms\n",
      "{\"grouping key\": 0, \"grouped x values\": [2, 4]}\n",
      "{\"grouping key\": 1, \"grouped x values\": [1, 3, 5]}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $x in 1 to 5\n",
    "let $y := $x mod 2\n",
    "group by $y\n",
    "return {\n",
    "  \"grouping key\" : $y,\n",
    "  \"grouped x values\" : [ $x ]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, here is an example of use of a group by clause with our example dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Took: 3.1400740146636963 ms\n",
      "{\"sid\": 64, \"country\": \"Egypt\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 68, \"country\": \"Estonia\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 42, \"country\": \"Central African Republic\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 83, \"country\": \"Gibraltar\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 54, \"country\": \"Cote D\\\"Ivoire\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 96, \"country\": \"Holy See (Vatican City State)\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 82, \"country\": \"Ghana\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 78, \"country\": \"Gabon\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 41, \"country\": \"Cayman Islands\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 89, \"country\": \"Guatemala\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 62, \"country\": \"Dominican Republic\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 86, \"country\": \"Grenada\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 58, \"country\": \"Czech Republic\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 66, \"country\": \"Equatorial Guinea\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 70, \"country\": \"Falkland Islands (Malvinas)\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 91, \"country\": \"Guinea\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 100, \"country\": \"Iceland\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 49, \"country\": \"Comoros\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 14, \"country\": \"Australia\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 88, \"country\": \"Guam\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 97, \"country\": \"Honduras\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 67, \"country\": \"Eritrea\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 15, \"country\": \"Austria\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 12, \"country\": \"Armenia\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 11, \"country\": \"Argentina\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 4, \"country\": \"Algeria\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 74, \"country\": \"France\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 92, \"country\": \"Guinea-Bissau\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 63, \"country\": \"Ecuador\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 5, \"country\": \"American Samoa\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 19, \"country\": \"Bangladesh\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 10, \"country\": \"Antigua and Barbuda\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 2, \"country\": \"land Islands\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 37, \"country\": \"Cambodia\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 59, \"country\": \"Denmark\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 73, \"country\": \"Finland\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 7, \"country\": \"Angola\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 61, \"country\": \"Dominica\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 94, \"country\": \"Haiti\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 56, \"country\": \"Cuba\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 1, \"country\": \"Afghanistan\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 79, \"country\": \"Gambia\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 9, \"country\": \"Antarctica\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 17, \"country\": \"Bahamas\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 23, \"country\": \"Belize\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 40, \"country\": \"Cape Verde\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 50, \"country\": \"Congo\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 16, \"country\": \"Azerbaijan\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 48, \"country\": \"Colombia\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 75, \"country\": \"French Guiana\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 32, \"country\": \"British Indian Ocean Territory\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 39, \"country\": \"Canada\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 76, \"country\": \"French Polynesia\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 93, \"country\": \"Guyana\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 80, \"country\": \"Georgia\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 36, \"country\": \"Burundi\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 53, \"country\": \"Costa Rica\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 31, \"country\": \"Brazil\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 98, \"country\": \"Hong Kong\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 45, \"country\": \"China\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 38, \"country\": \"Cameroon\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 33, \"country\": \"Brunei Darussalam\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 60, \"country\": \"Djibouti\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 29, \"country\": \"Botswana\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 95, \"country\": \"Heard Island and Mcdonald Islands\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 47, \"country\": \"Cocos (Keeling) Islands\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 69, \"country\": \"Ethiopia\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 25, \"country\": \"Bermuda\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 13, \"country\": \"Aruba\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 18, \"country\": \"Bahrain\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 84, \"country\": \"Greece\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 34, \"country\": \"Bulgaria\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 51, \"country\": \"Congo, The Democratic Republic of the\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 46, \"country\": \"Christmas Island\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 71, \"country\": \"Faroe Islands\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 99, \"country\": \"Hungary\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 52, \"country\": \"Cook Islands\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 90, \"country\": \"Guernsey\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 24, \"country\": \"Benin\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 8, \"country\": \"Anguilla\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 77, \"country\": \"French Southern Territories\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 28, \"country\": \"Bosnia and Herzegovina\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 85, \"country\": \"Greenland\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 6, \"country\": \"AndorrA\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 3, \"country\": \"Albania\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 44, \"country\": \"Chile\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 55, \"country\": \"Croatia\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 81, \"country\": \"Germany\", \"products\": [\"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\"]}\n",
      "{\"sid\": 72, \"country\": \"Fiji\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 21, \"country\": \"Belarus\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 22, \"country\": \"Belgium\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 57, \"country\": \"Cyprus\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 87, \"country\": \"Guadeloupe\", \"products\": [\"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\"]}\n",
      "{\"sid\": 43, \"country\": \"Chad\", \"products\": [\"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\"]}\n",
      "{\"sid\": 20, \"country\": \"Barbados\", \"products\": [\"blender\", \"tv\", \"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\"]}\n",
      "{\"sid\": 65, \"country\": \"El Salvador\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n",
      "{\"sid\": 27, \"country\": \"Bolivia\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 26, \"country\": \"Bhutan\", \"products\": [\"socks\", \"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\"]}\n",
      "{\"sid\": 35, \"country\": \"Burkina Faso\", \"products\": [\"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\", \"broiler\"]}\n",
      "{\"sid\": 30, \"country\": \"Bouvet Island\", \"products\": [\"broiler\", \"shirt\", \"toaster\", \"phone\", \"blender\", \"tv\", \"socks\"]}\n"
     ]
    }
   ],
   "source": [
    "%%jsoniq\n",
    "for $product in json-file(\"http://www.rumbledb.org/samples/products-small.json\")\n",
    "group by $sid := $product.store-number\n",
    "order by $sid\n",
    "let $store := json-file(\"http://www.rumbledb.org/samples/countries.json\")\n",
    "                 [$$.sid eq $sid]\n",
    "return {|\n",
    "   $store,\n",
    "   { \"products\" : [ distinct-values($product.product) ] }\n",
    "|}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}