How to combine 2 query into 1

Grafana
,
1

I am using Infinity Data Source. I wrote 2 query within one panel, first query return the result of my table and second query return the headers/column name for the first query. Is there any transfomration or ways allow me to combine these 2 into one

2

try the concatenate transformation

3

I have tried it. But it won’t return what I want. For more context what I am doing is connecting to my databricks catalog via API, and return everything I want thru there. But the API return format is json array where I have to process it.

4

please post a table view of your data

5

hi, i am trying another approach of using UQL to parse my data. Below is example of my data

{
“manifest”: {
“chunks”: [
{
“chunk_index”: 0,
“row_count”: 2,
“row_offset”: 0
}
],
“format”: “JSON_ARRAY”,
“schema”: {
“column_count”: 3,
“columns”: [
{
“name”: “l_orderkey”,
“position”: 0,
“type_name”: “LONG”,
“type_text”: “BIGINT”
},
{
“name”: “l_extendedprice”,
“position”: 1,
“type_name”: “DECIMAL”,
“type_precision”: 18,
“type_scale”: 2,
“type_text”: “DECIMAL(18,2)”
},
{
“name”: “l_shipdate”,
“position”: 2,
“type_name”: “DATE”,
“type_text”: “DATE”
}
]
},
“total_chunk_count”: 1,
“total_row_count”: 2,
“truncated”: false
},
“result”: {
“chunk_index”: 0,
“data_array”: [
[
“2”,
“71433.16”,
“1997-01-28”
],
[
“7”,
“86152.02”,
“1996-01-15”
]
],
“row_count”: 2,
“row_offset”: 0
},
“statement_id”: “00000000-0000-0000-0000-000000000000”,
“status”: {
“state”: “SUCCEEDED”
}
}

I can achive what I want by using 2 seperate query

  1. parse-json
    | scope “manifest.schema.columns”
  2. parse-json
    | scope “result.data_array”
    However I couldn’t combine both of them into one. The 1st query return the headers and second query returns the query.
6

Maybe a JSONata array join, like this?

7

As @grant2 mentioned, this can be achieved with JSONata.

$map($.result.data_array, function($row){
    $merge($map($$.manifest.schema.columns,function($key, $key_index){
        return { 
            $key.name : $row[$key_index]
        }
    }))
})

image
image3580Ă—1662 283 KB

8

hi, this solve my issue! however it won’t return column that has all the value as null, howveer to keep the complete record i would love it to be return. Is there any solution to this?

9

@tanyoongkiat - You can learn and adjust the query by yourself according to your needs. :slight_smile:

1 Like
10

Hi, I realise its not the query itself, I have fixed the query and showing it on online jsonata it show me the complete json version of my data, for all columns. However it is not the same in Grafana, even when I try to use the inline data. Do u have any idea about this?

11

can you give me example of the data and query to reproduce this myself ?

12

Data:
{
“statement_id”: “01eee28d-45e8-17ea-83aa-75ac2c73e6e2”,
“status”: {
“state”: “SUCCEEDED”
},
“manifest”: {
“format”: “JSON_ARRAY”,
“schema”: {
“column_count”: 5,
“columns”: [
{
“name”: “role_id”,
“type_text”: “INT”,
“type_name”: “INT”,
“position”: 0
},
{
“name”: “namespace_id”,
“type_text”: “INT”,
“type_name”: “INT”,
“position”: 1
},
{
“name”: “name”,
“type_text”: “STRING”,
“type_name”: “STRING”,
“position”: 2
},
{
“name”: “parent_id”,
“type_text”: “INT”,
“type_name”: “INT”,
“position”: 3
},
{
“name”: “sys_type”,
“type_text”: “STRING”,
“type_name”: “STRING”,
“position”: 4
}
]
},
“total_chunk_count”: 1,
“chunks”: [
{
“chunk_index”: 0,
“row_offset”: 0,
“row_count”: 5
}
],
“total_row_count”: 5,
“truncated”: false
},
“result”: {
“chunk_index”: 0,
“row_offset”: 0,
“row_count”: 5,
“data_array”: [
[
“1”,
“1”,
“XVD”,
null,
“A”
],
[
“2”,
“1”,
“RTG”,
null,
“U”
],
[
“3”,
“1”,
“YYZ”,
null,
“U”
],
[
“4”,
“1”,
“XXZ”,
null,
“U”
],
[
“5”,
“1”,
“XXX”,
null,
“U”
]
]
}
}

query: $map($.result.data_array, function($row){
$merge($map($$.manifest.schema.columns,function($key, $key_index){
return {
$key.name : $row[$key_index]
}
}))
})

13

If you are talking about missing all null columns, you need to handle this in your JSONata query.

something like…

$map($.result.data_array, function($row){
    $merge($map($$.manifest.schema.columns,function($key, $key_index){
       return {
          $key.name : $type($row[$key_index]) = 'null'  ? "" : $row[$key_index]
       }
    }))
})
14

I can see this is changing the null to “” . But I am having error within the plugin itself, it will load for 30seconds or more and just ran into error, its that soemting to do with the complexity of the data, or the plugin does not allow such complex jsonata formatting?