Effectuer une jointure entre deux tableaux et filtrer
Utilisez Data Shaping Language pour effectuer une jointure entre deux tableaux JSON et filtrer selon la valeur des éléments.
Pourquoi et quand exécuter cette tâche
Cet exemple utilise le JSON d'entrée ci-dessous. Il contient deux tableaux :
- Le tableau customer, qui contient l'ID de chaque client·e, son nom, son adresse et sa note.
- Le tableau order, qui contient le numéro de commande, l'identifiant du client ou de la cliente, la date de commande, la date d'expéditon et les détails relatifs aux éléments commandés.
L'objectif de cette transformation est de retourner un·e client·e avec une note supérieure à 650 et l'élément commandé avec le prix total le plus élevé.
{
"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
}
]
}
]
}