JSONiq-tutorial-6.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Big Data HS 2024\n",
    "\n",
    "## JSONiq tutorial - week 6"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the JSONiq tutorial for week 6.\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",
    "Let us look at an existing dataset on the Web. We picked a [GitHub archive file](https://gharchive.org)\n",
    "that we stored for convenience at this location: https://www.rumbledb.org/samples/git-archive.json.\n",
    "\n",
    "Accessing a JSON dataset can be done in two ways depending on the exact format:\n",
    "\n",
    "- If this is a file that contains a single JSON object spread over multiple lines, use json-doc(URL).\n",
    "- If this is a file that contains one JSON object per line (JSON Lines), use json-file(URL).\n",
    "\n",
    "The GitHub archive dataset is in the JSON Lines format, so we open it with json-file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a large file and the previous query output 500 JSON objects. To look closer, let us start looking at just the first object with a number predicate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\")[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that there are nested objects and arrays. This is perfect for JSONiq. Let us now figure out all the keys used in this dataset with the keys() function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "keys(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\"))"
   ]
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").type"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like there are a lot of duplicates in there. Let us use distinct-values() to figure out all unique values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "distinct-values(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").type)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So we see that for the key \"type\", all values are strings and there are only... how many, by the way? Let us use count()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "count(distinct-values(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").type))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So there are 13. Note that count() works just as well on the entire dataset, to know how many objects there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "count(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us know look at nested objects. It seems the key \"actor\" has these, so let us now use the dot object lookup to find all these values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").actor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can chain dot object lookups to navigate further down, for example to logins. Let us figure out how many distinct logins there are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "count(distinct-values(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").actor.login))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The id field inside the actor object seems to be an integer. What is the highest value? The max() function also works at large scales, just like count() and also min(), avg() and sum()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "max(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").actor.id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alright, let us know look for nested arrays. There does not seem to have any inside the actor object, so let us try the key \"payload\". Let us just look at the first one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\")[1].payload"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we see that there is a nested array associated with key \"commits\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\")[1].payload.commits"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, there is only one object in this array. Is there, by any chance, any one of these arrays that has more than one commit? For this, we can use a Boolean predicate. Let us evaluate the predicate\n",
    "\n",
    "size($$) gt 1\n",
    "\n",
    "which uses the size function and the gt (greater than) comparison and where $$ is the current array being tested."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[size($$) gt 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us just take the first one to have more visibility."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[size($$) gt 1][1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can expand it to a sequence of objects using the [] array unboxing syntax."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[size($$) gt 1][1][]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also lookup a specific position, say, the second object, with the [[ ]] array lookup syntax."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[size($$) gt 1][1][[2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now, please hold for something awesome. We can unbox all arrays of the entire collection at the same time by just using the [] syntax on the entire dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are objects. It is all too tempting to navigate further down with more dot object-lookup syntax. All at the same time, obviously. Let us figure out how many unique emails there are in all commits of all events."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "count(distinct-values(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[].author.email))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, how many unique emails are there in first commits?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "count(distinct-values(json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\").payload.commits[[1]].author.email))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You have now learned how to navigate large JSON datasets with the dot object lookup syntax, the [] array unboxing syntax, the [[ ]] array lookup syntax, number predicates, and Boolean predicates.\n",
    "\n",
    "All of these work nicely on very large sequences, and you can chain them arbitrarily. In fact, this will all happen in parallel on the cores of your machine or even on a large cluster.\n",
    "\n",
    "You also saw how to aggregate large sequences of values with min, max, count, avg and sum.\n",
    "\n",
    "Finally, you saw how to eliminate duplicates with distinct-values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Iteration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the previous tutorial, we looked at let and return clauses.\n",
    "It is possible to iterate on the elements in a sequence with another clause: the for clause, like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $i in 1 to 10\n",
    "return $i * 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The sequence to iterator on can itself come from a dataset, such as the one we were using previously:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "for $event in json-file(\"http://www.rumbledb.org/samples/git-archive-small.json\")\n",
    "return size($event.payload.commits)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For clauses can be mixed with let clauses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "let $path := \"http://www.rumbledb.org/samples/git-archive-small.json\"\n",
    "for $event in json-file($path)\n",
    "let $commits := $event.payload.commits\n",
    "return size($commits)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And the results can also be nested in a more complex query: for example, let us compute the max of all these array sizes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "max(\n",
    "  let $path := \"http://www.rumbledb.org/samples/git-archive-small.json\"\n",
    "  for $event in json-file($path)\n",
    "  let $commits := $event.payload.commits\n",
    "  return size($commits)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A third kind of clause is the where clause: it allows you to filter events. Let us only keep those with more than 10 commits, and count them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%jsoniq\n",
    "count(\n",
    "  let $path := \"http://www.rumbledb.org/samples/git-archive-small.json\"\n",
    "  for $event in json-file($path)\n",
    "  let $commits := $event.payload.commits\n",
    "  where size($commits) gt 10\n",
    "  return $event\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
}