JSON_EXTRACT in BigQuery Standard SQL?

I'm converting some SQL code from BigQuery to BigQuery Standard SQL.

我将一些SQL代码从BigQuery转换为BigQuery标准SQL。

I can't seem to find JSON_EXTRACT_SCALAR in Bigquery Standard SQL, is there an equivalent?

在Bigquery Standard SQL中，我似乎找不到JSON_EXTRACT_SCALAR，它是否具有等价物?

2 个解决方案

#1

Edit: we implemented the JSON functions a while back. You can read about them in the documentation.

编辑:我们以前实现过JSON函数。您可以在文档中了解它们。

#2

Not that I know of, but there is always workaround

据我所知并不是这样，但总有解决办法

Let's assume we want to mimic example from JSON_EXTRACT_SCALAR documentation

假设我们想要模拟json_extract_标量文档中的示例。

SELECT JSON_EXTRACT_SCALAR('{"a": ["x", {"b":3}]}', '$.a[1].b') as str

Below code does same

下面的代码相同

CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING)
RETURNS STRING
LANGUAGE js AS """
  try { var parsed = JSON.parse(json);
  } catch (e) { return null }
  return parsed.a[1].b;
""";

SELECT CUSTOM_JSON_EXTRACT('{"a": ["x", {"b":3}]}') AS str

I think this can be good starting point to experiment with
see more for Scalar UDF in BigQuery Standard SQL

我认为这可以作为在BigQuery Standard SQL中尝试查看更多标量UDF的良好起点

Quick update

快速更新

After cup of coffee, decided to complete this "exercise" by myself
Look as a good short term solution to me :o)

喝完咖啡，决定自己完成这个“锻炼”，对我来说是一个很好的短期解决方案:o)

CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS STRING
LANGUAGE js AS """
  try { var parsed = JSON.parse(json);
  } catch (e) { return null }
  return eval(json_path.replace("$", "parsed"));
""";

SELECT 
  CUSTOM_JSON_EXTRACT('{"a": ["x", {"b":3}]}', '$.a[1].b') AS str1,
  CUSTOM_JSON_EXTRACT('{"a": ["x", {"b":3}]}', '$.a[0]') AS str2,
  CUSTOM_JSON_EXTRACT('{"a": 1, "b": [4, 5]}', '$.b') AS str3

#1