我想将string BLOB解析为JSON，修改一些字段并将其解析回string BIGQUERY

Question

所以我有一个列，其中包含JSON作为字符串BLOBs。例如,

{
    "a": "a-1",
    "b": "b-1",
    "c":
    {
        "c-1": "c-1-1",
        "c-2": "c-2-1"
    },
    "d":
    [
        {
            "k": "v1"
        },
        {
            "k": "v2"
        },
        {
            "k": "v3"
        }
    ]
}

现在我的用例是解析关键字b，散列关键字b的值，将其分配回关键字b，并将其作为字符串存储在Bigquery中。

我最初尝试了一种懒惰的方法，我只使用Bigquery中的json_extract_scalar函数提取键b，而对于其他键(如c和d-我不想修改它们)，我使用json_extract函数。然后，在对关键字b进行散列之后，我将其转换回字符串。以下是查询-

SELECT
  TO_JSON_STRING( 
      STRUCT( 
          json_EXTRACT(COL_NAME, "$.a") AS a,
        MD5(json_extract_scalar(_airbyte_data,"$.b")) AS b,
          json_EXTRACT(COL_NAME,"$.c") AS c,
          json_EXTRACT(COL_NAME,"$.d") AS d ) )
FROM
  dataset.TABLE

但是这个查询的问题是，由于TO_JSON_STRING (我尝试在STRUCT之上使用CAST AS string，但不支持)，JSON对象被转换为字符串和双引号。例如，此查询的输出行如下所示：

{
    "a": "a-1",
    "b": "b-1",
    "c":
    "{
        \"c-1\": \"c-1-1\",
        \"c-2\": \"c-2-1\"
    }",
    "d":
    "[
        {
            \"k\": \"v1\"
        },
        {
            \"k\": \"v2\"
        },
        {
            \"k\": \"v3\"
        }
    ]"
}

如果我在每个键(和每个嵌套键)上使用JSON_EXTRACT和JSON_EXTRACT_SCALAR函数，我可以获得所需的输出，但这种方法不可伸缩，因为我有近200个键，其中许多键嵌套在2-3层深。有没有人能建议一个更好的方法来实现这一点？提亚

Answer 1

这应该是可行的

declare _json string default """{
    "a": "a-1",
    "b": "b-1",
    "c":
    {
        "c-1": "c-1-1",
        "c-2": "c-2-1"
    },
    "d":
    [
        {
            "k": "v1"
        },
        {
            "k": "v2"
        },
        {
            "k": "v3"
        }
    ]
}""";

SELECT regexp_replace(_json, r'"b": "(\w+)-(\w+)"',concat('"b":',TO_JSON_STRING( MD5(json_extract_scalar(_json,"$.b")))))

输出

{
    "a": "a-1",
    "b":"sdENsgFsL4PBOyX8sXDN6w==",
    "c":
    {
        "c-1": "c-1-1",
        "c-2": "c-2-1"
    },
    "d":
    [
        {
            "k": "v1"
        },
        {
            "k": "v2"
        },
        {
            "k": "v3"
        }
    ]
}

如果您需要特定的正则表达式，请指定b值的示例。