列的 Json 值

发布于 2025-01-15 23:21:19 字数 1408 浏览 2 评论 0原文

我是大查询的新手，我正在尝试将 json 值的行解析为列，单行中的 json 值看起来像这样

  {
      "event_params": [{
        "key": "entrances",
        "value": {
          "string_value": null,
          "int_value": "1",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "ga_session_id",
        "value": {
          "string_value": null,
          "int_value": "1647758422",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_screen_class",
        "value": {
          "string_value": "Home",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_event_origin",
        "value": {
          "string_value": "auto",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "ga_session_number",
        "value": {
          "string_value": null,
          "int_value": "775",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_screen_id",
        "value": {
          "string_value": null,
          "int_value": "4463573641295231098",
          "float_value": null,
          "double_value": null
        }
      }]

}

我不知道如何解析列中的每一行，我希望表格看起来像这样
关键|价值|字符串值|整数值|浮点值|双值 |
有办法做到吗？，谢谢

原文

I am new to big query, i am trying to parse rows of json values to columns ,json value in a single row looks like this

  {
      "event_params": [{
        "key": "entrances",
        "value": {
          "string_value": null,
          "int_value": "1",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "ga_session_id",
        "value": {
          "string_value": null,
          "int_value": "1647758422",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_screen_class",
        "value": {
          "string_value": "Home",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_event_origin",
        "value": {
          "string_value": "auto",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "ga_session_number",
        "value": {
          "string_value": null,
          "int_value": "775",
          "float_value": null,
          "double_value": null
        }
      }, {
        "key": "firebase_screen_id",
        "value": {
          "string_value": null,
          "int_value": "4463573641295231098",
          "float_value": null,
          "double_value": null
        }
      }]

}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吲‖鸣 2025-01-22 23:21:19

假设您的 json 对象位于一列中，您可以使用 json 函数 json_extract_array 结合 unnest 来获取数组的所有元素作为一行。之后，您可以选择另一个 json 函数提取数组每个元素的值。

select
    json_value(events, "$.key") as key,
    json_value(events, "$.value.string_value") as string_value,
    json_value(events, "$.value.int_value") as int_value,
     .
     .
     .
from
  example, unnest(json_extract_array(json_payload_col, "$.event_params")) as events

Assuming your json object is in a column, you can use the json function json_extract_array combined with unnest to get all the elements of your array as a row. After that, it's up to you to choose another json function to extract the value of each element of the array.

select
    json_value(events, "$.key") as key,
    json_value(events, "$.value.string_value") as string_value,
    json_value(events, "$.value.int_value") as int_value,
     .
     .
     .
from
  example, unnest(json_extract_array(json_payload_col, "$.event_params")) as events

回复收藏 0 原文

~没有更多了~