10 ms·
JSONiq: JSON Query Language
- great_wubwub 5y ago"The main language construct is called a FLWOR expression...an acronym for for, let, where, order by and return." They missed a huge opportunity to call it FLOWR (flow-er as in one who flows, or flower as in the thing that grows).
- FrontAid 5y agoNot sure where you got that quote from, but FLWOR is not something JSONiq invented. - https://en.wikipedia.org/wiki/FLWOR https://en.wikipedia.org/wiki/FLWOR - https://www.w3.org/TR/xquery-30/#id-flwor-expressions https://www.w3.org/TR/xquery-30/#id-flwor-expressions FLWOR is pronounced 'flower'.
- bestouff 5y agoEspecially when you hover over "group by" and the pop-up title says "FLWOR power" !
- bayesian_horse 5y agoIsn't every programming language a json query language? Particularly functional ones?
- capableweb 5y agoI can only think of one language that supports JSON "natively": JavaScript. For the rest of the languages, you usually need a parser (which, to be fair, JavaScript also needs, it just happens to come with the environment you use (browsers/NodeJS)) before you can actually do anything with the data.
- johnbeech 5y agoAgreed, I generally impress colleagues with how fast it is to solve problems with JSON / JS - the big advantage for me is being able to "dump" a portion of program memory to JSON, and then reload that into state of the application. It makes systems much easier to understand when you can peer inside the memory structures. I find by comparison that strongly typed languages end up with a huge amount of boiler plate and abstractions, you start to question which bits/bytes actually matter. JSON reduces a problem space to something meaningful and easily craftable by humans.
- jbverschoor 5y agoYou can eval() json.. That's where it came from
- michaelmior 5y agoYou can in JavaScript but you almost certainly don't want to when you can just use JSON.parse and avoid the possibility of the execution of arbitrary code.
- jbverschoor 5y agoI'm not saying it's a good idea. Javascript itself has many bad ideas. I'm just saying that's where it all started.
- dusted 5y agoI'd say yes, even the node repl is pretty comfortable for working with json, but I know it's popular to hate on js for not being typescript.
- gregwebs 5y agoSure, just like you don’t have to use SQL. JSONiq is strongly typed, so only the strongly typed languages are equivalent. The point of this project, like SQL, is to have something programming language agnostic that can be understood and optimized by a db or a tool that doesn’t use your programming language. It’s mostly designed for data analysis and data interchange.
- emmanueloga_ 5y agoThis answer will be a bit convoluted but hopefully has some interesting and related concepts ... First, from [1]: ... two representative [JSON] transformation tasks are considered ... The exercise demonstrates that the absence of parent or ancestor axes in the native representation of JSON means that the transformation task needs to be approached in a very different way. The article shows that the ability to "navigate upward" to the parent of a node can make certain queries and transformation easier to implement. BTW, as far as I can tell, JSONIq does not provide a way to "navigate upward" ... But! also from [1]: The ability to navigate upwards (and to a lesser extent, sideways, to preceding and following siblings) clearly has advantages and disadvantages. Without upwards navigation, a transformation process that operates primarily as a recursive tree walk cannot discover the context of leaf nodes (for example, when processing a price, what product does it relate to?), so this information needs to be passed down in the form of parameters. However, the convenience of being able to determine the context of a node comes at a significant price. I remember dealing with trees in pure functional languages and finding that typically implementing parent pointers can be tricky [2]. I think if we combine this two facts, we can see that not all languages make the same query or transformation tasks equally easy. 1: Page 167 of https://archive.xmlprague.cz/2016/files/xmlprague-2016-proceedings.pdf https://archive.xmlprague.cz/2016/files/xmlprague-2016-proce... 2: https://okmij.org/ftp/Scheme/xml.html#parent-ptr https://okmij.org/ftp/Scheme/xml.html#parent-ptr
- Felk 5y agoCan someone explain to me what the main differences to jq are, besides the syntax?
- baq 5y agolooks nothing like jq 1. let $stats := collection("stats") 2. for $access in $stats 3. group by $url := $access.url 4. return 5. { 6. "url": $url, 7. "avg": avg($access.response_time), 8. "hits": count($access) 9. }
- jansommer 5y agoSee also https://rumbledb.org https://rumbledb.org by same author
- oever 5y agoThere was a talk about RumbleDB and JSONiq at Declarative Amsterdam a few days ago. https://declarative.amsterdam/program https://declarative.amsterdam/program
- danellis 5y agoCurious syntax. Why does it require sigils? Why does assignment need both `let` and `:=`?
- emmanueloga_ 5y agoThe look and feel of this language is due to its close relationship with XQuery and the XML world, including the syntax. From [1]: XQuery 3.1 was designed with the goal to support additional data structures (maps, arrays) in memory. These structures are mapped to JSON for input and output. XQuery 3.1 has been a W3C recommendation since March 2017. JSONiq was designed with the goal of querying and updating JSON in settings such as document stores. It was also designed by members of the XML Query working group (disclaimer: I am one of them) while investigating various possibilities to support JSON. 1: https://stackoverflow.com/questions/44919443/what-are-the-differences-between-jsoniq-and-xquery-3-1 https://stackoverflow.com/questions/44919443/what-are-the-di...
- oever 5y agoIt's derived from XQuery, a functional language for querying and creating XML and JSON. (I realize that that's not an answer to your question.)
- lichtenberger 5y agoIf you don't want both, then use https://github.com/sirixdb/brackit https://github.com/sirixdb/brackit for instance :)
- ghislainfourny 5y agoThank you for your question. This is, in fact, not an assignment, but a variable binding that is highly optimizable by an execution engine. The let clause is part of the FLWOR expression, works in orchestration with for, where, group by, order by, count, return; the ability to bind variables while doing relational algebra is a feature that is often missed in SQL. JSONiq is functional and, in its core (non scripting) version, does not allow modifying variable values.
- ainar-g 5y agoRelated: * RFC 6901 JavaScript Object Notation (JSON) Pointer: https://datatracker.ietf.org/doc/html/rfc6901 https://datatracker.ietf.org/doc/html/rfc6901 * JSONPath RFC Draft: https://www.rfc-editor.org/rfc/internet-drafts/draft-ietf-jsonpath-base-02.html https://www.rfc-editor.org/rfc/internet-drafts/draft-ietf-js... * jq utility query language: https://stedolan.github.io/jq/manual/ https://stedolan.github.io/jq/manual/
- ABraidotti 5y agoDon't forget Gron: https://github.com/tomnomnom/gron https://github.com/tomnomnom/gron
- Aaargh20318 5y agoJSONata: https://jsonata.org/ https://jsonata.org/
- dariosalvi78 5y agosomewhat similar to the Arango Query Language as well: https://www.arangodb.com/docs/stable/aql/ https://www.arangodb.com/docs/stable/aql/
- seumars 5y agoAlso: * GROQ: https://groq.dev https://groq.dev
- sparsely 5y ago> Unlike SQL, which can only manipulate normalized data, JSONiq natively works on the entire normalization spectrum: textual, heterogeneous, deeply nested Maybe I'm misunderstanding something, but isn't SQL perfectly capable of acting on denormalized data? We often talk about the degree of normalization that a RDMS has. Perhaps this is in reference to nested data structures (which would normally be represented in RDMS via FK relations), but even there it's implementation dependent.
- chippiewill 5y ago> Perhaps this is in reference to nested data structures If you have nested data structures then it violates 1NF, i.e. the data isn't normalised. Any "pure" RDBMS won't even be able to represent that denormalised data in a table, let alone query it with SQL. Normally this is worked around by straight up serialising it. Obviously some implementations have extensions, like Postgres has JSON columns.
- e-master 5y agoTo be honest, I’m unsure how many ‘pure’ RDBMS are out there, and doesn’t seem like the parent comment had them in mind. I’d rather suggest that mainstream RDBMS in general don’t have trouble working with data formats such as json or xml. SQL Server for one has no trouble querying into deeply nested XML/JSON columns and it does it surprisingly quickly based on my experience.
- maweki 5y agoAs there are various degrees of "not normalized", this was a very valid question (and answer). SQL databases do indeed work with data not in Boyce-Codd-Normal-Form. :D
- user3939382 5y agoYes, MySQL also has functions dedicated to traversing and selecting JSON
- ghislainfourny 5y agoThank you for your comment. There exist indeed recent extensions of SQL that add support for denormalized data (arrays, objects), for example in Spark SQL and PostgreSQL, however SQL was originally designed for tables and it remains cumbersome to write complex queries on denormalized data (lateral views, etc). A deeper and more detailed analysis of query languages for nested data can be found in our recent paper with a concrete use case in high energy physics, to be presented at VLDB 2022: https://arxiv.org/abs/2104.12615 https://arxiv.org/abs/2104.12615
- ur-whale 5y agoI see dollar signs for variable names in there ... it is 2021 ... have we learnt nothing?
- gizdan 5y agoWhat's wrong with dollar signs for variable names?
- ivanche 5y agoI'd argue it's not dollar signs for variable names but dollar signs in variable names.
- HelloNurse 5y agoAs you should expect, there's a reason: distinguishing variables of the script from names within the JSON document and from numerous keywords and builtin stuff. Variables get a sigil because in normal code they are a minority of the names.
- jolmg 5y agoYup. Same with shells. Sigils on variables are so we don't need to quote every argument/option on every command. And we don't want to quote our strings in the shell because different to other programming languages, shells are for interactive use and so string literals are far more frequent than variables.
- traceroute66 5y agoHow does this differ from JMESPath[1] ? [1] https://jmespath.org/ https://jmespath.org/
- Mariehane 5y agoLooks to me like JMESPath is more similar to XPath, while JSONiq uses statements like 'let', 'for', etc. which are more similar to SQL (and seem more readable to me).
- vjust 5y agoI found it awkward to inject perl-like $variables in the middle of a Python notebook. The association with anything-XML also is a downer personally for me, its also a goodbye to lightweight. Yup, this maybe a superficial observation, but the $ in a python-script? Jq works, both in python, and from the shell prompt. https://pypi.org/project/jq/ https://pypi.org/project/jq/
- Mariehane 5y agoI took Ghislain Fourny's Big Data course at ETH last year and found working with JSONiq quite pleasant. I think it makes writing complex queries quite simple, and way more readable than trying to do the same in a query language like SQL, XPath, etc, and since json seems to be fairly standard it is also useable out-of-the-box for a lot of data "in the wild". It is my impression that the tooling is a bit difficult to setup though. Another thing is that it supposedly can scale to absolutely massive datasets, since there is a Rumbe/Hadoop backend.
- heresie-dabord 5y agoIt's interesting to watch solutions and anti-patterns repeat in history ("the time-series of Reality"). Structuring data is an art and a skill. Perhaps not taught very well. There is a low-cost solution: CSV, TSV, lines of text. The high-cost solution is relational structure and servers. In the middle range, with risk of expensive tooling and glaring anti-patterns, are XML and JSON. The latter can both be used simply or with grotesque opacity. A some point, feeding the monster becomes a main activity of the village. At which point, the villagers elect a new monster with their shovels and pitchforks.
- rendall 5y agoI'm not exactly sure what you're talking about, but I like it / am scared by it
- ocrb 5y agoWas the same for me. Like others here, I was quite sceptical because I use Python / JS on a daily basis as a JSON query language, but after I got used to it, I really liked the language and think that it could increase my productivity if I would use it more often.
- justsomeuser 5y agoI already have a JSON query language - it’s JavaScript. > Queries are 80% shorter than imperative code From the examples they look the same length as the corresponding JS I would write, maybe slightly longer. What are the advantages to using this language over JS? JS has a fast JIT which would make filtering data fast. How does this query language compare in performance? Does it have indexes?
- markstos 5y agoGood points. JSON processing tools I find helpful in addition to JavaScript include Gron aka "grep for JavaScript" ( https://github.com/tomnomnom/gron https://github.com/tomnomnom/gron ), jq for querying, json_pp for pretty-printing and `bat` for syntax highlighting.
- Mariehane 5y agoThis does some clever stuff in the background, e.g. query optimization and performing the computations of a 'for'-loop concurrently (potentially over multiple machines in a cluster). So in my experience there was a bit of latency for the query to execute, but then it executes faster and on much larger datasets than regular JS could handle. Just like how you could easily manipulate tabular data using numpy or pandas (or excel), but SQL allows you to do it declaratively, which has benefits in some cases.
- mr_toad 5y agoCouchDB has been running distributed Map/Reduce queries on JSON data for over a decade. It’ll be interesting to see how this compares.
- marstall 5y agoit's just less code - instead of writing a series of buggy loops with null checks and string concatenations all over the place, you just writing a line of declaration and it deals with all that for you.
- ghislainfourny 5y agoThank you for your comment. The motivation for JSONiq and RumbleDB is discussed in this recent paper, the core argument is data independence: https://arxiv.org/abs/1910.11582v3 https://arxiv.org/abs/1910.11582v3 JSONiq is a functional language and thus makes it easy to scale to collections that have billions of objects. A notable difference with JavaScript is that JSONiq has the FLWOR expression, which is similar to, but more generic than, a SQL statement.
- lichtenberger 5y agoWe're working on an implementation for SirixDB with currently a slightly different syntax (for instance "=>" to dereference object fields). We're also supporting all kinds of temporal query enhancements (point in time queries, time travel queries...): https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix The query engine used is developed here (core implemented by Sebastian Bächle and his tudents). Ideally the backend can be any other data store as well. https://github.com/sirixdb/brackit https://github.com/sirixdb/brackit
- markstos 5y agoThe "Beginner's Tutorial" README has no code examples, and suggests installing Anaconda, Jupyter and an old version of Zorba to get started... beginning. https://github.com/ghislainfourny/jsoniq-tutorial https://github.com/ghislainfourny/jsoniq-tutorial Back button.
- lichtenberger 5y agohttps://nbviewer.org/github/ghislainfourny/jsoniq-tutorial/blob/master/JSONiq-tutorial.ipynb https://nbviewer.org/github/ghislainfourny/jsoniq-tutorial/b...
- ghislainfourny 5y agoThank you for your feedback. The tutorial was moved over to an interactive notebook that you can find here: https://colab.research.google.com/github/RumbleDB/rumble/blob/master/RumbleSandbox.ipynb https://colab.research.google.com/github/RumbleDB/rumble/blo... The easiest way to get started locally is described here: https://rumble.readthedocs.io/en/latest/Getting%20started/ https://rumble.readthedocs.io/en/latest/Getting%20started/ We no longer recommend the installation of Anaconda -- instead, the Spark tgz file can directly be downloaded, unzipped, and the bin subdirectory added to the PATH, which is considerably simpler. Likewise, the RumbleDB jar is just a download. Using the RumbleDB shell is the easiest to set up; Jupyter and the server require a bit of additional work. For a cluster, this is even easier because most cloud platforms can create one with the push of a button, and one only needs to download the RumbleDB jar on the remote machine and get started right away. Use of RumbleDB on a cluster is explained here: https://rumble.readthedocs.io/en/latest/Run%20on%20a%20cluster/ https://rumble.readthedocs.io/en/latest/Run%20on%20a%20clust...
- ghislainfourny 5y agoThere is also a docker version that should ease the process: https://hub.docker.com/r/rumbledb/rumble https://hub.docker.com/r/rumbledb/rumble
- marstall 5y agoif anyone's here looking for javascript-language json query-er, I've been using jsonata. found it deep and well-thought out, and not too hard to get my head around the syntax https://jsonata.org https://jsonata.org