sql查询多元半结构化数据(json)

7fhtutme  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(348)

我不能用snowflake sql正确地查询半结构化的多层次(json)数据。你能帮我解释一下什么是不正确的,我正在分析的数据或者我的扁平语法吗?如何写出正确的语法?非常感谢!我的问题是:

WITH a AS ( 
SELECT PARSE_JSON('[ 
             {
"name": "conversion_event",
"created_at" : "2020-01-01 00:00:00",
"uuid" : 12314512441312312,
"campaign_name": "campaign_name1",
"campaign_channel": "channel1",
"meta":{
"app_name": "iOS",
"os_version": 11,
"country" : "ABC",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-15 00:00:00",
"uuid" : 12314512441312435,
"campaign_name": "campaign_name2",
"campaign_channel": "channel1",
"meta":{
"app_name": "Winsows",
"os_version": "n/a" ,
"country" : "AC",
"latitude": 16.35,
"longitude": 31.45,
},
"type_of_conversion": "purchase"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-30 00:00:00",
"uuid" : 12314512441554433,
"campaign_name": "campaign_name1",
"campaign_channel": "channel3",
"meta":{
"app_name": "iOS",
"os_version": 11,
"country" : "AB",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}
,
{
"name": "conversion_event",
"created_at" : "2020-02-28 00:00:00",
"uuid" : 12314512441312312,
"campaign_name": "campaign_name2",
"campaign_channel": "channel3",
"meta":{
"app_name": "iOS",
"os_version": "9",
"country" : "ABC",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-30 00:00:00",
"uuid" : 12314512441312435,
"campaign_name": "campaign_name3",
"campaign_channel": "channel4",
"meta":{
"app_name": "Winsows",
"os_version": "n/a",
"country" : "AC",
"latitude": 16.35,
"longitude": 31.45,
},
"type_of_conversion": "purchase"
}
,
{
"name": "conversion_event",
"created_at" : "2020-03-01 00:00:00",
"uuid" : 12314512441312355,
"campaign_name": "campaign_name9",
"campaign_channel": "channel1",
"meta":{
"app_name": "Linux",
"os_version": "n/a",
"country" : "DE",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "purchase"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-06 00:00:00",
"uuid" : 12314512441312355,
"campaign_name": "campaign_name9",
"campaign_channel": "channel1",
"meta":{
"app_name": "Linux",
"os_version": "n/a",
"country" : "DE",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registration"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-01 00:00:00",
"uuid" : 12314512441554433,
"campaign_name": "campaign_name6",
"campaign_channel": "channel5",
"meta":{
"app_name": "iOS",
"os_version": 11,
"country" : "AB",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-01 00:00:00",
"uuid" : 12314512441312312,
"campaign_name": "campaign_name1",
"campaign_channel": "channel1",
"meta":{
"app_name": "iOS",
"os_version": 11,
"country" : "ABC",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}
,
{
"name": "conversion_event",
"created_at" : "2020-01-01 00:00:00",
"uuid" : 12314512441312312,
"campaign_name": "campaign_name1",
"campaign_channel": "channel1",
"meta":{
"app_name": "iOS",
"os_version": 11,
"country" : "ABC",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}
,
{
"name": "conversion_event",
"created_at" : "2020-06-04 00:00:00",
"uuid" : 12314512441312312,
"campaign_name": "campaign_name3",
"campaign_channel": "channel2",
"meta":{
"app_name": "iOS",
"os_version": 11,
"country" : "ABC",
"latitude": 12.12,
"longitude": 12.12,
},
"type_of_conversion": "registrations"
}

]')::variant as var)

            SELECT var:name::string AS "name", meta.value:country::string AS "country", var:uuid::int AS "uuid",
            var:type_of_conversion::string AS "type_of_conversion",  var:campaign_name::string AS "campaign_name",
             meta.value:app_name::string AS "app_name"
                       FROM a
, LATERAL FLATTEN (var:meta) AS meta ;
wn9m85ua

wn9m85ua1#

真正的问题是你的数据,而不是你的代码。。在json数据中,您使用multple json obect数据作为单行,因此解析json时会出错:输入中有多个文档。所以snowflake建议在转换数据之前先对其进行处理[检查这个]
否则,您可以尝试从其中取出一个json对象。

相关问题