Extract components of a nested Array/STRUCT JSON string field in BigQuery-CodePudding

I have a String field in JSON format that I am trying to extract the stripe decline code from. An example of the field is below:

{"errors":[{"message":"Your card has insufficient funds.","type":"payment","code":"card_declined","decline_code":"insufficient_funds","gateway":"stripe","stripe":{"type":"card_error","code":"card_declined","decline_code":"insufficient_funds","message":"Your card has insufficient funds.","charge":"ch_3JodUAHkqql8g8ta1ADf5fBf"}}]}

I have tried various combinations of UNNEST but continue to get an error message. I think that the issue is related to the fact the field is a combination of various STRUCTS/Arrays but had no luck extracting what I need. Any help would be greatly appreciated!

CodePudding user response：

I think I found a solution. A little hacky, but I did the following in a CTE;

REPLACE(REPLACE(JSON_EXTRACT(error_message, '$.errors'),"[",""),"]","") as struct_1

And then took another JSON_EXTRACT of this;

JSON_EXTRACT(struct_1,'$.stripe.decline_code')

CodePudding user response：

Consider below approach - non hacky one :o)

select json_extract_scalar(error, '$.stripe.decline_code') as decline_code
from your_table, 
unnest(json_extract_array(error_message, '$.errors')) error

if applied to sample data in your question - the output is