https://linen.dev logo
c

Chris (deprecated profile)

07/23/2021, 6:04 PM
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?
Copy code
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
c

charles

07/23/2021, 7:35 PM
it seems like it comes down to what BQ support, right? does BQ not support quotation marks in column names?
u

user

07/23/2021, 7:35 PM
if that's the case then it seems like you just got to normalize the column names.
u

user

07/23/2021, 7:35 PM
is that how you were thinking about it in the approach that "modifies" the data?
u

user

07/23/2021, 7:35 PM
or was that something else?
u

user

07/23/2021, 8:04 PM
Doing it in sql query with the way i proposed here modifies the column name but the content data too ... Which is not ideal
u

user

07/23/2021, 8:04 PM
ah
u

user

07/23/2021, 8:04 PM
If i do it in java i can apply it to the column name only (by properly parsing the json)
c

charles

07/23/2021, 8:05 PM
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?
u

user

07/23/2021, 8:05 PM
No it s pure bq constraint
u

user

07/23/2021, 8:06 PM
I dont know how to refer to a json field with quotes with jsonpath in biquery
u

user

07/23/2021, 8:07 PM
oh i see.
u

user

07/23/2021, 8:07 PM
Other desrinations do have some support for that
u

user

07/23/2021, 8:07 PM
how does java help?
u

user

07/23/2021, 8:08 PM
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
c

charles

07/23/2021, 8:13 PM
oh as in you would do it before you insert into the databse in the first place?
u

user

07/23/2021, 8:16 PM
With java yes (etl)
u

user

07/23/2021, 8:18 PM
Unless we can figure how to do it in bigquery in sql (elt)
u

user

07/23/2021, 8:18 PM
Have you tried to contact Google?
u

user

07/23/2021, 8:19 PM
I have the support package(the $150/month one), I can ask it for you.
u

user

07/23/2021, 9:56 PM
that's a good point. i think we have a support package, but if we don't we'll take you up on it.
u

user

07/23/2021, 9:56 PM
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 🤷‍♀️
c

charles

07/23/2021, 9:59 PM
I feel u
u

user

07/26/2021, 4:19 PM
@Chris (deprecated profile) what do you think? happy to discuss more if it will be helpful.
5 Views