在Amazon或Quicksight中提取具有$前缀的特定嵌套属性

Question

我使用PostHog进行产品分析，并将一些事件数据导出到Amazon以及用于Quicksight的S3中。

在JSON的personal属性部分下，每个单独的属性都是嵌套的，但以$开头

我对SQL查询以及从JSON获得的具体细节都非常陌生。在Quicksight中使用parseJson

下面是来自PostHog的JSON示例

"properties": {
    "$active_feature_flags": [],
    "$browser": "Chrome",
    "$browser_version": 98,
    "$ce_version": 1,
    "$device_type": "Desktop",
    "$environment": "test",
    "$event_type": "click",
    "$lib": "web",
    "$lib_version": "1.17.8",
    "$os": "Mac OS X",
    "$pathname": "/events",
    "$plugins_deferred": [],
    "$plugins_failed": [],
    "$plugins_succeeded": [
        "First Event Today (4914)",
        "GeoIP (5539)"
    ],

我从几个来源寻求帮助，他们提到这并不是那么简单，因为一开始就有$符号。

所以我的问题是，

1. 如何在Redshift中查询此内容以成功提取$device_type和$os？
2. 如何使用亚马逊Quicksight中的parseJson提取相同的属性？

Answer 1

我可以回答第一条。

提供的json看起来是一个片段，并且看起来是无效的。因此，我删除了尾随'，‘，并使用SQL提供周围的'{}’。一旦它是有效的，json，就可以运行了：

create table test as select '"properties": {
    "$active_feature_flags": [],
    "$browser": "Chrome",
    "$browser_version": 98,
    "$ce_version": 1,
    "$device_type": "Desktop",
    "$environment": "test",
    "$event_type": "click",
    "$lib": "web",
    "$lib_version": "1.17.8",
    "$os": "Mac OS X",
    "$pathname": "/events",
    "$plugins_deferred": [],
    "$plugins_failed": [],
    "$plugins_succeeded": [
        "First Event Today (4914)",
        "GeoIP (5539)"
    ]
}' as json_text;

select json_extract_path_text('{' || json_text  ||'}', 'properties' ,'$device_type') as device_type,
                json_extract_path_text('{' || json_text  ||'}', 'properties' ,'$os') as os
from test;