CODEWITHSUNDEEP
google-bigquery
Lloyd Tabb
Lloyd Tabb

Reputation: 86

JSON_EXTRACT in BigQuery Standard SQL?

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

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

Upvotes: 3

Views: 11609

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

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

Upvotes: 4

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Not that I know of, but there is always workaround

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

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

Quick update

After cup of coffee, decided to complete this "exercise" by myself
Look as a good short term solution to me :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

Upvotes: 1

Related Questions