Joining and filtering two arrays
Use Data Shaping Language to
join two JSON arrays and filter them based on element values.
About this task
This example uses the JSON input below. It contains two arrays:
- The customer array, which contains each customer's ID, name, address, and rating.
- The order array, which contains each order's number, customer ID, order date, shipping date and details about items ordered.
The goal of this transformation is to return a customer with a rating over 650 and the item ordered with the highest total price.
{
"customer": [
{
"custid": "C13",
"name": "T. Cruise",
"address": {
"street": "201 Main St.",
"city": "St. Louis, MO",
"zipcode": "63101"
},
"rating": 750
},
{
"custid": "C25",
"name": "M. Streep",
"address": {
"street": "690 River St.",
"city": "Hanover, MA",
"zipcode": "02340"
},
"rating": 690
},
{
"custid": "C31",
"name": "B. Pitt",
"address": {
"street": "360 Mountain Ave.",
"city": "St. Louis, MO",
"zipcode": "63101"
}
},
{
"custid": "C35",
"name": "J. Roberts",
"address": {
"street": "420 Green St.",
"city": "Boston, MA",
"zipcode": "02115"
},
"rating": 565
},
{
"custid": "C37",
"name": "T. Hanks",
"address": {
"street": "120 Harbor Blvd.",
"city": "Boston, MA",
"zipcode": "02115"
},
"rating": 750
},
{
"custid": "C41",
"name": "R. Duvall",
"address": {
"street": "150 Market St.",
"city": "St. Louis, MO",
"zipcode": "63101"
},
"rating": 640
},
{
"custid": "C47",
"name": "S. Loren",
"address": {
"street": "Via del Corso",
"city": "Rome, Italy"
},
"rating": 625
}
],
"order": [
{
"orderno": 1001,
"custid": "C41",
"order_date": "2017-04-29",
"ship_date": "2017-05-03",
"items": [
{
"itemno": 347,
"qty": 5,
"price": 19.99
},
{
"itemno": 193,
"qty": 2,
"price": 28.89
}
]
},
{
"orderno": 1002,
"custid": "C13",
"order_date": "2017-05-01",
"ship_date": "2017-05-03",
"items": [
{
"itemno": 460,
"qty": 95,
"price": 100.99
},
{
"itemno": 680,
"qty": 150,
"price": 8.75
}
]
},
{
"orderno": 1003,
"custid": "C31",
"order_date": "2017-06-15",
"ship_date": "2017-06-16",
"items": [
{
"itemno": 120,
"qty": 2,
"price": 88.99
},
{
"itemno": 460,
"qty": 3,
"price": 99.99
}
]
},
{
"orderno": 1004,
"custid": "C35",
"order_date": "2017-07-10",
"ship_date": "2017-07-15",
"items": [
{
"itemno": 680,
"qty": 6,
"price": 9.99
},
{
"itemno": 195,
"qty": 4,
"price": 35
}
]
},
{
"orderno": 1005,
"custid": "C37",
"order_date": "2017-08-30",
"ship_date": "",
"items": [
{
"itemno": 460,
"qty": 2,
"price": 99.98
},
{
"itemno": 347,
"qty": 120,
"price": 22
},
{
"itemno": 780,
"qty": 1,
"price": 1500
},
{
"itemno": 375,
"qty": 2,
"price": 149.98
}
]
},
{
"orderno": 1006,
"custid": "C41",
"order_date": "2017-09-02",
"ship_date": "2017-09-04",
"items": [
{
"itemno": 680,
"qty": 51,
"price": 25.98
},
{
"itemno": 120,
"qty": 65,
"price": 85
},
{
"itemno": 460,
"qty": 120,
"price": 99.98
}
]
},
{
"orderno": 1007,
"custid": "C13",
"order_date": "2017-09-13",
"ship_date": "2017-09-20",
"items": [
{
"itemno": 185,
"qty": 5,
"price": 21.99
},
{
"itemno": 680,
"qty": 1,
"price": 20.5
}
]
},
{
"orderno": 1008,
"custid": "C13",
"order_date": "2017-10-13",
"ship_date": "",
"items": [
{
"itemno": 460,
"qty": 20,
"price": 99.99
}
]
}
]
}