json - 如何在 BigQuery 中提取存储为字符串的 JSON 数组

Question

我有一个与此类似的 JSON 数组

[{"key":"Email","slug":"customer-email","value":"abc@gmail.com"},{"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"},{"key":"First Name","slug":"first-name","value":"abc"},{"key":"Last Name","slug":"last-name","value":"xyz"},{"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}]

但棘手的部分是，这个数组存储为字符串。所以我认为第一步是将字符串转换为数组，然后取消嵌套，然后按照 here 中的方法进行操作
我想知道这种方法是否可行，如果可行，我想我面临的挑战是将字符串转换为数组。如果没有，或者您有更有效的方法，请帮助。谢谢

Answer 1

你试过 json_extract_array

select json_extract_array(
  """[{"key":"Email","slug":"customer-email","value":"abc@gmail.com"},{"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"},{"key":"First Name","slug":"first-  name","value":"abc"},{"key":"Last Name","slug":"last-name","value":"xyz"},{"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}]""");