Skip to main content Skip to complementary content

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

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!