Joining and filtering two arrays - Cloud

Talend Data Shaping Language Reference Guide

Version
Cloud
Language
English (United States)
Product
Talend Cloud
Module
Data Shaping Language
Content
Design and Development
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
        }
      ]
    }
  ]
}