# Merging two arrays based on conditions

Use the UNION ALL keyword with conditions to merge two arrays into one and iterate on its contents.

## About this task

This example uses the JSON input below. It contains a customers element with two arrays: Platinum and Gold. Each array contains the same three elements: region, cid, and name. The goal is to create a single array.

```
{
	"customers": {
		"Platinum": [
			{
				"region": "West Midlands",
				"cid": 12,
				"name": "E. W. Gilbert"
			},
			{
				"region": "Greater Manchester",
				"cid": 75,
				"name": "John Doe"
			}
		],
		"Gold": [
			{
				"region": "West Midlands",
				"cid": 13,
				"name": "G. K. Peatling"
			},
			{
				"region": "Greater Manchester",
				"cid": 76,
				"name": "Tom Cruise"
			}
		]
	}
}
```

## Procedure

1. Create the FROM and SELECT clauses to create the new customers array.
   ```
   FROM customers
   SELECT {
      customers =
         []
   }
   ```
2. In this new array, create a first sub-query to iterate on the input Platinum array.

   You can use a conditional expression with the IF keyword to check that the Platinum array has a value.
   ```
   FROM customers
   SELECT {
      customers =
         [
            IF (hasValue(Platinum)) (
               FROM Platinum
               SELECT {
                  region = region,
                  cid = cid,
                  name = name,
                  ranking = "Platinum"
               }
            )
         ]
   }
   ```
3. Create a second sub-query to iterate on the Gold array, and use the UNION ALL keyword to merge both arrays in the new customers array.

   After the UNION ALL keyword, you can include a conditional expression to check that the Gold array has a value.
   ```
   FROM customers
   SELECT {
      customers =
         [
            IF (hasValue(Platinum)) (
               FROM Platinum
               SELECT {
                  region = region,
                  cid = cid,
                  name = name,
                  ranking = "Platinum"
               }
            )
            UNION ALL
            IF (hasValue(Gold)) (
               FROM Gold
               SELECT {
                  region = region,
                  cid = cid,
                  name = name,
                  ranking = "Gold"
               }
            )
         ]
   }
   ```

## Results

This query returns the following result:

```
[
	{
		"customers": [
			{
				"region": "West Midlands",
				"cid": 12,
				"name": "E. W. Gilbert",
				"ranking": "Platinum"
			},
			{
				"region": "Greater Manchester",
				"cid": 75,
				"name": "John Doe",
				"ranking": "Platinum"
			},
			{
				"region": "West Midlands",
				"cid": 13,
				"name": "G. K. Peatling",
				"ranking": "Gold"
			},
			{
				"region": "Greater Manchester",
				"cid": 76,
				"name": "Tom Cruise",
				"ranking": "Gold"
			}
		]
	}
]
```