cancel
Showing results for 
Search instead for 
Did you mean: 

Response Content to Collection

nicolailykke
Level 3

Hi all,

When receiving the response from a BigQuery.jobs query using Skills, I would like to have the data structured in a collection. However, I'm having trouble achieving this and always end up with rows that require looping to unnest.

nicolailykke_1-1724308710851.png

The entire response will consist of upwards of 100,000 records, so having to loop each row to unnest is not an ideal solution in this case.

I've tried using the updated Function for Utility - JSON - 10.0.1 on DX and also attempted the solution provided by Eric: https://community.blueprism.com/t5/Product-Forum/Web-Services-Json-Path/td-p/73574
However, it doesn't seem to work in our case. Additionally, I've tried the custom Business Object from Symphony Ventures: Ventures: https://digitalexchange.blueprism.com/dx/entry/7888/solution/json-manipulation-c

The response looks like this (with sensitive information removed):
{
"kind": "bigquery#queryResponse",
"schema": {
"fields": [
{
"name": "json",
"type": "STRING",
"mode": "NULLABLE"
}
]
},
"jobReference": {
"projectId": "production",
"jobId": "XXXXXXX",
"location": "europe"
},
"totalRows": "41158",
"pageToken": "123456789xxx======",
"rows": [
{
"f": [
{
"v": "{\"id\":\"A1\",\"codes\":\"123,234\"}"
}
]
},
{
"f": [
{
"v": "{\"id\":\"A2\",\"codes\":\"123\"}"
}
]
}
],
"totalBytesProcessed": "403783338",
"jobComplete": true,
"cacheHit": false,
"queryId": "job_xxx",
"jobCreationReason": {
"code": "REQUESTED"
}
}

I would like to end up with the data structured in a collection, similar to this:

nicolailykke_0-1724308650587.png

Thank you for any help on this matter.

Best regards,
Nicolai

 

 

 

 

 

11 REPLIES 11

Hi Nandhakumar,

Thank you very much 🙂 I got the time reduced to a few seconds with the regex solution, så it's perfect 🙂

Have a great day!

Best regards,
Nicolai

Nandhakumar
Level 6

Hi @nicolailykke 

I'm glad you are able to solve the problem 🙂

Please mark it as Correct Answer.

Cheers,

Nandha