雪花 - 获取另一个对象中一个对象的键值 [英] Snowflake - Get value of key in an object within another object

问题描述

我正在使用 Snowflake 并将 json 文件从 Staging 环境加载到 ODS 环境.

I'm working with Snowflake and loading json files from a Staging environment to an ODS environment.

json 数据的结构如下:

The structure of the json data looks like this:

{"address": "921 Pearl St",  
 "attributes": {
    "Alcohol": "'beer_and_wine'",
    "Ambience": "{'touristy': False, 'hipster': False, 'romantic': False, 'divey': False, 'intimate': False, 'trendy': False, 'upscale': False, 'classy': False, 'casual': True}",
    "BikeParking": "True",
    "BusinessAcceptsBitcoin": "False",
    "BusinessAcceptsCreditCards": "True",
    "BusinessParking": "{'garage': False, 'street': True, 'validated': False, 'lot': False, 'valet': False}"
}

现在我正在尝试像这样在 Snowflake 中查询我的数据:

Now I'm trying to query my data in Snowflake like this:

SELECT json_data:attributes.BusinessParking.garage
FROM my_json_table; 

但我得到的只是空值.知道如何到达BusinessParking"的底层?领域?

but all I get are null values. Any idea how do I get to the bottom levels in the "BusinessParking" field?

谢谢！！！

推荐答案

经过大量研究后，我能够以我需要的方式查询字段.我发现我需要使用try_parse_json"具有这些字段的函数，因为它们被定义为字符串而不是实际的 json 对象.

After a lot of research I'm able to query the fields the way I needed to. I found out that I need to use the "try_parse_json" functions with those fields as they are defined as strings not actual json objects.

使用该函数后，我可以像这样解析特定字段:

After using the function I can parse specific fields just like this:

SELECT
 TRY_PARSE_JSON(
  jsondata: attributes.BusinessParking
 ): garage AS garage
FROM
 "RESTAURANT"."STAGING"."JSON_TABLE_BUSINESS"

Snowflake 的文档不是很好，但它以某种方式解释了您可以做什么:https://docs.snowflake.com/en/sql-reference/functions/try_parse_json.html

Snowflake's doc isn't that great but somehow it explains what you can do: https://docs.snowflake.com/en/sql-reference/functions/try_parse_json.html

这是另一种可以在需要时将字符串值转换为列的方法:https://sqlkover.com/cool-stuff-in-snowflake-part-3-split-and-flatten/

This is another way you could transform string values to columns in case you need it: https://sqlkover.com/cool-stuff-in-snowflake-part-3-split-and-flatten/

感谢@simeon-pilgrim 的所有评论！

Thanks for all the comments @simeon-pilgrim!

这篇关于雪花 - 获取另一个对象中一个对象的键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！