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

Question

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!

M_rich_bark · Accepted Answer · 2021-11-25 16:42:06Z

1

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')

answered Nov 25, 2021 at 16:42

M_rich_bark

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mikhail Berlyant Over a year ago

creative but ... see non-hacky approach in my answer

Mikhail Berlyant · Accepted Answer · 2021-11-25 17:47:49Z

1

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

answered Nov 25, 2021 at 17:47

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

1 Comment

Mikhail Berlyant Over a year ago

Have you tried? Did it work for you?

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related