I'm starting working on json columns in google bigquery. My aim is to implement a query which: extracts a given value in the json object based on path and returns its real SQL value. Example: given {"foo": {"bar": 123}}, I'd…
I am trying to run a cron job to execute a PHP script. The command for the cron I am trying to execute is /usr/local/bin/php -q /home/domain/public_html/apps/app.php >> /home/domain/public_html/logs/cron.log. When running the job, I get the following error: Fatal error:…
I have a table called temp with columns name,age. I need to update the schema, the new structure should be name,details,age. I can achieve the above using bq update proj:dataset.temp file/schema.json And also i need the age column at last…
I have a JSON data like this: {"payload":"{"custom":{"a":{"hs.dl":"hs:\/\/categories\/Z2lkOi8vc2hvcGlmeS9NZW51SXRlbS81NDM2Nzk0NDczODI=","hs.image":"https:\/\/cms-highstreetapp.imgix.net\/denham\/2023\/08\/0657f839-0045-49b0-ba89-fbde3c74f519\/montage20230818-1-kzy7bm.jpg","hs.body":"Reworked in the colours of the season.","hs.video_720":null,"hs.thumbnail":"https:\/\/cms-highstreetapp.imgix.net\/denham\/2023\/08\/0657f839-0045-49b0-ba89-fbde3c74f519\/montage20230818-1-kzy7bm.jpg","hs.title":"Free Move Chinos","hs.video_480":null,"hs.id":"HS_STUDIO-91428"},"i":"31d443e3-1874-4deb-b475-ac10c850451d"},"aps":{"mutable-content":1,"alert":"Free Move Chinos - Reworked in the colours of the season.","category":"category","sound":"default"}}"} Currently, I am replacing escaping characters this way: REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value, 'hs.', ''), '\"', '"'), '"{',…
Doing self join with same table with range condition. The below is sql statement. select current_rec .* ,compare_rec.employee_id as less_salary_employee from hr.employees current_rec left join hr.employees compare_rec on current_rec.hire_date < compare_rec .hire_date and current_rec.salary < compare_rec.salary order by current_rec .employee_id…
My sql statement is written using cross join lateral in postgres sql. But i want to execute this sql statement in google bigquery and bigquery doesn't support cross join lateral. How can i rewrite this sql for bigquery engine? select…
I have a table with the following structure in bigquery, id desc btjap {"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}} fxlol {"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}} nretx {"val":{}} The desc column contains multiple wildcard keys as value for the val key . I want to unpack the data into…
I need to do a one-time load (batch) from Azure to BigQuery and I am new in the Google Cloud environment. I noticed there are numerous ways to do this, but still isn't clear which option is the most efficient…
I have a JSON column in bigquery as such SELECT PARSE_JSON('{"a": [1,2,3], "b":[4,5,6]}') AS key_values; Is it possible to write a query such that I can get the following? I can have any number of keys so I cannot specify…
I'm getting an error when creating a table. can anyone tell me what I'm doing wrong ? DROP Table if exists #PercentPopulationVaccinated Create Table #PercentPopulationVaccinated ( Continent nvarchar(255), Location nvarchar(255), Date datetime, Population numeric, New_vaccinations numeric, RollingPeopleVaccinated numeric )