Selecting elements from two arrays - Cloud

Talend Data Shaping Language Reference Guide

Version
Cloud
Language
English (United States)
Product
Talend Cloud
Module
Data Shaping Language
Content
Design and Development
Create a query that joins two arrays and selects elements from both to create the output.

Procedure

  1. 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.

  2. Unnest the items array from order using the clause UNNEST o.items.
  3. 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
  4. 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
	}
]