Selecting elements from two arrays

Create a query that joins two arrays and selects elements from both to create the output.

Procedure

Create a FROM clause to call the order array and give it an alias: FROM order AS o.

In the sample input provided, both arrays contain an element named custid. In order to call both of these elements in the query, you need an alias for each array. For more information, see Using the FROM clause.
Unnest the items array from order using the clause UNNEST o.items.
Add a JOIN clause to join the customer array to the order array when the value of custid is the same in both arrays: JOIN customer AS c ON c.custid = o.custid
Create a SELECT clause to return the customer name and rating, the order number and the item number.
```
SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno,
}
```

Results

The query should look like this:

FROM order AS o
UNNEST o.items
JOIN customer AS c ON c.custid = o.custid
SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno
}

It returns the following result:

[
	{
		"name": "R. Duvall",
		"rating": 640,
		"orderno": 1001,
		"itemno": 347
	},
	{
		"name": "R. Duvall",
		"rating": 640,
		"orderno": 1001,
		"itemno": 193
	},
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1002,
		"itemno": 460
	},
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1002,
		"itemno": 680
	},
	{
		"name": "B. Pitt",
		"rating": null,
		"orderno": 1003,
		"itemno": 120
	},
	{
		"name": "B. Pitt",
		"rating": null,
		"orderno": 1003,
		"itemno": 460
	},
	{
		"name": "J. Roberts",
		"rating": 565,
		"orderno": 1004,
		"itemno": 680
	},
	{
		"name": "J. Roberts",
		"rating": 565,
		"orderno": 1004,
		"itemno": 195
	},
	{
		"name": "T. Hanks",
		"rating": 750,
		"orderno": 1005,
		"itemno": 460
	},
	{
		"name": "T. Hanks",
		"rating": 750,
		"orderno": 1005,
		"itemno": 347
	},
	{
		"name": "T. Hanks",
		"rating": 750,
		"orderno": 1005,
		"itemno": 780
	},
	{
		"name": "T. Hanks",
		"rating": 750,
		"orderno": 1005,
		"itemno": 375
	},
	{
		"name": "R. Duvall",
		"rating": 640,
		"orderno": 1006,
		"itemno": 680
	},
	{
		"name": "R. Duvall",
		"rating": 640,
		"orderno": 1006,
		"itemno": 120
	},
	{
		"name": "R. Duvall",
		"rating": 640,
		"orderno": 1006,
		"itemno": 460
	},
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1007,
		"itemno": 185
	},
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1007,
		"itemno": 680
	},
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1008,
		"itemno": 460
	}
]

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 – let us know how we can improve!

Leave your feedback here