如何在表中的varchar列中解析此JSON?雪花

Question

我有一个表，它有一个名为"message_json"；在该表中，我将这个json存储为varchar数据类型。

{
"request_id": "b53e7cc3-89b1-495b-aab0-e0dd6243b32e",
"quote_id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6",
"tenant_abbreviation": "ahs",
"tenant_id": "ee312e77-8463-44bd-ad7e-2cd4e75c9e3d",
"event_detail": {
"source": "Quote service",
"event_name": "quote_created",
"timestamp": {
"seconds": 1608236418,
"nanos": 290575000
},
"id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6"
},
"quote": {
"attribute": {
"contract.renewal": "false",
"contract.yearsOfService": "0",
"description": "xx 3X3 xx $xx DC SC",
"mktgSourceKey": "5fdc118555b95efff7d29f23",
"order.method": "eCom",
"originalSalesChannel": "",
"plan.id": "xxx",
"product.familyName": "combo",
"product.name": "xx xx COMBO $x DC SC",
"product.origin": "TX3C217D",
"property.address1": "xxx xx xx",
"property.address2": "",
"property.ageOfHome": "",
"property.city": "xxx",
"property.country": "USA",
"property.dwellingType": "1",
"property.dwellingTypeCode": "SINGLE FAMILY RESIDENCE",
"property.motherInLaw": "",
"property.sizeOfHome": "4900",
"property.state": "xxxx",
"property.unitType": "",
"property.unitValue": "",
"property.zip5": "xxxxx",
"property.zip9": "xxxxxx",
"salesChannel": "DC",
"serviceFee": "xxxx"
}
}

我正在尝试创建一个新表，将每个键：值对分配给一列。我尝试过parse_json(message_json(，但由于某种原因，它只返回以下内容。

{
"event_detail": {
"event_name": "xxxxxx",
"id": "77e49765-2b53-4d79-9442-8156b0bde3bc",
"source": "xxx xxx",
"timestamp": {
"nanos": 830472300,
"seconds": 1572679265
}
},
"quote_id": "77e49765-2b53-4d79-9442-8156b0bde3bc",
"request_id": "d8ad7a0a-f390-4660-8dc4-2838853d3846",
"tenant_abbreviation": "xxx",
"tenant_id": "ee312e77-8463-44bd-ad7e-2cd4e75c9e3d"
}

我也尝试过message_json:request_id，但我得到了这个错误函数"GET"的参数类型无效：(VARCHAR(16777216(，VARCHAR(10((

感谢提供的任何帮助

Answer 1

我不知道你的情况出了什么问题，但我可以通过添加一个右括号来修复问题中的json字符串：

create or replace temp table texts as
select '{
"request_id": "b53e7cc3-89b1-495b-aab0-e0dd6243b32e",
"quote_id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6",
"tenant_abbreviation": "ahs",
"tenant_id": "ee312e77-8463-44bd-ad7e-2cd4e75c9e3d",
"event_detail": {
"source": "Quote service",
"event_name": "quote_created",
"timestamp": {
"seconds": 1608236418,
"nanos": 290575000
},
"id": "7a760b81-2c9c-4f20-9453-f7b72d4e06c6"
},
"quote": {
"attribute": {
"contract.renewal": "false",
"contract.yearsOfService": "0",
"description": "xx 3X3 xx $xx DC SC",
"mktgSourceKey": "5fdc118555b95efff7d29f23",
"order.method": "eCom",
"originalSalesChannel": "",
"plan.id": "xxx",
"product.familyName": "combo",
"product.name": "xx xx COMBO $x DC SC",
"product.origin": "TX3C217D",
"property.address1": "xxx xx xx",
"property.address2": "",
"property.ageOfHome": "",
"property.city": "xxx",
"property.country": "USA",
"property.dwellingType": "1",
"property.dwellingTypeCode": "SINGLE FAMILY RESIDENCE",
"property.motherInLaw": "",
"property.sizeOfHome": "4900",
"property.state": "xxxx",
"property.unitType": "",
"property.unitValue": "",
"property.zip5": "xxxxx",
"property.zip9": "xxxxxx",
"salesChannel": "DC",
"serviceFee": "xxxx"
}
}
}' input;
select parse_json(input):request_id
from texts
-- "b53e7cc3-89b1-495b-aab0-e0dd6243b32e"

它甚至以所谓的"；缺少"；零件：

select parse_json(input):quote.attribute['property.address1']
from texts
-- "xxx xx xx"