How do I extract nested values from a JSON string in SQL on BigQuery?

beginnerprogrammerforever 0
Viewed 35

I have a column in a BigQuery table known as change_json_string, which contains two sets of values that I need to extract. Given below is a sample record from said column:

{"old_value": {"variants": {"652442770:1070774498": {"choice_groups": {"652442771": {"choices": {"1070774500": {"price": "0"}}}}}}}],
"new_value": {"variants": {"652442770:1070774498": {"choice_groups": {"652442771": {"choices": {"1070774500": {"price": "1"}}}}}}}}

I need to extract the price from old_value and new_value (in other words, I need to find out what old price was and what the new price is). I would like to create two separate columns that extract the old price and the new price respectively.

I tried using the following code but it returned null values:

SELECT JSON_QUERY(change_json_string,'$.old_value.variants.price') AS old_value,
       JSON_QUERY(change_json_string,'$.new_value.variants.price') AS new_value

I also tried using the following but again only saw null values:

SELECT JSON_QUERY(change_json_string,'$.old_value.variants[3].price') AS old_value,
       JSON_QUERY(change_json_string,'$.new_value.variants[3].price') AS new_value

Any help on this would be greatly appreciated, thank you.

nested google-bigquery sql json
1 Answers

Your json data seems have a typo ] at the end of first line. If it's been removed, below query will return old and new values.

DECLARE change_json_string DEFAULT """
{"old_value": {"variants": {"652442770:1070774498": {"choice_groups": {"652442771": {"choices": {"1070774500": {"price": "0"}}}}}}},
"new_value": {"variants": {"652442770:1070774498": {"choice_groups": {"652442771": {"choices": {"1070774500": {"price": "1"}}}}}}}}
""";

SELECT JSON_VALUE(change_json_string, '$.old_value.variants.652442770:1070774498.choice_groups.652442771.choices.1070774500.price') AS old_value,
       JSON_VALUE(change_json_string, '$.new_value.variants.652442770:1070774498.choice_groups.652442771.choices.1070774500.price') AS new_value;

+-----------+-----------+
| old_value | new_value |
+-----------+-----------+
|         0 |         1 |
+-----------+-----------+
Update - Cusom JSON Function
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS ARRAY<STRING> 
LANGUAGE js AS """
  try { 
    return jsonPath(JSON.parse(json), json_path);
  } catch (e) { return null; }
"""
OPTIONS (
    library="https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jsonpath/jsonpath-0.8.0.js.txt"
);

SELECT CUSTOM_JSON_EXTRACT(change_json_string, '$.old_value..price') AS old_value,
       CUSTOM_JSON_EXTRACT(change_json_string, '$.new_value..price') AS new_value;
Jaytiger0
Related