sql - 雪花json横向子查询

Question

我在雪花中有以下内容:

create or replace table json_tmp as select column1 as id, parse_json(column2) as c
    from VALUES (1,
                 '{"id": "0x1",
                   "custom_vars": [
                      { "key": "a", "value": "foo" },
                      { "key": "b", "value": "bar" }
                   ] }') v;

基于FLATTEN docs , 我希望把它们变成这样的表格:

+-------+---------+-----+-----+
| db_id | json_id |  a  |  b  |
+-------+---------+-----+-----+
+-------+---------+-----+-----+
|   1   |   0x1   | foo | bar |
+-------+---------+-----+-----+

这是我试过的查询；它导致 SQL 编译错误:“对象 'CUSTOM_VARS' 不存在。”

select json_tmp.id as dbid,
    f.value:id as json_id,
    a.v,
    b.v
from json_tmp,
    lateral flatten(input => json_tmp.c) as f,
    lateral flatten(input => f.value:custom_vars) as custom_vars,
    lateral (select value:value as v from custom_vars where value:key = 'a') as a,
    lateral (select value:value as v from custom_vars where value:key = 'b') as b;

这里的错误到底是什么？有没有更好的方法来进行这种转换？

Answer 1

请注意 - 您的解决方案实际上并不执行任何连接 - 展平是一种“流式”操作，它“分解”输入，然后选择它想要的行。如果数据中只有 2 个属性，它应该相当快。但是，如果不这样做，可能会导致不必要的数据爆炸(例如，如果您有 1000 多个属性)。

最快的解决方案取决于您的数据的准确结构，以及您对输入的假设。例如，如果你知道 'a' 和 'b' 总是按那个顺序，你显然可以使用

select 
    id as db_id, 
    c:id, 
    c:custom_vars[0].value, 
    c:custom_vars[1].value 
from json_tmp;

如果你知道custom_vars始终是 2 个元素，但顺序未知，您可以这样做，例如

select 
    id as db_id, 
    c:id, 
    iff(c:custom_vars[0].key = 'a', c:custom_vars[0].value, c:custom_vars[1].value), 
    iff(c:custom_vars[0].key = 'b', c:custom_vars[0].value, c:custom_vars[1].value) 
from json_tmp;

如果 custom_vars 的大小未知，您可以创建一个 JavaScript 函数，如 extract_key(custom_vars, key)这将遍历 custom_vars并返回 value对于发现key (或者例如 null 或 <empty_string> 如果没有找到)。

希望这对您有所帮助。如果不是，请提供有关您的问题的更多详细信息(数据等)。