json - 谷歌数据工作室 REGEX_EXTRACT json
问题描述
我正在尝试通过在 Google DataStudio 中创建一个计算字段并解析一些 json 来创建一个新字段。但是,如果我使用 REGEX_EXTRACT,我一直收到一条错误消息,提示无法从基础数据集中获取数据
我正在使用的公式:
REGEXP_EXTRACT(json,'(?<="received_at":")(.*?)(?=")')
json字符串:
{"end_device_ids":{"device_id":"eui-a81758fffe0600cc","application_ids":{"application_id":"3cc54cfd-a809-42e3-89bb-4497ec67fe42"},"dev_eui":"A81758FFFE0600CC","join_eui":"0000000000000000","dev_addr":"260B1B1E"},"correlation_ids":["as:up:01FG833XJCCNBGDDNDYDRV9SRR","gs:conn:01FG16JYQBRKZC3K8HXE6CWK89","gs:up:host:01FG16JZSSC6GWA8TNY733T5GK","gs:uplink:01FG833XBKQGR83P9MTXSEQQHE","ns:uplink:01FG833XBR0NHHAM079T00ZC76","rpc:/ttn.lorawan.v3.GsNs/HandleUplink:01FG833XBR8SJYB9BMX8ZC61YY","rpc:/ttn.lorawan.v3.NsAs/HandleUplink:01FG833XJBN6BQX9HP4MYK51YR"],"received_at":"2021-09-23T01:08:02.252783252Z","uplink_message":{"session_key_id":"AXvkNZkkbAdO+pD5hVSLXg==","f_port":5,"f_cnt":1230,"frm_payload":"AQDoAjwHDjY=","decoded_payload":{"humidity":60,"temperature":23.2,"vdd":3638},"rx_metadata":[{"gateway_ids":{"gateway_id":"iot-stadslab-1","eui":"A84041FFFF1F9951"},"time":"2021-09-23T01:08:06.175212Z","timestamp":2063422380,"rssi":-125,"channel_rssi":-125,"snr":-8.2,"location":{"latitude":51.69522207692182,"longitude":5.293667793175701,"altitude":15,"source":"SOURCE_REGISTRY"},"uplink_token":"ChwKGgoOaW90LXN0YWRzbGFiLTESCKhAQf//H5lREKyn9dcHGgsI8qavigYQroDmECDgr7vshpo1","channel_index":4}],"settings":{"data_rate":{"lora":{"bandwidth":125000,"spreading_factor":10}},"data_rate_index":2,"coding_rate":"4/5","frequency":"867300000","timestamp":2063422380,"time":"2021-09-23T01:08:06.175212Z"},"received_at":"2021-09-23T01:08:02.040299926Z","consumed_airtime":"0.370688s","network_ids":{"net_id":"000013","tenant_id":"ttn","cluster_id":"ttn-eu1"}}}
我的正则表达式在 Regex101 中按预期工作。为什么我的公式不起作用?如果我尝试使用该REGEXEXTRACT(A2, "(?<=\"received_at\":\").*?(?=\")")
功能在谷歌表格中手动添加一列,我也会收到一个错误,它什么也没告诉我。对我的问题的任何解决方案表示赞赏。
先感谢您。
解决方案
你的正则表达式是
=regexextract(A2,"""received_at"":"".*?""")
推荐阅读
- excel - Excel VBA。什么时候需要激活工作表?
- php - 使用单选按钮返回布尔值 true 还是 false?
- javascript - 在 PhantomJS 中使用类名创建嵌套元素
- php - 如何在php中获取搜索结果
- php - 使用 PHP 在 MySQL 中插入图像时出错
- javascript - 如何将 dropzone 放在表单中但仍允许原始表单提交?
- c# - 从 Azure Active Directory 获取 MVC Web App 中的 samAccountName 名称
- haskell-stack - 如何显示`stack haddock`的进度?
- jquery - 当使用 Laravel 取消选择另一个框时,取消选择“全选”复选框。
- php - PHP MariaDB PDO 计算重复值