Bigquery to Json

[ThorstenO]

Hello Community,
I am a little lost:-) I have a result from a bigquery Table. See screenshot.

And i want to format it to json so i can give it back to a webhook.
The output shall look like this format

{
“keywords”: [
{“keyword”: “digital marketing”, “search_volume”: 50000},
{“keyword”: “digital marketing strategies”, “search_volume”: 10000},
{“keyword”: “digital marketing tools”, “search_volume”: 8000},
// more keywords…
]
}

What do i need? the iterator? The text aggregator?

[samliew]

Welcome to the Make community!

Please provide the output bundles of the modules by running the scenario, then click the white speech bubble on the top-right of each module, click the download icon on the top-right, copy and save the bundle contents in your text editor as a bundle.json file, and upload it here into this discussion thread.

Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

This will allow others to better assist you. Thanks!

[ThorstenO]

@samliew

bundle.json

bundle.json (3.4 KB)

Of course here is the output bundle of the biquery export

[samliew]

Hi @ThorstenO,

You’ll just need three modules. This costs a total of three operations.

1. Extract key-value and convert to collection, then to array

Replace 58.rows with your BigQuery module’s “Rows”

2. Convert the previous module’s array to JSON

3. Rename keys from previous module’s JSON string, then assign array to “keywords” property of a new object.

You can copy-paste this into your field, but change 62.json to whatever your previous module’s output is:

{
  "keywords": {{replace(replace(62.json; """key"""; """keyword"""); """value"""; """search_volume""")}}
}

Sample Output:

Hope this helps!

[Lauren_Moineau]

Hi @ThorstenO I have an alternative solution to @samliew 's, using 3 operations as well.

Pass your JSON string into a ParseJSON module

Iterate your rows

Add an Aggregate JSON module creating a new data structure

Output

Hope that helps

[samliew]

@Lauren_Moineau good effort, but you need to also consider that the output of the BigQuery module is not JSON, so you cannot begin with “Parse JSON”.

Also I don’t like the additional complexity of having to create a data structure just for this.

[Lauren_Moineau]

Sure @samliew , I’d swap the Parse JSON to a Transform JSON module passing in rows then.
Fair enough about data structure. I find it simpler but to each their own

[ThorstenO]

thank you so Much Samliew. Thats works. And also LaurenM.
Now i know the workflow…
Mybe of your interest it was for a Custum Chatgpt and this works with Make also.