\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": [
"## JSON\n",
"\n",
"As explained on the [official JSON Web site](http://www.json.org/), JSON is a lightweight data-interchange format designed for humans as well as for computers. It supports as values:\n",
"- objects (string-to-value maps)\n",
"- arrays (ordered sequences of values)\n",
"- strings\n",
"- numbers\n",
"- booleans (true, false)\n",
"- null\n",
"\n",
"JSONiq provides declarative querying and updating capabilities on JSON data.\n",
"\n",
"## Elevator Pitch\n",
"\n",
"JSONiq is based on XQuery, which is a W3C standard (like XML and HTML). XQuery is a very powerful declarative language that originally manipulates XML data, but it turns out that it is also a very good fit for manipulating JSON natively.\n",
"JSONiq, since it extends XQuery, is a very powerful general-purpose declarative programming language. Our experience is that, for the same task, you will probably write about 80% less code compared to imperative languages like JavaScript, Python or Ruby. Additionally, you get the benefits of strong type checking without actually having to write type declarations.\n",
"Here is an appetizer before we start the tutorial from scratch.\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"\n",
"let $stores :=\n",
"[\n",
" { \"store number\" : 1, \"state\" : \"MA\" },\n",
" { \"store number\" : 2, \"state\" : \"MA\" },\n",
" { \"store number\" : 3, \"state\" : \"CA\" },\n",
" { \"store number\" : 4, \"state\" : \"CA\" }\n",
"]\n",
"let $sales := [\n",
" { \"product\" : \"broiler\", \"store number\" : 1, \"quantity\" : 20 },\n",
" { \"product\" : \"toaster\", \"store number\" : 2, \"quantity\" : 100 },\n",
" { \"product\" : \"toaster\", \"store number\" : 2, \"quantity\" : 50 },\n",
" { \"product\" : \"toaster\", \"store number\" : 3, \"quantity\" : 50 },\n",
" { \"product\" : \"blender\", \"store number\" : 3, \"quantity\" : 100 },\n",
" { \"product\" : \"blender\", \"store number\" : 3, \"quantity\" : 150 },\n",
" { \"product\" : \"socks\", \"store number\" : 1, \"quantity\" : 500 },\n",
" { \"product\" : \"socks\", \"store number\" : 2, \"quantity\" : 10 },\n",
" { \"product\" : \"shirt\", \"store number\" : 3, \"quantity\" : 10 }\n",
"]\n",
"let $join :=\n",
" for $store in $stores[], $sale in $sales[]\n",
" where $store.\"store number\" = $sale.\"store number\"\n",
" return {\n",
" \"nb\" : $store.\"store number\",\n",
" \"state\" : $store.state,\n",
" \"sold\" : $sale.product\n",
" }\n",
"return [$join]\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## All JSON values are JSONiq, too\n",
"\n",
"The first thing you need to know is that a well-formed JSON document is a JSONiq expression as well.\n",
"This means that you can copy-and-paste any JSON document into a query. The following are JSONiq queries that are \"idempotent\" (they just output themselves):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"{ \"pi\" : 3.14, \"sq2\" : 1.4 }"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"[ 2, 3, 5, 7, 11, 13 ]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"{\n",
" \"operations\" : [\n",
" { \"binary\" : [ \"and\", \"or\"] },\n",
" { \"unary\" : [\"not\"] }\n",
" ],\n",
" \"bits\" : [\n",
" 0, 1\n",
" ]\n",
" }"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"[ { \"Question\" : \"Ultimate\" }, [\"Life\", \"the universe\", \"and everything\"] ]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This works with objects, arrays (even nested), strings, numbers, booleans, null.\n",
"\n",
"It also works the other way round: if your query outputs an object, you can use it as a JSON document.\n",
"JSONiq is a declarative language. This means that you only need to say what you want - the compiler will take care of the how. \n",
"\n",
"In the above queries, you are basically saying: I want to output this JSON content, and here it is."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In fact JSONiq makes JSON \"dynamic\": try to replace numbers with arithmetic formulas, keys with concatenations of strings, etc and see how the resulting JSON object is automatically created."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"{\n",
" \"foo\" : 2 + 2,\n",
" \"foo\" || \"bar\" : if(2 gt 1) then true else false\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Navigating an existing JSON dataset\n",
"\n",
"Next, 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": [
"# Variables"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some of the queries seen previously involve several chained lookups and function calls. It can become complex"
]
},
{
"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": [
"It is then a natural thing to use variables to store intermediate results. This can be achieved with a series of let clauses. The final result is then put in a return clause."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $path := \"http://www.rumbledb.org/samples/git-archive-small.json\"\n",
"let $events := json-file($path)\n",
"let $actors := $events.actor\n",
"let $logins := $actors.login\n",
"let $distinct-logins := distinct-values($logins)\n",
"return count($distinct-logins)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that types are not needed, however they exist! It is possible to add a static type to each variable.\n",
"Since values can be sequences, you can add suffixes for cardinality: * for a sequence of arbitrary length, ? for zero or one item, + for one or more items."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $path as string := \"http://www.rumbledb.org/samples/git-archive-small.json\"\n",
"let $events as object* := json-file($path)\n",
"let $actors as object* := $events.actor\n",
"let $logins as string* := $actors.login\n",
"let $distinct-logins as string* := distinct-values($logins)\n",
"let $count as integer := count($distinct-logins)\n",
"return $count"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As you can see, variables can be used to store single items, as well as enormous sequences. RumbleDB will automatically select the best way to evaluate your query.\n",
"\n",
"Note that it is possible to reuse variable names. However, these are not assignments: these are bindings. Reusing a variable name hides the previous binding."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $v as string := \"http://www.rumbledb.org/samples/git-archive-small.json\"\n",
"let $v as object* := json-file($v)\n",
"let $v as object* := $v.actor\n",
"let $v as string* := $v.login\n",
"let $v as string* := distinct-values($v)\n",
"let $v as integer := count($v)\n",
"return $v"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Iteration"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It is possible to iterate on the elements in a sequence, 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": [
"## Simple calculations"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let us now look closer arithmetics, comparison and logic expressions. They are particularly useful in a where clause or in a Boolean predicate, however these expressions can be used just about anywhere as this is a functional language."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Arithmetics"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"JSONiq works like a calculator and can do arithmetics with the four basic operations."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
" (38 + 2) div 2 + 11 * 2\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"(mind the division operator which is the \"div\" keyword. The slash operator has different semantics).\n",
"\n",
"Like JSON, JSONiq works with decimals and doubles:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
" 6.022e23 * 42"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"JSONiq also support modulos, integer division, and has a rich function library (trigonometry, logarithms, exponential, powers, etc)."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Comparison"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Values (numbers, strings, dates, etc) can be compared with the binary operators eq, ne, gt, ge, lt and le.\n",
"Let us change the comparison used in the where clause with other kinds."
]
},
{
"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": "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) eq 10\n",
" return $event\n",
")"
]
},
{
"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) ne 10\n",
" return $event\n",
")"
]
},
{
"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) le 10\n",
" return $event\n",
")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Why not = or < or >=? This is because these are more powerful. In fact, they implicitly perform an existential quantification over the operands."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"1 to 10 = 5"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"1 to 10 > 11 to 20"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Logical operations\n",
"\n",
"JSONiq supports Boolean logic."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"true and false"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"(true or false) and (false or true)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The unary not is also available:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 243.537109375,
"end_time": 1557235032261.665
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"not true"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"Note that JSONiq, unlike SQL, does two-valued logic. Nulls are automatically converted to false."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"null and true"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some non-Booleans can also get converted. For example, non-empty strings are converted to true and empty strings to false."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"not \"\""
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"not \"non empty\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Zero is converted to false, non-zero numbers to true."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"not 0"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"not 1e10"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Strings\n",
"\n",
"JSONiq is capable of manipulating strings as well, using functions:\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 21398.401123046875,
"end_time": 1557212587338.105
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"concat(\"Hello \", \"Captain \", \"Kirk\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 752.509033203125,
"end_time": 1557234001018.501
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"substring(\"Mister Spock\", 8, 5)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"JSONiq comes up with a rich string function library out of the box, inherited from its base language. These functions are listed [here](https://www.w3.org/TR/xpath-functions-30/) (actually, you will find many more for numbers, dates, etc).\n"
]
},
{
"cell_type": "markdown",
"metadata": {
"cell_status": {
"execute_time": {
"duration": 17337.39599609375,
"end_time": 1557230774662.657
}
}
},
"source": [
"\n",
"### Sequences\n",
"\n",
"Until now, we have only been working with single values (an object, an array, a number, a string, a boolean). JSONiq supports sequences of values. You can build a sequence using commas:\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
" (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 222241.63500976562,
"end_time": 1557387411306.792
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"1, true, 4.2e1, \"Life\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The \"to\" operator is very convenient, too:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 39579.559814453125,
"end_time": 1557387951481.842
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
" (1 to 100)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some functions even work on sequences:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 27601.31787109375,
"end_time": 1557171455208.987
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"sum(1 to 100)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 55527.44091796875,
"end_time": 1557229508578.007
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"string-join((\"These\", \"are\", \"some\", \"words\"), \"-\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 13343.739013671875,
"end_time": 1557224627704.526
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"count(10 to 20)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"cell_status": {
"execute_time": {
"duration": 19340.81201171875,
"end_time": 1557220260002.04
}
}
},
"outputs": [],
"source": [
"%%jsoniq\n",
"avg(1 to 100)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Unlike arrays, sequences are flat. The sequence (3) is identical to the integer 3, and (1, (2, 3)) is identical to (1, 2, 3)."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"and even filter out some values:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $sequence := 1 to 10\n",
"for $value in $sequence\n",
"let $square := $value * 2\n",
"where $square < 10\n",
"return $square"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that you can only iterate over sequences, not arrays. To iterate over an array, you can obtain the sequence of its values with the [] operator, like so:\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"[1, 2, 3][]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Conditions\n",
"\n",
"You can make the output depend on a condition with an if-then-else construct:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $x in 1 to 10\n",
"return if ($x < 5) then $x\n",
" else -$x"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that the else clause is required - however, it can be the empty sequence () which is often when you need if only the then clause is relevant to you."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Composability of Expressions\n",
"\n",
"Now that you know of a couple of elementary JSONiq expressions, you can combine them in more elaborate expressions. For example, you can put any sequence of values in an array:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"[ 1 to 10 ]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Or you can dynamically compute the value of object pairs (or their key):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"{\n",
" \"Greeting\" : (let $d := \"Mister Spock\"\n",
" return concat(\"Hello, \", $d)),\n",
" \"Farewell\" : string-join((\"Live\", \"long\", \"and\", \"prosper\"),\n",
" \" \")\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can dynamically generate object singletons (with a single pair):\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"{ concat(\"Integer \", 2) : 2 * 2 }"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"and then merge lots of them into a new object with the {| |} notation:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"{|\n",
" for $i in 1 to 10\n",
" return { concat(\"Square of \", $i) : $i * $i }\n",
"|}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## JSON Navigation\n",
"\n",
"Up to now, you have learnt how to compose expressions so as to do some computations and to build objects and arrays. It also works the other way round: if you have some JSON data, you can access it and navigate.\n",
"All you need to know is: JSONiq views\n",
"an array as an ordered list of values,\n",
"an object as a set of name/value pairs\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Objects\n",
"\n",
"You can use the dot operator to retrieve the value associated with a key. Quotes are optional, except if the key has special characters such as spaces. It will return the value associated thereto:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $person := {\n",
" \"first name\" : \"Sarah\",\n",
" \"age\" : 13,\n",
" \"gender\" : \"female\",\n",
" \"friends\" : [ \"Jim\", \"Mary\", \"Jennifer\"]\n",
"}\n",
"return $person.\"first name\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can also ask for all keys in an object:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $person := {\n",
" \"name\" : \"Sarah\",\n",
" \"age\" : 13,\n",
" \"gender\" : \"female\",\n",
" \"friends\" : [ \"Jim\", \"Mary\", \"Jennifer\"]\n",
"}\n",
"return { \"keys\" : [ keys($person)] }"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Arrays\n",
"\n",
"The [[]] operator retrieves the entry at the given position:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $friends := [ \"Jim\", \"Mary\", \"Jennifer\"]\n",
"return $friends[[1+1]]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It is also possible to get the size of an array:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $person := {\n",
" \"name\" : \"Sarah\",\n",
" \"age\" : 13,\n",
" \"gender\" : \"female\",\n",
" \"friends\" : [ \"Jim\", \"Mary\", \"Jennifer\"]\n",
"}\n",
"return { \"how many friends\" : size($person.friends) }"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Finally, the [] operator returns all elements in an array, as a sequence:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $person := {\n",
" \"name\" : \"Sarah\",\n",
" \"age\" : 13,\n",
" \"gender\" : \"female\",\n",
" \"friends\" : [ \"Jim\", \"Mary\", \"Jennifer\"]\n",
"}\n",
"return $person.friends[]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Relational Algebra\n",
"\n",
"Do you remember SQL's SELECT FROM WHERE statements? JSONiq inherits selection, projection and join capability from XQuery, too."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"let $stores :=\n",
"[\n",
" { \"store number\" : 1, \"state\" : \"MA\" },\n",
" { \"store number\" : 2, \"state\" : \"MA\" },\n",
" { \"store number\" : 3, \"state\" : \"CA\" },\n",
" { \"store number\" : 4, \"state\" : \"CA\" }\n",
"]\n",
"let $sales := [\n",
" { \"product\" : \"broiler\", \"store number\" : 1, \"quantity\" : 20 },\n",
" { \"product\" : \"toaster\", \"store number\" : 2, \"quantity\" : 100 },\n",
" { \"product\" : \"toaster\", \"store number\" : 2, \"quantity\" : 50 },\n",
" { \"product\" : \"toaster\", \"store number\" : 3, \"quantity\" : 50 },\n",
" { \"product\" : \"blender\", \"store number\" : 3, \"quantity\" : 100 },\n",
" { \"product\" : \"blender\", \"store number\" : 3, \"quantity\" : 150 },\n",
" { \"product\" : \"socks\", \"store number\" : 1, \"quantity\" : 500 },\n",
" { \"product\" : \"socks\", \"store number\" : 2, \"quantity\" : 10 },\n",
" { \"product\" : \"shirt\", \"store number\" : 3, \"quantity\" : 10 }\n",
"]\n",
"let $join :=\n",
" for $store in $stores[], $sale in $sales[]\n",
" where $store.\"store number\" = $sale.\"store number\"\n",
" return {\n",
" \"nb\" : $store.\"store number\",\n",
" \"state\" : $store.state,\n",
" \"sold\" : $sale.product\n",
" }\n",
"return [$join]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Access datasets\n",
"\n",
"RumbleDB can read input from many file systems and many file formats. If you are using our backend, you can only use json-doc() with any URI pointing to a JSON file and navigate it as you see fit. \n",
"\n",
"You can read data from your local disk, from S3, from HDFS, and also from the Web. For this tutorial, we'll read from the Web because, well, we are already on the Web.\n",
"\n",
"We have put a sample at http://rumbledb.org/samples/products-small.json that contains 100,000 small objects like:\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"json-file(\"http://rumbledb.org/samples/products-small.json\", 10)[1]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The second parameter to json-file, 10, indicates to RumbleDB that it should organize the data in ten partitions after downloading it, and process it in parallel. If you were reading from HDFS or S3, the parallelization of these partitions would be pushed down to the distributed file system.\n",
"\n",
"JSONiq supports the relational algebra. For example, you can do a selection with a where clause, like so:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"where $product.quantity ge 995\n",
"return $product"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"Notice that by default only the first 200 items are shown. In a typical setup, it is possible to output the result of a query to a distributed system, so it is also possible to output all the results if needed. In this case, however, as this is printed on your screen, it is more convenient not to materialize the entire sequence.\n",
"\n",
"For a projection, there is project():"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"where $product.quantity ge 995\n",
"return project($product, (\"store-number\", \"product\"))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can also page the results (like OFFSET and LIMIT in SQL) with a count clause and a where clause"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"where $product.quantity ge 995\n",
"count $c\n",
"where $c gt 10 and $c le 20\n",
"return project($product, (\"store-number\", \"product\"))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"JSONiq also supports grouping with a group by clause:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"group by $store-number := $product.store-number\n",
"return {\n",
" \"store\" : $store-number,\n",
" \"count\" : count($product)\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As well as ordering with an order by clause:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"group by $store-number := $product.store-number\n",
"order by $store-number ascending\n",
"return {\n",
" \"store\" : $store-number,\n",
" \"count\" : count($product)\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"JSONiq supports denormalized data, so you are not forced to aggregate after a grouping, you can also nest data like so:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"group by $store-number := $product.store-number\n",
"order by $store-number ascending\n",
"return {\n",
" \"store\" : $store-number,\n",
" \"products\" : [ distinct-values($product.product) ]\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Or"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%%jsoniq\n",
"for $product in json-file(\"http://rumbledb.org/samples/products-small.json\", 10)\n",
"group by $store-number := $product.store-number\n",
"order by $store-number ascending\n",
"return {\n",
" \"store\" : $store-number,\n",
" \"products\" : [ project($product[position() le 10], (\"product\", \"quantity\")) ],\n",
" \"inventory\" : sum($product.quantity)\n",
"}"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"That's it! You know the basics of JSONiq. Now you can also download the RumbleDB jar and run it on your own laptop. Or [on a Spark cluster, reading data from and to HDFS](https://rumble.readthedocs.io/en/latest/Run%20on%20a%20cluster/), etc.\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
}