CODEWITHSUNDEEP
google-bigquery
Kamila Nurlanova
Kamila Nurlanova

Reputation: 35

How to extract in BigQuery

How to extract the word followed by location_name from an example:
{'type': ['city_group'], 'location_name': ['Costa Del Sol'], 'cities_groups_ids': ['3'], 'bedrooms': ['1:']}

I would like to create column called "location" where I find "Costa Del Sol"

Upvotes: 0

Views: 63

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Try json_value_array:

SELECT JSON_VALUE_ARRAY(
    "{'type': ['city_group'], 'location_name': ['Costa Del Sol'], 'cities_groups_ids': ['3'], 'bedrooms': ['1:']}",
    "$.location_name"
) AS locations_array;

Add [OFFSET(0)] if you need only the first value from the location_name list:

SELECT JSON_VALUE_ARRAY(
    "{'type': ['city_group'], 'location_name': ['Costa Del Sol'], 'cities_groups_ids': ['3'], 'bedrooms': ['1:']}",
    "$.location_name"
)[OFFSET(0)] AS location;

Upvotes: 1

Related Questions