Page History

...

It's also possible to NOT use a cross join at all, but to nest the json extract statements in one long expression in the Select stanza. However, you lose the ability to include the ordinality of the values extracted. See Summary at the end of this page ("Three ways to extract arrays").

• Nested extract statements are applied from the outside in. So the left-most json statement applies to the right-most element in the parentheses
• The entire nested extract statement (created by the cross join) is a json object
• That json object is then used in the select stanza to get the needed fields within it (by using another json extract statement on it)
  • Note that SQL performs the work in the FROM stanza before it performs the work in the SELECT stanza; that's how it knows about the json object you created through the cross join, and why you can use that object in the Select stanza

...