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?

CodePudding user response：

You may try and consider below approach using

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