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
- Create the FROM customer clause to call the customer array.
- Create a SELECT clause to return the input name and a new element named info.
-
Define the conditional expression to use as the value of info:
- Set the value of info to 'No info' if the customer has no rating: IF (rating == null) 'No info'.
- 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 }.
- 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"
}
}
]