CODEWITHSUNDEEP
sqljsongoogle-bigquery
renaudb3
renaudb3

Reputation: 80

BigQuery >> Extract Value for Dictionary Key in JSON Object

I have a string object in a table column with structure:

[{"__food": "true"},{"item": "1"},{"toppings": "true"},{"__discount_amount": "4.95"},{"__original_price": "24.95"}]

How can I extract the value true from the toppings key from this?

I tried turning it into JSON first but json_extract(parse_json(string_object_column), '$.toppings') just returns null

The closest I got was keeping it as a string and doing json_extract(string_object_column, '$[0]')

Which gets me: {"toppings":"true"}

Is this doable without unnesting?

Upvotes: 0

Views: 357

Answers (1)

Scott B
Scott B

Reputation: 2944

You may try and consider below approach using REGEXP_EXTRACT:

SELECT REGEXP_EXTRACT('[{"__food": "true"},{"item": "1"},{"toppings": "true"},{"__discount_amount": "4.95"},{"__original_price": "24.95"}]', r'"toppings": "(\D+)"}') as EXTRACT_TOPPINGS

OUTPUT:

enter image description here

You may just update the REGEX to make it more strict based on your use case.

Upvotes: 1

Related Questions