时间戳

首页 > 解决方案 > RedShift：嵌套 json 的一些问题

amazon-redshift - RedShift：嵌套 json 的一些问题

问题描述

我有下一个 JSON：

{"promptnum":4,"corpuscode":"B0014","prompttype":"video","skipped":false,"transcription":"1","deviceinfo":{"DEVICE_ID":"exynos980","DEVICE_MANUFACTURER":"samsung","DEVICE_SERIAL":"unknown","DEVICE_DESIGN":"a51x","DEVICE_MODEL":"SM-A5160","DEVICE_OS":"android","DEVICE_OS_VERSION":"10","DEVICE_CARRIER":"","DEVICE_BATTERY_LEVEL":"70.00%","DEVICE_BATTERY_STATE":"unplugged","Current App Version":"1.1.0","Current App Build":"6"}}

我想从 1 级和 2 级获取值。

第一级："promptnum":4,"corpuscode":"B0014","prompttype":"video","skipped":false,"transcription":"1","deviceinfo":...

2级：

"deviceinfo":{"DEVICE_ID":"exynos980","DEVICE_MANUFACTURER":"samsung","DEVICE_SERIAL":"unknown","DEVICE_DESIGN":"a51x","DEVICE_MODEL":"SM-A5160","DEVICE_OS":"android","DEVICE_OS_VERSION":"10","DEVICE_CARRIER":"","DEVICE_BATTERY_LEVEL":"70.00%","DEVICE_BATTERY_STATE":"unplugged","Current App Version":"1.1.0","Current App Build":"6"}

当我用

SELECT d.*
FROM (
  SELECT c.json_parse, c.json_parse.deviceinfo AS device_info
  FROM (
      SELECT JSON_PARSE(file_attr)
      FROM public.dc_ac_files
  ) AS c) AS d

它运作良好。

但是当我尝试从第二级获取值时

SELECT d.*, l.DEVICE_ID
FROM (
  SELECT c.json_parse, c.json_parse.deviceinfo AS device_info
  FROM (
      SELECT JSON_PARSE(file_attr)
      FROM public.dc_ac_files
  ) AS c) AS d, d.device_info AS l

它不起作用 - 没有错误，也没有数据。

如果我知道，这是解析嵌套 json 的正确方法，但它对我不起作用。

你能帮助我吗？

标签： amazon-redshift

解决方案

维克多，你有几个问题。首先，符号“AS d, d.device_info AS l”用于在您的超级数据中取消嵌套数组。您没有任何要取消嵌套的数组，因此这将返回零行。

对于所有列名，第二个 Redshift 默认为小写，因此 DEVICE_ID 被视为 device_id。您可以通过将 enable_case_sensitive_identifier 连接变量设置为 true 并引用所有需要大写字符的列名来启用区分大小写的列名。“将 enable_case_sensitive_identifier 设置为 true；” 并将 l.DEVICE_ID 更改为 l."DEVICE_ID"。

您的查询中还有不需要的图层。

将所有这些放在一起，您可以运行：

SELECT l, l.deviceinfo, l.deviceinfo."DEVICE_ID" 
FROM (
    SELECT JSON_PARSE(file_attr) AS l
    FROM public.dc_ac_files
) AS c

您也不需要 SUPER 数据类型来执行此操作。这可以通过 json 字符串解析函数来完成。

SELECT file_attr, json_extract_path_text(file_attr, 'deviceinfo') as deviceinfo, json_extract_path_text(file_attr, 'deviceinfo','DEVICE_ID') as device_id
FROM public.dc_ac_files

推荐阅读