Expand, Explode, and Rotate Functions

Summary

Expand, Explode, and Rotate are a class of XSQL functions that extract individual data values from columns containing multivalued attributes. A single input row may generate multiple output rows. Connect tables can store multivalued attribute data using XSQL’s “list” and “JSON” data types. The functions each behave differently, but in general, will extract individual items found in the list and JSON array column values, creating multiple output rows from a single input row.

Syntax

Parameters

Input=(select id, name, list_products, json_account_tasks from delta.some_table)

Return Type

Each function takes an input XSQL query or expression and returns a result set. The specific data transformations performed by each function are detailed later on this page with example data scenarios.

Details

Expand: Performs a FULL OUTER JOIN of the list/JSON array values from a column with the other values in the input row, including other list/JSON array columns.

Explode: Performs a UNION JOIN for every value found in a list or JSON array column.

Rotate:Outputs a rows based on the same index position across all list or JSON array columns, i.e. match every list/json array position 0..n across all list/JSON array columns.

The behavior of each function is best seen visually with the data examples found in the next section.

Example using Lists

The following examples show the functional differences between the three functions. Each example is based on the following input result set. The first column is an integer value. The second and third columns contain a comma delimited list of integers. The data type of the last two columns is “list”. The functions will only operate on the data types: list and json (arrays only, not objects).

The example uses lists, but the same logic can be applied to columns that store JSON arrays (but not JSON objects), as found in the second example set. Columns that store JSON objects can be queries using XSQL’s $ syntax (example at bottom).

select id, list_1, list_2 
from CollectionMockData();

select * 
from Expand(Input=(select id, list_1, list_2 from CollectionMockData()));

select * 
from Explode(Input=(select id, list_1, list_2 from CollectionMockData()));

select * 
from Rotate(Input=(select id, list_1, list_2 from CollectionMockData()));

Example using JSON Arrays

First, generate the raw demo input result set. It contains three columns:

• The first is an integer id
• The second column is a JSON array containing three JSON objects.
• The third column is also a JSON array, but only contains two JSON objects.

We’ll see how the functions create multiple rows from this single input result. Recall, the functions do not manipulate JSON objects, only JSON arrays.

select SeqNum() as id,
ToJson('[{"Key":"a","Val":"index 1"},{"Key":"b","Val":"index 2"},{"Key":"c","Val":"index 3"}]') as jsonArray_1,
ToJson('[{"Pos":"idx 1","Desc":"One", "task":"Work."},{"Pos":"idx 2","Desc":"Two", "task":"Sleep."}]') as jsonArray_2
from Empty(Rows=1);

call Expand(Input=(select SeqNum() as id,
ToJson('[{"Key":"a","Val":"index 1"},{"Key":"b","Val":"index 2"},{"Key":"c","Val":"index 3"}]') as jsonArray_1,
ToJson('[{"Pos":"idx 1","Desc":"One", "task":"Work."},{"Pos":"idx 2","Desc":"Two", "task":"Sleep."}]') as jsonArray_2
from Empty(Rows=1)));

call Explode(Input=(select SeqNum() as id,
ToJson('[{"Key":"a","Val":"index 1"},{"Key":"b","Val":"index 2"},{"Key":"c","Val":"index 3"}]') as jsonArray_1,
ToJson('[{"Pos":"idx 1","Desc":"One", "task":"Work."},{"Pos":"idx 2","Desc":"Two", "task":"Sleep."}]') as jsonArray_2
from Empty(Rows=1)));

call Rotate(Input=(select SeqNum() as id,
ToJson('[{"Key":"a","Val":"index 1"},{"Key":"b","Val":"index 2"},{"Key":"c","Val":"index 3"}]') as jsonArray_1,
ToJson('[{"Pos":"idx 1","Desc":"One", "task":"Work."},{"Pos":"idx 2","Desc":"Two", "task":"Sleep."}]') as jsonArray_2
from Empty(Rows=1)));

Related Commands

MSFTDynamics

JsonPath

$ syntax to extract values from JSON objects:

select jsonObject_1,
$.jsonObject_1.fruit,
$.jsonObject_1.lunch
from CollectionMockData()
where id=2;