Assigning values based on conditions
Last updated: 9/17/2026
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"
}
}
]