Transform a Bigquery table to list of json on dataform

[brown-will]

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

[nceniza]

I cant replicate your code but you are possibly looking for converting BQ table to JSON Data ? I would suggesting this Documentation how Google SQL is working with JSON: 

https://cloud.google.com/bigquery/docs/json-data#convert_schematized_data_to_json

[brown-will]

Here is the code : 

definition/query.sqlx : 

config {

    type: 'table'

}

 with table as ( select "t1"as obj, "'ok'" as val

                union all

                select "t2" as obj, "'ko'" as val),

              data as (SELECT  array_agg(struct(obj,val)) as x

                      FROM  table)

              SELECT    to_json_string(x) as json FROM data

The result is :  [{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}]

 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.obj] = row.val;

    });

    return tagValues[tagName];

}

module.exports = {tagValues}

When I call the functions tagValues on definition/test.sqlx file for exemple I have this result : 

select ${functions.tagValues( [{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}],"t1")} the result is select 'ok'.

I have replace results from the function by [{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}] and tagName by "t1"  manually, but that is not what I want.  What I want is to apply the result of the query to a variable so that it can be use in the function , here is what i did , ref("query") is a table not an array 

SELECT ${functions.tagValues( ref("query"),"t1")}.

I got the error :  SELECT [object Object]

Only TagName from the function can be replaced by a value from val column from the query result  manually.

So my question is how can I assign the result of the query (query.sqlx) to a variable so that i can use it in the function (tagValues in functions.js file)  without doing copy and paste the result of query. 

 

[kolban]

Can you maybe describe what you are trying to achieve from a logical perspective without a lot of code.   I see you have some SQL that returns some values and I see you have JavaScript that takes some input.  The puzzle in my mind is what you are trying to achieve.  Can you paint the high level picture of your design/logic.  That will help (at least me) get a handle on the semantics and purpose of the code.

[brown-will]

Yes of course.  here is the data of my TABLE 

OBJ	VAL
t1	'ok'
t2	'ko'

What I am trying to acheive is to get the value from the column VAL knowing  value from OBJ column and data from table after transforming data on the table to an array (list of json).

It is basically a function like this  f (q, x) = y  ==> f(q, t1) = 'ok' ; f(q, t2) = 'ko'

My inputs are  : t1 , t2 value from OBJ column  and the TABLE

The query will transform the data from the table to an array

q : [{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}]

I have got the result of what I want to acheive by copy and paste the q result on my function. so what I did is : f([{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}], "t1") = 'ok' 

It is working fine.

But i don't want to copy and paste q value on the function, I would like to get the q value automatically from the result of the query  as a variable on my function : f(q, "t1"), q is the variable which come from the result of the query .

It means that the result of the query will be call on the function, without any copy and paste.

Note that I am working on DATAFORM : 

For exemple we can call a table  as a variable. It would be the same for what I would like to acheive, call the result of the query as variable. 

It is simple if I consider my table as a dataframe  df: the result of the query will  be this table 

json

q

value of q is : [{"obj":"t1","val":"'ok'"},{"obj":"t2","val":"'ko'"}]

to get q value from my dataframe, I will run df.iloc[0]['json']  and the result is q and I can call it as a variable  

So my function will be : f(df.iloc[0]['json'], "t1") = "ok" 

[brown-will · Accepted solution]

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.