时间戳

首页 > 解决方案 > 解析雪花中的json键值，其中键中有一个点

sql - 解析雪花中的json键值，其中键中有一个点

问题描述

{
  "deviceLocale": "en_US",
  "deviceSerialNumber": "xxxxxxxxxx",
  "eventSource": "abc",
  "ext.user.browser": "Mobile Safari",
  "ext.user.browser.version": "1.0.4",
  "ext.user.device.family": "iPhone", 
  "ext.user.os": "iOS",
  "ext.user.os.version": "1.3.0",
  "Timestamp": 158007896874 }

这是我拥有的示例 json..

在雪花中解析

```
select distinct
eve_id,
json_payload:ext.useragent.device.family::varchar as type,
json_payload:ext.useragent.os::varchar as osname,
json_payload:ext.useragent.os.version::varchar as os 
from XYZ table, lateral flatten (input => json_payload)
```

但是所有这三个字段都给出了 NULL 值，我看到的是 json 格式的数据。所以我猜解析是不对的。我在解析时知道雪花是否使用点或：然后它指的是嵌套键。但就我而言，我有一个没有嵌套键的简单 json。

任何想法？

标签： sqljsonsnowflake-cloud-data-platform

解决方案

首先，您可以将名称放在双引号中，如下所示：

SELECT parse_json('{
        "deviceLocale": "en_US",
        "deviceSerialNumber": "xxxxxxxxxx",
        "eventSource": "abc",
        "ext.user.browser": "Mobile Safari",
        "ext.user.browser.version": "1.0.4",
        "ext.user.device.family": "iPhone", 
        "ext.user.os": "iOS",
        "ext.user.os.version": "1.3.0",
        "Timestamp": 158007896874 }') AS json_payload,
    json_payload:"ext.user.device.family"::varchar as type,
    json_payload:"ext.user.os"::varchar as osname,
    json_payload:"ext.user.os.version"::varchar as os;

给出：

JSON_PAYLOAD    TYPE    OSNAME  OS
{    "Timestamp": 158007896874,    "deviceLocale": "en_US",    "deviceSerialNumber": "xxxxxxxxxx",    "eventSource": "abc",    "ext.user.browser": "Mobile Safari",    "ext.user.browser.version": "1.0.4",    "ext.user.device.family": "iPhone",    "ext.user.os": "iOS",    "ext.user.os.version": "1.3.0"  }    iPhone  iOS 1.3.0

或者您可以使用允许避免双引号的['']格式json_payload['ext.user.os.version']::varchar as os（如果您想避免这种情况）。

在您访问 SQL 时，您拥有json_payload:ext.useragent.device.family::varchar但该useragent部分仅user在您的 JSON 中。所以这会给你带来麻烦。

同样在您的示例中，您使用的是 LATERAL FLATTEN，但询问如何访问您正在展平的对象的展平成员。所以不需要扁平化。但是，如果您想要扁平化，那么您将在每个顶级项目中获得一行，此时您将想要过滤key.. 但我怀疑这不是您想要做的。但是，如果您是一个好主意，请给 flatten 起别名以帮助显示意图。

WITH jp AS (
    SELECT parse_json('{
      "deviceLocale": "en_US",
      "deviceSerialNumber": "xxxxxxxxxx",
      "eventSource": "abc",
      "ext.user.browser": "Mobile Safari",
      "ext.user.browser.version": "1.0.4",
      "ext.user.device.family": "iPhone", 
      "ext.user.os": "iOS",
      "ext.user.os.version": "1.3.0",
      "Timestamp": 158007896874 }') AS json_payload
)
SELECT 
    f.key,
    f.path,
    f.value
FROM jp, LATERAL FLATTEN (input => json_payload) f;

给出：

KEY PATH    VALUE
Timestamp   Timestamp   158007896874
deviceLocale    deviceLocale    "en_US"
deviceSerialNumber  deviceSerialNumber  "xxxxxxxxxx"
eventSource eventSource "abc"
ext.user.browser    ['ext.user.browser']    "Mobile Safari"
ext.user.browser.version    ['ext.user.browser.version']    "1.0.4"
ext.user.device.family  ['ext.user.device.family']  "iPhone"
ext.user.os ['ext.user.os'] "iOS"
ext.user.os.version ['ext.user.os.version'] "1.3.0"

推荐阅读