在Redshift 6层深度中解析JSON

Question

我正在解析Redshift中的一个JSON字段，但是值有6层深。亚马逊网络服务表示，JSON_EXTRACT_PATH_TEXT有5个级别的限制。我曾短暂地尝试过cte，但由于SQL和JSON对我来说都是新的，所以我正在陷入困境。或者，我想知道最好的地方是不是在dbt。如果是这样的话，我可以看看那个兔子洞是不是没那么深。

代码：

{
  "overall": {
    "WE": {
      "A": {
        "WE: one": "1",
        "WE: two": "5",
        "Work: three": "1"
}}
}

预期结果：

WE  name value
--  ---- -----
A   one   1
A   two   5
A   three 1

Answer 1

首先，您的输入不是有效的JSON ...(例如:您有4个打开的{，但只有3个})。其次，您的输入/输出对看起来您要做的不仅仅是解嵌。例如，您的嵌套密钥"WE:one"被解析为"name": "one"。第三:给定您的预期输出，您应该在某个地方有一个记录数组：

{
  "overall": {
    "WE": {
      "A": [
        {"WE: one": "1"},
        {"WE: two": "5"},
        {"Work: three": "1"}
      ]
    }
  }
}

因此，您不能仅通过取消嵌套来解决您的问题。

话虽如此，您可以开始使用Redshift的JSON函数之一。从Redshift docs

JSON JSON_EXTRACT_PATH_TEXT返回由

字符串中的一系列路径元素引用的键:值对的值。

因此，在您的示例中，这将如下所示：

select 
    json_extract_path_text('{"overall": {"WE": {"A": {"WE: one": "1", "WE: two": "5", "Work: three": "1" }} }','overall', 'WE', 'A')
;

然后，您可以参考Return elements of Redshift JSON array on separate rows来分解数组。