SQL was designed for tables and does this job indisputably well. However, even adding dots, lateral joins, and variants, it was not made for nested, heterogeneous data and reaches its limits with many levels of nestedness or high sparsity/extra fields (aka, denormalized data, aka semi-structured data).
The underlying constructs behind JSONiq and XQuery (which are 99% the same, differing only on the "JSON finish"), in particular the FLWOR expressions (which support pipe-syntax-like clauses natively), were designed in a W3C standardization working group by some of the same experts who also contributed to or edited SQL.
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.
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:
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.
SQL was designed for tables and does this job indisputably well. However, even adding dots, lateral joins, and variants, it was not made for nested, heterogeneous data and reaches its limits with many levels of nestedness or high sparsity/extra fields (aka, denormalized data, aka semi-structured data).
The underlying constructs behind JSONiq and XQuery (which are 99% the same, differing only on the "JSON finish"), in particular the FLWOR expressions (which support pipe-syntax-like clauses natively), were designed in a W3C standardization working group by some of the same experts who also contributed to or edited SQL.
I explain the reasons why SQL is not appropriate for denormalized data in my MSc course at ETH: https://www.youtube.com/watch?v=WBe6MlCM9EY&list=PLs5KPrcFtb...
You will also find our VLDB conference paper on data independence for messy data here: https://www.vldb.org/pvldb/vol14/p498-muller.pdf
I hope it helps!