Details
-
Bug
-
Resolution: Unresolved
-
Major
-
7.1.0, 7.2.0, 7.6.0
Description
We secretly support what's needed (AT <position>) already - we just need to document it and make sure it's covered in our tests. What's needed is the ability to capture the positions of the array elements being unnested in a query - this works as long as what's being unnested is contained in a single object.
Here is an example of “AT <position>” that works with the Don Chamberlin example data that Analytics offers in its docs. (Setup DDL/DML attached for the open source version of the engine.)
USE DonCDataNoSchema;
FROM orders AS o UNNEST o.items AS i AT p
SELECT o.orderno, o.order_date, i.itemno AS item_number,
i.qty AS quantity, p AS line_number
ORDER BY o.orderno, line_number;
Its output is:
{"line_number":1,"orderno":1001,"order_date":"2017-04-29","item_number":347,"quantity":5} {"line_number":2,"orderno":1001,"order_date":"2017-04-29","item_number":193,"quantity":2} {"line_number":1,"orderno":1002,"order_date":"2017-05-01","item_number":460,"quantity":95} {"line_number":2,"orderno":1002,"order_date":"2017-05-01","item_number":680,"quantity":150} {"line_number":1,"orderno":1003,"order_date":"2017-06-15","item_number":120,"quantity":2} {"line_number":2,"orderno":1003,"order_date":"2017-06-15","item_number":460,"quantity":3} {"line_number":1,"orderno":1004,"order_date":"2017-07-10","item_number":680,"quantity":6} {"line_number":2,"orderno":1004,"order_date":"2017-07-10","item_number":195,"quantity":4} {"line_number":1,"orderno":1005,"order_date":"2017-08-30","item_number":460,"quantity":2} {"line_number":2,"orderno":1005,"order_date":"2017-08-30","item_number":347,"quantity":120}Note that this is potentially very important for creating tabular views of nested data, in case the nested array elements don’t have anything in them that could be used as their key when they are unnested/normalized into separate (virtual) tables.
Attachments
Issue Links
- mentioned in
-
Page Loading...