How to extract a value in a JSON table on BigQuery?

Question

I have a JSON table which has over 30.000 rows. There are different rows like this:

JSON_columns
------------

{
    "level": 20,
    "nickname": "ABCDE",
    "mission_name": "take_out_the_trash",
    "mission_day": "150",
    "duration": "0",
    "properties": []
} 
{
    "nickname": "KLMNP",
    "mission_name": "recycle",
    "mission_day": "180",
    "properties": [{
        "key": "bottle",
        "value": {
            "string_value": "blue_bottle"
        }
    }, {
        "key": "bottleRecycle",
        "value": {
            "string_value": "true"
        }
    }, {
        "key": "price",
        "value": {
            "float_value": 21.99
        }
    }, {
        "key": "cost",
        "value": {
            "float_value": 15.39
        }
    }]
}

I want to take the sum of costs the table. But firtsly, I want to extract the cost from the table.

I tried the code below. It returns null:

SELECT JSON_VALUE('$.properties[3].value.float_value') AS profit
FROM `missions.missions_study`
WHERE mission_name = "recycle"

My question is, how can I extract the cost values right, and sum them?

Answer 1

Common way to extract cost from your json is like below.

WITH sample_table AS (
  SELECT '{"level":20,"nickname":"ABCDE","mission_name":"take_out_the_trash","mission_day":"150","duration":"0","properties":[]}' json
   UNION ALL
  SELECT '{"nickname":"KLMNP","mission_name":"recycle","mission_day":"180","properties":[{"key":"bottle","value":{"string_value":"blue_bottle"}},{"key":"bottleRecycle","value":{"string_value":"true"}},{"key":"price","value":{"float_value":21.99}},{"key":"cost","value":{"float_value":15.39}}]}' json
)
SELECT SUM(cost) AS total FROM (
  SELECT CAST(JSON_VALUE(prop, '$.value.float_value') AS FLOAT64) AS cost
    FROM sample_table, UNNEST(JSON_QUERY_ARRAY(json, '$.properties')) prop
   WHERE JSON_VALUE(json, '$.mission_name') = 'recycle' 
     AND JSON_VALUE(prop, '$.key') = 'cost'
);