Skip to main content Skip to complementary content

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
        }
      ]
    }
  ]
}

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!