-
Notifications
You must be signed in to change notification settings - Fork 3
/
unnest_by_keys.sql
66 lines (49 loc) · 1.62 KB
/
unnest_by_keys.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- Unnests a single key's value from an array
{%- macro unnest_by_key(column_to_unnest, key_to_extract, value_type = "string") -%}
(
SELECT
value.{{ value_type }}_value
FROM
UNNEST({{ column_to_unnest }})
WHERE
key = '{{ key_to_extract }}'
) AS {{ key_to_extract }}
{%- endmacro -%}
{%- macro unnest_by_key_alt(column_to_unnest, key_to_extract, value_type = "string") -%}
(
SELECT
value.{{ value_type }}_value
FROM
UNNEST({{ column_to_unnest }})
WHERE
key = '{{ key_to_extract }}'
)
{%- endmacro -%}
-- MACRO FOR HANDLING `query_params` --
{%- macro unnest_by_key_2(column_to_unnest, key_to_extract) -%}
(
SELECT
value
FROM
UNNEST({{ column_to_unnest }})
WHERE
key = '{{ key_to_extract }}'
LIMIT 1
) AS {{ key_to_extract }}
{%- endmacro -%}
-- REFACTORING MACRO -- [OLD]
{%- macro unnest_by_key2(column_to_unnest, key_to_extract) -%}
{% set value_types = ["string", "int", "float", "double"] %}
(
SELECT
{%- if value_type == "string" %}
value.string_value AS value_type
{% else %}
COALESCE(value.int_value, value.float_value, value.double_value) AS value_type
{%- endif %}
FROM
UNNEST({{ column_to_unnest }})
WHERE
key = '{{ key_to_extract }}'
) AS {{ key_to_extract }}
{%- endmacro -%}