0

I have a JSON nested Data in column name response of a table name data_1 as i am using JSON_EXTRACT function in my google bigquery but don't know key. talking about each row have its own key (https://i.stack.imgur.com/f1IcE.png) how to access and extract under the unique id key of nested json. as i have done till response.data.menus.

SELECT response.data.menus FROM tablename LIMIT 1000;
narendra-choudhary
  • 4,582
  • 4
  • 38
  • 58
akshat chourasia
  • 9
  • 2

1 Answers1

0

you can try using JSON_EXTRACT_SCALAR or JSON_EXTRACT with a wildcard character "". Wildcard character "" in JSONPath expressions can represent any field in a JSON object or any element in a JSON array.

SELECT 
  JSON_EXTRACT(response, '$.data.menus.*') as menu_data 
FROM 
  tablename 
LIMIT 1000;

But if you need to extract the unique ID as well, you'd have to do that outside of SQL, in a language that allows you to loop over keys in JSON, such as Python or JavaScript.

{
  "data": {
    "menus": [
      {"id": "unique_id1", "value": "value1"},
      {"id": "unique_id2", "value": "value2"}
      // ...
    ]
  }
}

--

SELECT 
  JSON_EXTRACT(response, '$.data.menus') as menu_data 
FROM 
  tablename 
LIMIT 1000;
Maria Teresa
  • 36
  • 2