sql - JSON 扁平化和表创建

Question

有人可以帮我创建一个 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-WWW

234234

asdfsdfsdf-23423-fsff-3445435
32
MMDI
:--:
这是一个测试
11-4543535-2
4056
erte

343534

Answer 1

你可以用几个扁平化和一个枢轴来做到这一点。您需要事先知道要旋转多少列。在您的示例数据中，两条记录都只有 10 列，但如果某些记录包含更多列，您将需要更新数据透视语句。
我认为你的结果表弄错了。您已经错过了 json 中的键“6”，这将您的结果推回了 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
erte

343534

1234-567-890
00-123-safsf-3345
sfsfd-234-fgf-55-4545
32
MMAD

巴克拉-达克拉
33-5455-9

4056
TUU-WWW

234234