时间戳

首页 > 解决方案 > JSON扁平化和表创建

sql - JSON扁平化和表创建

问题描述

有人可以帮我创建一个 SQL 语句来展平 Snowflake Table1 表中的 JSON 数据，在一个具有数组的 JSON_DATA 列中吗？

JSON数据

{
  "id": "1234-567-890",
  "parent_id": "00-123-safsf-3345",
  "data": [
    {
      "id": "sfsfd-234-fgf-55-4545",
      "values": [
        {
          "name": "one",
          "value": "32"
        },
        {
          "name": "Two",
          "value": "MMAD"
        },
        {
          "name": "three",
          "value": ""
        },
        {
          "name": "four",
          "value": "Bacra-Dacra"
        },
        {
          "name": "five",
          "value": "33-5455-9"
        },
        {
          "name": "six",
          "value": ""
        },
        {
          "name": "seven",
          "value": "4056"
        },
        {
          "name": "eight",
          "value": "TUU-WWW"
        },
        {
          "name": "nine",
          "value": ""
        },
        {
          "name": "ten",
          "value": "234234"
        }
      ]
    },
    {
      "id": "asdfsdfsdf-23423-fsff-3445435",
      "values": [
        {
          "name": "One",
          "value": "32"
        },
        {
          "name": "Two",
          "value": "MMDI"
        },
        {
          "name": "Three",
          "value": ""
        },
        {
          "name": "four",
          "value": "THis is a Test"
        },
        {
          "name": "five",
          "value": "11-4543535-2"
        },
        {
          "name": "six",
          "value": ""
        },
        {
          "name": "seven",
          "value": "4056"
        },
        {
          "name": "eight",
          "value": "ert erte"
        },
        {
          "name": "nine",
          "value": ""
        },
        {
          "name": "ten",
          "value": "343534"
        }
      ]
    }
  ]
}

所需表格格式：

ID 一 二 三 四 五 六 七 八 九 十
sfsfd-234-fgf-55-4545 32 MMAD :--: 巴克拉-达克拉 33-5455-9 4056 TUU-万维网 234234
asdfsdfsdf-23423-fsff-3445435 32 MMDI :--: 这是一个测验 11-4543535-2 4056 永远的 343534

标签： sqlsnowflake-cloud-data-platformjson-flattener

解决方案

你可以用几个扁平化和一个枢轴来做到这一点。您需要事先知道要旋转多少列。在您的示例数据中，两条记录都只有 10 列，但如果某些记录包含更多，您将需要更新数据透视语句。

我认为您的结果表有误。您错过了 json 中的键“六”，这将您的结果向后推了 1。例如，最后一列“十”应该包含数字234234，343534但您将它们放在“九”列中。5 之后的列的相同处理。

这是可重现的示例设置：

-- create example source table
create or replace table source_table
(
    json_data variant
);

-- create example target table
create or replace table target_table
(
    id    number,
    one   varchar,
    two   varchar,
    three varchar,
    four  varchar,
    five  varchar,
    six   varchar,
    seven varchar,
    eight varchar,
    nine  varchar,
    ten   varchar
);

-- Insert json data into source table
insert overwrite into source_table (json_data)
select
    parse_json('{
  "id": "1234-567-890",
  "parent_id": "00-123-safsf-3345",
  "data": [
    {
      "id": "sfsfd-234-fgf-55-4545",
      "values": [
        {
          "name": "one",
          "value": "32"
        },
        {
          "name": "Two",
          "value": "MMAD"
        },
        {
          "name": "three",
          "value": ""
        },
        {
          "name": "four",
          "value": "Bacra-Dacra"
        },
        {
          "name": "five",
          "value": "33-5455-9"
        },
        {
          "name": "six",
          "value": ""
        },
        {
          "name": "seven",
          "value": "4056"
        },
        {
          "name": "eight",
          "value": "TUU-WWW"
        },
        {
          "name": "nine",
          "value": ""
        },
        {
          "name": "ten",
          "value": "234234"
        }
      ]
    },
    {
      "id": "asdfsdfsdf-23423-fsff-3445435",
      "values": [
        {
          "name": "One",
          "value": "32"
        },
        {
          "name": "Two",
          "value": "MMDI"
        },
        {
          "name": "Three",
          "value": ""
        },
        {
          "name": "four",
          "value": "THis is a Test"
        },
        {
          "name": "five",
          "value": "11-4543535-2"
        },
        {
          "name": "six",
          "value": ""
        },
        {
          "name": "seven",
          "value": "4056"
        },
        {
          "name": "eight",
          "value": "ert erte"
        },
        {
          "name": "nine",
          "value": ""
        },
        {
          "name": "ten",
          "value": "343534"
        }
      ]
    }
  ]
}');

select *
from (
 select
     st.json_data:id::varchar         as main_id,
     st.json_data:parent_id::varchar  as parent_id,
     data.value:id::varchar           as id,
     upper(vals.value: name::varchar) as col_name,
     vals.value: value::varchar       as col_value
 from source_table st,
      lateral flatten(input => json_data: data) data,
      lateral flatten(input => data.value: values) vals
 )
 pivot (max(col_value) for col_name in ('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN', 'EIGHT', 'NINE', 'TEN'))

以上产生的结果如下所示：

MAIN_ID PARENT_ID ID '一' '二' '三' '四' '五' '六' '七' '八' '九' '十'
1234-567-890 00-123-safsf-3345 asdfsdfsdf-23423-fsff-3445435 32 MMDI 这是一个测验 11-4543535-2 4056 永远的 343534
1234-567-890 00-123-safsf-3345 sfsfd-234-fgf-55-4545 32 MMAD 巴克拉-达克拉 33-5455-9 4056 TUU-万维网 234234

推荐阅读