How to extract the first key value pair in a JSON object with BigQuery

Question

I am trying to extract values of a certain key in a JSON object with BigQuery. However, the key is unique for each record in the table as shown in the picture. Keys and values

My JSON Object is structured like this: Object's structure

(Note: I am trying to unnest regularHours array that is the value of this unique key.)

I tried using - "SELECT JSON_EXTRACT(response.data, '$.menus') FROM MyDataSet.UberEats" This only gives me the key and its values but I'm only after unnesting the array value of this key and I don't know how to proceed further. (Note: response is the field name)

Answer 1

Please try the following UDF to convert the Dictionary object, as we cannot refer to the Dynamic keys in the Statically typed SQL statement

CREATE TEMPORARY FUNCTION dictToArray(input JSON)
RETURNS JSON
LANGUAGE js AS """
  return Object.entries(input).map(([id, values]) => ({ id, values }));
""";

-- Direct Function
SELECT
  dictToArray(response)
FROM (SELECT JSON '{ "ID1": [10,11,12,13], "ID2": [20,21,22,23,24], "ID3": [31,32,33,34] }' AS response

Output
------
[{"id":"ID1","values":[10,11,12,13]},{"id":"ID2","values":[20,21,22,23,24]},{"id":"ID3","values":[31,32,33,34]}]

--Unnest Operation with Bigquery Native Types
SELECT
    SAFE.STRING(item.id) AS id,
    SAFE.INT64(value) AS values
FROM (SELECT JSON '{ "ID1": [10,11,12,13], "ID2": [20,21,22,23,24], "ID3": [31,32,33,34] }' AS response)
     LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(dictToArray(response), '$')) AS item
     LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(item.values)) AS value

/*----+--------*
| id  | values |
+-----+--------|
| ID1 | 10     |    
| ID1 | 11     |
| ID1 | 12     |
| ID1 | 13     |
| ID2 | 20     |
| ID2 | 21     |
| ID2 | 22     |
| ID2 | 23     |
| ID2 | 24     |
| ID3 | 31     |
| ID3 | 32     |
| ID3 | 33     |
| ID3 | 34     |
+-----+-------*/