Skip to main content Skip to complementary content
Announcements

Assigning values based on conditions

Use Data Shaping Language to produce records with values based on conditions.

About this task

This example uses the JSON input below. It contains a customer array with each customer's ID, name, address and rating.

The goal of this transformation is to return an array of customers with a name and an info element. The value of info changes depending on the value of rating:
  • If the customer has no rating, info is a string with the value 'No info'.
  • If the customer has a rating between 0 and 600, info is an object that contains a category element with the value Standard, and the customer's city.
  • Otherwise, info contains the category element with the value Gold, and the customer's full address.
{
  "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
    }
  ]
}

Procedure

  1. Create the FROM customer clause to call the customer array.
  2. Create a SELECT clause to return the input name and a new element named info.
  3. Define the conditional expression to use as the value of info:
    1. Set the value of info to 'No info' if the customer has no rating: IF (rating == null) 'No info'.
    2. Set the value of info to an object with a category element with the value 'Standard' and the customer's city if the value of rating is between 0 and 600: ELSEIF rating BETWEEN 0 AND 600 { category = 'Standard', city = address.city }.
    3. For all other cases, set the value of info to an object with a category element with the value 'Gold' and the customer's full address, concatenated in a single string: ELSE { category = 'Gold', address = concatWith(', ', address.street, address.city) }.

Results

The query should look like this:
FROM customer
SELECT {
   name,
   info =
      IF (rating == null) 
		'No info'
      ELSEIF rating BETWEEN 0 AND 600 { 
			category = 'Standard', 
			city = address.city 
		}
      ELSE { 
	  category = 'Gold', 
	  address = concatWith(', ', address.street, address.city) 
	  }
}
It returns the following result:
[
    {
        "name": "T. Cruise",
        "info": {
            "category": "Gold",
            "address": "201 Main St., St. Louis, MO"
        }
    },
    {
        "name": "M. Streep",
        "info": {
            "category": "Gold",
            "address": "690 River St., Hanover, MA"
        }
    },
    {
        "name": "B. Pitt",
        "info": "No info"
    },
    {
        "name": "J. Roberts",
        "info": {
            "category": "Standard",
            "city": "Boston, MA"
        }
    },
    {
        "name": "T. Hanks",
        "info": {
            "category": "Gold",
            "address": "120 Harbor Blvd., Boston, MA"
        }
    },
    {
        "name": "R. Duvall",
        "info": {
            "category": "Gold",
            "address": "150 Market St., St. Louis, MO"
        }
    },
    {
        "name": "S. Loren",
        "info": {
            "category": "Gold",
            "address": "Via del Corso, Rome, Italy"
        }
    }
]

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 – let us know how we can improve!

Leave your feedback here