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

Create the FROM and SELECT clauses to create the new customers array.
```
FROM customers
SELECT {
   customers =
      []
}
```

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

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

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!

Leave your feedback here