CODEWITHSUNDEEP
jsongoogle-bigqueryextract
Stephnk
Stephnk

Reputation: 1

Bigquery JSON String to columns and rows

I have a BigQuery table that contains a column that contains a JSON string. I am new to using both.

Within the JSON, there are key value pairs - but some are nested arrays and some are not. And the nesting is at different levels.

Example:

{
  "participant_id": "12345",
  "rd_visit_focus_area": [
    "Wt Management",
    " Eating Healthier meals",
    " Eating more fruits and vegetables",
    " decreasing salty foods",
    " decreasing sugary foods",
    " family meals",
    " home cooking "
  ],
  "demog_work_schedule": "",
  "Comments": "",
  "health": {
    "center": "Health Center 1",
    "height": "5 ft 10 in",
    "weight": "226 lbs",
    "conditions": [
      "Obesity",
      " cholesterol",
      " hypoparathyroidism"
    ]
  }
}

I've tried MANY solutions found online - most don't work - but the best was using the JSON functions below.

These 'sort of' work within Biqquery - but there 2 issues with using these functions:

  1. Not all key value pairs are extracted
  2. I need this SQL to execute inside Tableau and Tableau does not support using these functions
CREATE TEMP FUNCTION json_keys(input STRING) RETURNS Array<String>
LANGUAGE js AS """
  return Object.keys(JSON.parse(input));
""";

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

Is there a sql solution that does not use functions?

This is my first time posting a question here - so if I'm missing any necessary info to help solve this problem, please let me know!

Thank you in advance for your help!

Upvotes: 0

Views: 72

Answers (1)

sarath.mec
sarath.mec

Reputation: 22

You can create direct functions by removing the temp keyword. Then you would be able to use it as needed.

Upvotes: 0

Related Questions