48.Merchant - 大订单

索尼娅现在想找出哪些4月的订单超过500美元的单价。右边的查询使用UNNEST运算和JOIN字句来获得期望的结果。

SELECT purchases.purchaseId, l.product, prod.name, prod.unitPrice
    FROM purchases UNNEST purchases.lineItems l
        JOIN product prod ON KEYS l.product
    WHERE DATE_PART_STR(purchases.purchasedAt,"month") = 4
        AND DATE_PART_STR(purchases.purchasedAt,"year") = 2014 
        AND prod.unitPrice > 500

结果:

{
  "results": [
    {
      "name": "Brother PQ1500S High Speed Quilting and Sewing Machine",
      "product": "product221",
      "purchaseId": "purchase1533",
      "unitPrice": 599
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase170",
      "unitPrice": 700
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product490",
      "purchaseId": "purchase1760",
      "unitPrice": 1094.46
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase1917",
      "unitPrice": 1499
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase2195",
      "unitPrice": 1499
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase2316",
      "unitPrice": 1499
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase2372",
      "unitPrice": 1094.46
    },
    {
      "name": "Archie Eli & Peyton Manning NFL Duke Football",
      "product": "product839",
      "purchaseId": "purchase2500",
      "unitPrice": 1249.99
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase2891",
      "unitPrice": 599.99
    },
    {
      "name": "Archie Eli & Peyton Manning NFL Duke Football",
      "product": "product839",
      "purchaseId": "purchase2907",
      "unitPrice": 1249.99
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase3010",
      "unitPrice": 1499
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase3278",
      "unitPrice": 700
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase3476",
      "unitPrice": 599.99
    },
    {
      "name": "TOTO MS854114SL-01 Ultramax ADA One Piece Toilet, Cotton White",
      "product": "product506",
      "purchaseId": "purchase3685",
      "unitPrice": 502.37
    },
    {
      "name": "Eli Manning Signed Super Bowl XLVI Football",
      "product": "product833",
      "purchaseId": "purchase3685",
      "unitPrice": 599.99
    },
    {
      "name": "Archie Eli & Peyton Manning NFL Duke Football",
      "product": "product839",
      "purchaseId": "purchase3949",
      "unitPrice": 1249.99
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase4281",
      "unitPrice": 1094.46
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase4660",
      "unitPrice": 700
    },
    {
      "name": "Eli Manning Signed Super Bowl XLVI Football w/ SB XLVI MVP Insc.",
      "product": "product838",
      "purchaseId": "purchase5042",
      "unitPrice": 799.99
    },
    {
      "name": "Danby DDW1899WP 8 Place Setting Portable Dishwasher - White",
      "product": "product203",
      "purchaseId": "purchase5321",
      "unitPrice": 524
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase545",
      "unitPrice": 700
    },
    {
      "name": "Danby DDW1899WP 8 Place Setting Portable Dishwasher - White",
      "product": "product203",
      "purchaseId": "purchase5483",
      "unitPrice": 524
    },
    {
      "name": "Eli Manning Signed Super Bowl XLVI Football",
      "product": "product833",
      "purchaseId": "purchase568",
      "unitPrice": 599.99
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase6223",
      "unitPrice": 599.99
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase6491",
      "unitPrice": 700
    },
    {
      "name": "Danby DDW1899WP 8 Place Setting Portable Dishwasher - White",
      "product": "product203",
      "purchaseId": "purchase7679",
      "unitPrice": 524
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase8069",
      "unitPrice": 599.99
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase8390",
      "unitPrice": 1094.46
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase8539",
      "unitPrice": 1094.46
    },
    {
      "name": "Oeuf Sparrow Crib, White",
      "product": "product606",
      "purchaseId": "purchase8764",
      "unitPrice": 760
    },
    {
      "name": "LG 3.6 Cu. Ft. White Front Load Steam Washer - WM2650HRA",
      "product": "product167",
      "purchaseId": "purchase8886",
      "unitPrice": 892.49
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product490",
      "purchaseId": "purchase9007",
      "unitPrice": 1094.46
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase9210",
      "unitPrice": 1499
    },
    {
      "name": "LG 3.6 Cu. Ft. White Front Load Steam Washer - WM2650HRA",
      "product": "product167",
      "purchaseId": "purchase9374",
      "unitPrice": 892.49
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase9591",
      "unitPrice": 599.99
    },
    {
      "name": "EdgeStar 2.0 Cu. Ft. Ventless Combo Washer/Dryer - White",
      "product": "product158",
      "purchaseId": "purchase9598",
      "unitPrice": 830.99
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase9646",
      "unitPrice": 700
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase9668",
      "unitPrice": 700
    }
  ]
}