0

I have a json string in bigquery that looks like that:

{"1":"eggs","2":"nuts","3":"fish"}

How could I extract all values without listing the keys? What I need is:

['eggs', 'nuts', 'fish']

I've tried [json_extract(json_string, '$[1]'), json_extract(json_string, '$[2]')] and it does the job but it won't work if the number of keys increases

user7586060
  • 1
  • You're probably going to have to implement your own function, such as here: https://stackoverflow.com/a/59490036/53341 *(Also, find whomever supplied you a dictionary/object instead of a list/array, and shoot them.)* – MatBailie Jan 21 '23 at 14:41
  • Using that custom function I'm able to get all distinct keys, but I'm not sure how could I use it to get the values? – user7586060 Jan 22 '23 at 13:06
  • Change the function to return values instead of keys, or use the keys as an input to JSON_EXTRACT(). – MatBailie Jan 22 '23 at 15:02

1 Answers1

0

I managed to do this using this query:

CREATE TEMP FUNCTION jsonObjectValues(input STRING)
RETURNS Array<String>
LANGUAGE js AS """
  RETURN Object.values(JSON.parse(input));
""";

SELECT
  jsonObjectValues(value) AS values
FROM my_dataset
user7586060
  • 1