UnPivot

Creates multiple records from sets of columns. It offers a way to consolidate columns by exploding records.

Before UnPivot:

id	score_item1	score_item2	score_item3
1001	4	5	2
1002	6	0	10

After UnPivot:

id	item	score
1001	1	4
1001	2	5
1001	3	2
1002	1	6
1002	2	0
1002	3	10

Example syntax

{
    stack: [
        { id: "id", item: "1", score: "score_item1" },
        { id: "id", item: "2", score: "score_item2" },
        { id: "id", item: "3", score: "score_item3" },
    ]
}

Stack

Stack is an array of stack entries. At least 2 stack entries should exist. Each stack entry is a JSON object that contains field assignment(s). This defines the column consolidation (the unpivot), as each stack entry can assign a unique column to a consolidated column name.

stack: [
    { brand: "1", score: "score_b1" }, // assigns score_b1 into q1 (also adds a brand id of 1)
    { brand: "2", score: "score_b2" }, // assigns score_b2 into q1 (also adds a brand id of 2)
    { brand: "3", score: "score_b3" }, // assigns score_b2 into q1 (also adds a brand id of 2)
]

A stack entry field assignment can be an expression

stack: [
    { brand: "1", score: "score_b1 * 100" },
    { brand: "2", score: "score_b2 * 100" },
    { brand: "3", score: "score_b3 * 100" },
]

Keep

Keep is optional. It is a convenient way to add expression(s) to every stack entry. You might use it if you have a long list of variables to keep and don't want to redundantly type them for every stack entry. Keep expressions provide the same value for every stack entry.

// using "keep"
{
    keep: {
        // (rob todo: consider a simplified array syntax)
        id: "id",
        q2: "q2 * 100", // it can be an expression
        q3: "q3",
        q4: "q4"
    },
    stack: [
        { brand: "1", score: "score_b1" },
        { brand: "2", score: "score_b2" },
        { brand: "3", score: "score_b3" },
    ]
}

// equivalent syntax without using "keep"
{
    stack: [
        { id: "id", brand: "1", score: "score_b1", q2: "q2 * 100", q3: "q3", q4: "q4" },
        { id: "id", brand: "2", score: "score_b2", q2: "q2 * 100", q3: "q3", q4: "q4" },
        { id: "id", brand: "3", score: "score_b3", q2: "q2 * 100", q3: "q3", q4: "q4" },
    ]
}

Other:

note

You might want to append a filter stage after this stage to remove empty entries. (Or should I integrate it somehow?)

// unpivot stage
{
    keep: {
        "id": "id"
    },
    stack: [
        { brand: "1", score: "score_b1" }, // assigns score_b1 into score (also adds a brand id of 1)
        { brand: "2", score: "score_b2" }, // assigns score_b2 into score (also adds a brand id of 2)
    ]
}

// filter stage
["q1 is not null"]