Item assignments

1-- core.monday.item_assignments
2-- Who is assigned to what: every person in every people column on every item.
3-- Grain: item x people column x person. Items with nobody assigned have no row.
4--
5-- People columns store JSON like {"personsAndTeams":[{"id":123,"kind":"person"}]}.
6-- Teams assigned to an item are skipped: only `kind = person` entries are kept, so
7-- an item assigned to a team counts for nobody here.
8--
9-- An item with two people columns (say "Owner" and "Reviewer") produces rows for
10-- both. Filter on `column_id` if you want one role only. Column titles are not
11-- synced, so the id is what you have.
12
13WITH people AS (
14    SELECT
15        v.item_id,
16        v.column_id,
17        JSON_VALUE(p, '$.id')    AS user_id
18    FROM {{staging.monday.item_column_value}} v,
19    UNNEST(JSON_QUERY_ARRAY(v.value_json, '$.personsAndTeams')) AS p
20    WHERE v.column_type IN ('people', 'multiple-person')
21      AND JSON_VALUE(p, '$.kind') = 'person'
22)
23
24SELECT
25    i.item_id,
26    i.item_name,
27    i.is_subitem,
28    i.parent_item_id,
29    i.board_id,
30    b.board_name,
31    b.board_state,
32    p.column_id,
33    p.user_id,
34    u.user_name,
35    u.email,
36    i.created_at  AS item_created_at,
37    i.updated_at  AS item_updated_at,
38    DATE_DIFF(CURRENT_DATE(), DATE(i.created_at), DAY) AS item_age_days
39FROM people p
40JOIN {{staging.monday.item}} i ON i.item_id = p.item_id