JSONiq-tutorial-7.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Big Data HS 2024\n",
    "\n",
    "## JSONiq tutorial - week 7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the JSONiq tutorial for week 7.\n",
    "\n",
    "Do not forget to use localhost:8888 as the URL to make sure the notebook is accessed via docker! And if it does not work, you should delete all containers, images, and volumes, then try again with\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "````\n",
    "docker-compose up\n",
    "````"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like last week, junst run the cell below to connect the Jupyter notebook with RumbleDB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext rumbledb\n",
    "%env RUMBLEDB_SERVER=http://rumble:9090/jsoniq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Navigating an existing JSON dataset\n",
    "\n",
    "We continue with an existing dataset on the Web. Recall the following query, which opens the textual dataset as a sequence of strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are now going to logically simulate a MapReduce-like job that counts the number of occurences of each word.\n",
    "First, we can add a for clause to iterate over the line (and do nothing else, so the query is equivalent to the previous one)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "return $line"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we can tokenize the lines. For simplicity, we will use spaces. The builtin tokenize() functions splits strings into several strings and by default, does this based on space characters.\n",
    "\n",
    "Tokenizing each string in this way would correspond to the mapping phase of MapReduce (the value associated with each one of the words is implicitly 1)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "return tokenize($line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also bind an intermediate variable to each token for convenience."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "return $token"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can make the intermediate key-value pairs explicit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "let $pair := { $token : 1 }\n",
    "return $pair"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we can use a group by clause, which essentially handles the shuffling and groups all words together that are the same.\n",
    "\n",
    "After the group by clause, in each group, \\\\$t will be bound to the current token, and \\\\$pair (which precedes the group by) will now contain the *sequence* of all pairs with the current token as a key.\n",
    "\n",
    "Thus, a JSONiq group by clause is similar to a SQL GROUP BY clause, but it is more generic because of its ability to bind each non-key variable to the sequence of all its values within a group, with no obligation to aggregate.\n",
    "\n",
    "Note how we dynamically navigate to all the values in the sequence of pairs with \\\\$pair.\\\\$t, where \\\\$t is the current token and $pair contains all pairs with that token."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "let $pair := { $token : 1 }\n",
    "group by $t := keys($pair)[1]\n",
    "return \n",
    "{\n",
    "    $t : sum($pair.$t)\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can clean up a bit by binding the count with an intermediate variable like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "let $pair := { $token : 1 }\n",
    "group by $t := keys($pair)[1]\n",
    "let $count := sum($pair.$t)\n",
    "return \n",
    "{\n",
    "    $t : $count\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which allows us to sort by descending counts and spot the most common tokens. The order by clause is similar to the SQL ORDER BY clause and also offers the choice between ascending and descending."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "let $pair := { $token : 1 }\n",
    "group by $t := keys($pair)[1]\n",
    "let $count := sum($pair.$t)\n",
    "order by $count descending\n",
    "return \n",
    "{\n",
    "    $t : $count\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can simplify the query a bit, but this is because JSONiq is more high-level than MapReduce and does not force the use of keys!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "group by $t := $token\n",
    "order by count($token) descending\n",
    "return \n",
    "{\n",
    "    $t : count($token)\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also limit the size of the output with a count clause. This would be similar to the use of LIMIT and OFFSET clauses in SQL, but the filtering can be done more generally than SQL with a where clause.\n",
    "\n",
    "This is also an opportunity to say that the order of the clauses in JSONiq is very flexible and generic, whereas in SQL the clauses have to be in the order of SELECT FROM WHERE GROUP BY HAVING ORDER LIMIT OFFSet. In JSONiq, the only requirement is that the first clause is either a for or a let, and that the last clause is a return clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $line in unparsed-text-lines(\"https://www.rumbledb.org/samples/hamlet.txt\")\n",
    "for $token in tokenize($line)\n",
    "group by $t := $token\n",
    "order by count($token) descending\n",
    "count $c\n",
    "where $c le 10\n",
    "return \n",
    "{\n",
    "    $t : count($token)\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Try your own queries!\n",
    "\n",
    "This notebook is interactive. You can edit all queries above and also execute your own! We will show you more features every week."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "1+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "1+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "1+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "1+1"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}