Solved: Transform a Bigquery table to list of json on data... - Page 2

brown-will · 03-01-2024 03:27 AM

Hello,

I have a script on definition/query.sqlx:

with data as (SELECT array_agg(struct(obj,val)) as x FROM table) SELECT to_json_string(x) as json FROM data .

The result of this query is json list :[{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}] for exemple , I would like to assign the result of the query to my function created on includes/functions.js :

function tagValues(results, tagName) {

if (!Array.isArray(results)) {

console.error("Input is not an array");

return {};

}

const tagValues = {};

results.forEach(row => {

tagValues[row.tag] = row.value;

});

return tagValues[tagName];

}, but when i write this query select ${functions.tagValues(ref("query"), "t1")} I have the error, select [object , object], It is because the result of the query is not a list of json but a table, is it possible to convert the result of the query to a list of json so that it can be taken on my function ? The result that I expect is : select 'ok' . Thanks

brown-will

Thanks nceniza and kolban

Here is a solution:

I have created a pyhton script that can execute the query and produce a string : q = '[{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}]'

The q string value is stored in a json file in dataform project. This task can be automated using airflow (I use the operator DataformWriteFileOperator to create the json file), for exemple : includes/q_string.json

The path of the json file can be considered as a variable in the dataform.json file

{

"defaultSchema": "dataform",

"assertionSchema": "dataform_assertions",

"warehouse": "bigquery",

"defaultDatabase": "",

"defaultLocation": "",

"vars" : {

"q": "includes/q_string.json"

}

the q variable can be called as a constant in sqlx file too in the test.sqlx file

config {

type : 'table'

}

js {

const q_value = require(dataform.projectConfig.vars.q);

}

select f( q_value, "t1") as col ==> select 'ok' as col

the compilation give the right answer.

We have f( q_value, "t1") = 'ok'

Note that the f function is the one on my first post.

View solution in original post

Transform a Bigquery table to list of json on dataform