如何取消嵌套 JSON 数据对象并使用 Snowflake 创建简化的 JSON？

Question

我当前的 JSON 对象如下所示：

-- create a sample table
create or replace table json_example(v variant);
-- create sample json record
insert into json_example
select parse_json(
    '[
      {
        "key": "variable_a",
        "value": {
          "double_value": null,
          "float_value": null,
          "int_value": null,
          "string_value": "https://example.com"
        }
      },
      {
        "key": "variable_b",
        "value": {
          "double_value": null,
          "float_value": null,
          "int_value": 2,
          "string_value": null
        }
      }
     ]');

这是我想要实现的简化 JSON：

  {
    "variable_a": "https://example.com",
    "variable_b": 2
  }

How can I get the simple JSON from the multilevel JSON object?

我就是这样想的：

select value:key::string as key, value:value:string_value::varchar as value
from json_example, lateral flatten(input => v)
union all
select value:key::string as key, value:value:int_value::varchar as value
from json_example, lateral flatten(input => v)

先谢谢你了。

Answer 1

因此，如果您希望一切都成为json文本，则可以：

WITH data as (
select parse_json(
    '[
      {
        "key": "variable_a",
        "value": {
          "double_value": null,
          "float_value": null,
          "int_value": null,
          "string_value": "https://example.com"
        }
      },
      {
        "key": "variable_b",
        "value": {
          "double_value": null,
          "float_value": null,
          "int_value": 2,
          "string_value": null
        }
      }
     ]') as json
)
SELECT f.value:key::text as t_key
    ,try_to_double(f.value:value:double_value::text) as d_val
    ,try_to_double(f.value:value:float_value::text) as f_val
    ,try_to_number(f.value:value:int_value::text) as n_val
    ,f.value:value:string_value::text as s_val
    ,coalesce(d_val::text, f_val::text, n_val::text, s_val) as c_val
    ,object_construct(t_key, c_val) as obj
FROM DATA, lateral flatten(input=>json) f

t_key d_val	f_val n_val	s_val	s_val	c_val	obj abj
variable_a				https://example.com	https://example.com
varibal_b			2		2，

然后向我们展示我们如何构建案例语句，以及如何构建案例语句，并且构建干净的本机对象，例如：

SELECT 
    case 
        when not is_null_value(f.value:value:double_value) 
            then object_construct(f.value:key::text, try_to_double(f.value:value:double_value::text))
        when not is_null_value(f.value:value:float_value) 
            then object_construct(f.value:key::text, try_to_double(f.value:value:float_value::text))
        when not is_null_value(f.value:value:int_value) 
            then object_construct(f.value:key::text, try_to_number(f.value:value:int_value::text))
        else 
            object_construct(f.value:key::text, f.value:value:string_value::text)
    end obj
FROM DATA, lateral flatten(input=>json) f

obj
{“ variable_a”：“ https：//example.com”}
{“ variable_b”：2}，

可以将其变成一个单个对象，例如so：

SELECT 
    object_agg(f.value:key, 
    case 
        when not is_null_value(f.value:value:double_value) 
            then try_to_double(f.value:value:double_value::text)
        when not is_null_value(f.value:value:float_value) 
            then try_to_double(f.value:value:float_value::text)
        when not is_null_value(f.value:value:int_value) 
            then try_to_number(f.value:value:int_value::text)
        else 
            f.value:value:string_value
    end
   ) as obj
FROM DATA, lateral flatten(input=>json) f

obj
{“ variable_a”：“ https：/https：/https：/ /example.com”，“ variable_b”：2}

Answer 2

这有三个部分：

1. 将原始的JSON数组弄平，然后从中选择所需的值
2. 创建新的JSON对象，以结果的行值。
3. 将JSON对象组合到一个对象中。

with json_example(json) as (
  select parse_json(
      '[
        {
          "key": "variable_a",
          "value": {
            "double_value": null,
            "float_value": null,
            "int_value": null,
            "string_value": "https://example.com"
          }
        },
        {
          "key": "variable_b",
          "value": {
            "double_value": null,
            "float_value": null,
            "int_value": 2,
            "string_value": null
          }
        }
       ]'
  )
),
flattened_rows as
(
      select v.value:key::string as key, 
        v.value:value:int_value::int as int_value,
        v.value:value:string_value::string as string_value
        -- other values here
    from json_example, lateral flatten(input => json) as v
)
,
simplified_json as 
(
  select
    case when int_value is not null then object_construct(key, int_value)::variant
    else object_construct(key, string_value)
    end as json
  from flattened_rows
)
select object_agg(j.key, j.value)
from simplified_json, lateral flatten(input => json) AS j
;

flattened_rows 看起来像这样：

键	int_value	string_value
variable_a		https://example.com
variable_b	2

这样：

simpleified_json
看起来像 com“}
{“ variable_b”：2}

最终结果：

obj
{{“ variable_a”：“ https：//example.com”，“ variable_b”：2}

更新

我更新了上面的答案以将上面的答案合并到 object_agg 找到Simeon的方法。我的原始答案涉及创建利用 object.assign 组合JSON对象的JavaScript UDTF。