json 在BigQuery REGEXP_REPLACE中操作匹配的值，并使用替换该值

rvpgvaaj 于 2023-01-14 发布在其他

关注(0)|答案(1)|浏览(166)

我尝试使用大查询字符串函数来操作JSON（保存为字符串，因为它已经从firestore流到大查询），并将EPOCH时间戳（以秒为单位）替换为字符串形式的时间戳。
数据中的时间戳数量在每个JSON中可能会有所不同，因此不可能通过一系列替换来完成此操作，我需要基于可变数量的时间戳一次性完成此操作。
示例值：

{
    "createdAtTimestamp": {
        "_seconds": 1670337132,
        "_nanoseconds": 863000000
    },
    "phoneNo": "+44970000000",
    "phoneVerified": false,
    "updatedAtTimestamp": {
        "_seconds": 1672754379,
        "_nanoseconds": 112000000
    },
    "timestamp3": {
        "_seconds": 1669127206,
        "_nanoseconds": 112000000
    },
    "timestamp4": {
        "_seconds": 1672909833,
        "_nanoseconds": 112000000
    },
    "PIN": "$2b$10$gsgfdgfsdfdf"
}

我很接近了，我可以运行这个：

SELECT
REGEXP_REPLACE(data,ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(data,'"_seconds":([0-9]+)'),'|'),'\\0')
FROM 
  `tableXYZ`

因此，类似下面的方法应该可以奏效：

SELECT
,REGEXP_REPLACE(data,ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(data,'"_seconds":([0-9]+)'),'|'),cast(FORMAT_TIMESTAMP("%Y-%m-%dT%X",TIMESTAMP_SECONDS(cast('\\0' as bigint))) as string) )

但这会抛出一个错误，而且我找不到一种方法来处理正则表达式匹配的值（即\0）
有没有人知道这在大查询SQL中是如何实现的？
谢谢

JSON

来源：https://stackoverflow.com/questions/75108566/manipulate-the-matched-value-in-bigquery-regexp-replace-and-use-to-replace-the-v

1条答案

按热度按时间

vawmfj5a1#

正如Tonion所说，JSON函数更适合于对字符串文本执行操作。
但是可以将时间从秒转换为日期时间：

create temp function replace_json_seconds(s string, match string)
returns string
language js as 
"""
function replacer(match, a,secs, offset, string) {
  var d=new Date(new Date(secs * 1000));
  return a+'"'+d.toISOString().slice(0,-1)+'"';
}
return s.replace(RegExp('("'+match+'":) ([0-9]+)*', 'gi') , replacer );
//
""";

With tbl as (select

"""
{
    "createdAtTimestamp": {
        "_seconds": 1670337132,
        "_nanoseconds": 863000000
    },
    "phoneNo": "+44970000000",
    "phoneVerified": false,
    "updatedAtTimestamp": {
        "_seconds": 1672754379,
        "_nanoseconds": 112000000
    },
    "timestamp3": {
        "_seconds": 1669127206,
        "_nanoseconds": 112000000
    },
    "timestamp4": {
        "_seconds": 1672909833,
        "_nanoseconds": 112000000
    },
    "PIN": "$2b$10$gsgfdgfsdfdf"
}

""" data

)

select *,replace_json_seconds(data,"_seconds" ),

 from tbl

赞(0）回复(0）举报 2023-01-14

我来回答

json 在BigQuery REGEXP_REPLACE中操作匹配的值，并使用替换该值

1条答案

相关问题

热门标签

最新问答