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.

// using array "keep" (simple)
{
    "keep": ["id"],
    "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" },
        { "id": "id", "brand": "2", "score": "score_b2" },
        { "id": "id", "brand": "3", "score": "score_b3" },
    ]
}

// using object "keep" (calc in keep)
{
    "keep": {
        "id": "id",
        "q2": "q2 * 100", // it can be an expression
    },
    "stack": [
        { "brand": "1", "score": "score_b1" },
        { "brand": "2", "score": "score_b2" },
        { "brand": "3", "score": "score_b3" },
    ]
}

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

Integrated Meta

It may be convenient to add field meta, such as a label for new vars and values.

{
    "keep": ["id"],
    "stack": [
        { "brand": "1", "score": "score_b1" },
        { "brand": "2", "score": "score_b2" },
        { "brand": "3", "score": "score_b3" },
    ],
    "meta": {
        "brand": {
            "label": "Brand",
            "valueLabels": [
                { "value": 1, "label": "Acme" },
                { "value": 2, "label": "NSP" },
                { "value": 3, "label": "Netherbrand" }
            ]
        },
        "score": {
            "label": "Brand Score"
        }
    }
}

Integrated Filter:

It may be convenient to filter out rows that have empty values.

// unpivot stage
{
    "keep": ["id"],
    "stack": [
        { "brand": "1", "score": "score_b1" },
        { "brand": "2", "score": "score_b2" },
        { "brand": "3", "score": "score_b3" },
    ],
    "filter": [
        "score is not null"
    ]
}