Any BigQuery SQL user up to solve a challenge? 🎁 How do you cleanly extract a json column from this example? 1. I have one solution that is not so acceptable because it modifies the json value data… 2. the other twos are throwing errors from BigQuery • But in ideal, I would want a column named:

column_'with"_quotes

(which may not be possible in BQ) • or maybe I might be ok with another name such as

column__with__quotes

• and my row should still have the value of:

How to extract this json "column_'with"_quotes" in BigQuery?

with example_json_data as (
  select
    "{'column_\'with\"_quotes': 'How to extract this json \"column_\'with\"_quotes\" in BigQuery?'}" as nested_json
)
select 
  json_extract(
      replace(`nested_json`, "column_'with\"_quotes", "column__with__quotes"), "$['column__with__quotes']" -- but i don't want to modify the data value!
      --`nested_json`, '$."column_\'with\"_quotes"' -- throws error: Invalid token in JSONPath at: ."column_'with"_quotes"
      --`nested_json`, "$['column_\'with\"_quotes']" -- throws error: Invalid token in JSONPath at: ['column_'with"_quotes']
    ) as column__with__quotes
from example_json_data

it seems like it comes down to what BQ support, right? does BQ not support quotation marks in column names?

if that's the case then it seems like you just got to normalize the column names.

is that how you were thinking about it in the approach that "modifies" the data?

or was that something else?

Doing it in sql query with the way i proposed here modifies the column name but the content data too ... Which is not ideal

ah

If i do it in java i can apply it to the column name only (by properly parsing the json)

yeah. modifying the value doesn't seem great. and we have to modify the value in the sql query because of a dbt implementation constraint?

No it s pure bq constraint

I dont know how to refer to a json field with quotes with jsonpath in biquery

oh i see.

Other desrinations do have some support for that

how does java help?

It s valid json where quotes are escaped so i can parse from string to json, normalise just the column/field name and reserialise to string

oh as in you would do it before you insert into the databse in the first place?

With java yes (etl)

Unless we can figure how to do it in bigquery in sql (elt)

Have you tried to contact Google?

I have the support package(the $150/month one), I can ask it for you.

that's a good point. i think we have a support package, but if we don't we'll take you up on it.

i kinda think just doing it in java isn't the end of the world for the bigquery-normalized destination? i guess for normal bigquery it feels weirder, but if that's what we gotta do 🤷‍♀️

I feel u

@Chris (deprecated profile) what do you think? happy to discuss more if it will be helpful.