Summary
The user is experiencing issues with JSON data transformation in the Airbyte connector for transferring data from ElasticSearch to BigQuery. They provided examples of code that did not work as expected and mentioned a workaround that worked. They are seeking assistance in fixing the transformation issue.
Question
Hi all,
I am using Airbyte for ElasticSearch to BigQuery. Airbyte not transforming well json data. I am trying to say that transformation is not working well in the “Raw Table Dataset Name” to “Default Dataset ID” stage with json data. For example i have ShotID in _airbyte_data. Airbyte extracts with below code
PARSE_JSON(CASE
WHEN JSON_QUERY(_airbyte_data, ‘$.“ShotID”’) IS NULL
OR JSON_TYPE(PARSE_JSON(JSON_QUERY(_airbyte_data, ‘$.“ShotID”’), wide_number_mode=>‘round’)) != ‘array’
THEN NULL
ELSE JSON_QUERY(_airbyte_data, ‘$.“ShotID”’)
END, wide_number_mode=>‘round’)
as ShotID,
But this code returns null value.
If i extracted with using JSON_EXTRACT_SCALAR(_airbyte_data,‘$.ShotID’) as “ShotID”, i works well.
Is it possible to fix that issue?
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.