Flatten array of arrays json node

Grafana 11.2.2

I have a table which takes data from an API call and returns values. to make the data fit into the table as I need to create a number of transformations, including Extract Fields and Organise Fields and Join by filed. See screen show for output.

However I am not able to remove the square brackets ‘[’ from my table. I have tried, various transformations including regex and ‘Value mappings’ and ‘Overrides’ but nothing works. I cannot get rid of the square brackets.

For example - My table column has values such as -

[ [ [ “CpuMemoryUsed” ] ], [ [ “CpuMemoryFree” ] ] ]

and I need it to look like

CpuMemoryUsed, CpuMemoryFree

No matter what I try nothing changes, I only get the Heading to update that is it. Im aware that Grafana uses Go regex. Ive tried various simple regex expressions but nothing changes at all. Is there a bug ? Is this something that is simply not possible in Grafana ? Or do I need to go back to my regex, if this is the case, what regex would work here.

Hi,

What datasource are you using? Maybe a better approach would be to use e.g. Infinity Plugin and parse the data with Jsonata?

Please post the original data coming from your API as is raw

Hi, Im using Infinity Plug with a JSON Query using a GET method. This pulls a report from a server as JSON.

Hi, thanks for reply. Please see screen shot. I am using the values in Column ‘Content’ and ‘Name’. For content I am transforming the data using, ‘Extract fields’ using Auto format.

Below is the Header and first row for the data

content id name path properties time-range type
{columns:[Metric name],rows:[[[CurrentUtilization]],[[H323Count]],[[CurrentLoad]],[[Health]],[[ArpUtilization]],[[CallCount]],[[ContactCount]],[[phyCardState]],[[MessagesRejected]],[[LicensedSessions]],[[SessionCount]],[[GatewayCount]],[[powerSupplyState]],[[NatUtilization]],[[Temperature]]]} 0-t62ce62219462574b-d531a45a-e32bab34 ACME-RESOURCES All/Report Library/Masks and Metrics Overview/ACME-RESOURCES {} {end:1741132800,start:1741046400} table
1 Like

the text data you provided does not match the image you posted.

It does, it matches the first row only, not all rows. content column is the JSON data, the other columns, id, name path etc are also in the text posted but I have hidden in the table. After transforming the data in the first screen shot and extracting fields and ordering fields I get the data into the Metrics Columns I want. Don’t worry about the MASK columns that is a different query. I’m just interested in the data in the METRICS columns and removing all the square brackets.

help us help you by providing us this raw data without any transformation so that we can emulate your environment in ours.

:nerd_face:

in original data I see quotation marks, in the second one I do not

Is this better, taken directly from the Api call. Its the first few sections, not all but should be enough.

{
“id”: “0-t62ce62219462574b-b61d21d3”,
“path”: “All/Report Library/Masks and Metrics Overview”,
“name”: “Masks and Metrics Overview”,
“type”: “mixed”,
“properties”: {},
“time-range”: {
“start”: 1741132800,
“end”: 1741219200

much better but not a valid json data. please validate

Please try this…

{
  "id": "0-t62ce62219462574b-b61d21d3",
  "path": "All/Report Library/Masks and Metrics Overview",
  "name": "Masks and Metrics Overview",
  "type": "mixed",
  "properties": {},
  "time-range": {
    "start": 1741132800,
    "end": 1741219200
  },
  "content": {
    "child-reports": [
      {
        "id": "0-t62ce62219462574b-b61d21d3-c476b829",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-ATM-ATT",
        "name": "CISCO-ATM-ATT",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "cAal5VccHCInOctets"
              ]
            ],
            [
              [
                "cAal5VccInDroppedPkts"
              ]
            ],
            [
              [
                "cAal5VccOutDroppedPkts"
              ]
            ],
            [
              [
                "cAal5VccHCOutPkts"
              ]
            ],
            [
              [
                "cAal5VccInPkts"
              ]
            ],
            [
              [
                "cAal5VccOutDroppedOctets"
              ]
            ],
            [
              [
                "cAal5VccHCInPkts"
              ]
            ],
            [
              [
                "cAal5VccInDroppedOctets"
              ]
            ],
            [
              [
                "cAal5VccOutOctets"
              ]
            ],
            [
              [
                "cAal5VccInOctets"
              ]
            ],
            [
              [
                "cAal5VccHCOutOctets"
              ]
            ],
            [
              [
                "cAal5VccOutPkts"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-d08cfb04",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-ENHANCED-MEMORY-POOL",
        "name": "CISCO-ENHANCED-MEMORY-POOL",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "UsedMemoryPool"
              ]
            ],
            [
              [
                "FreeMemoryPool"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-c63ed855",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-IPSLA-HISTORY",
        "name": "CISCO-IPSLA-HISTORY",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "HistoryCollectionSense"
              ]
            ],
            [
              [
                "HistoryCollectionCompletionTime"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-909099ac",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-IPSLA-INVENTORY",
        "name": "CISCO-IPSLA-INVENTORY",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "ApplSupportedRTTTypesValid"
              ]
            ],
            [
              [
                "CtrlAdminVerifyData"
              ]
            ],
            [
              [
                "CtrlAdminThreshold"
              ]
            ],
            [
              [
                "ApplSupportedProtocolsValid"
              ]
            ],
            [
              [
                "CtrlOperRttLife"
              ]
            ],
            [
              [
                "CtrlAdminTimeout"
              ]
            ],
            [
              [
                "CtrlAdminStatus"
              ]
            ],
            [
              [
                "CtrlOperOctetsInUse"
              ]
            ],
            [
              [
                "CtrlAdminFrequency"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-fae3a9c4",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-IPSLA-JITTER-LATEST",
        "name": "CISCO-IPSLA-JITTER-LATEST",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "LatestJitterOperRTTMaxOfPositivesSD"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMaxOfPositivesDS"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMinOfNegativesDS"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMinOfNegativesSD"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMax"
              ]
            ],
            [
              [
                "LatestJitterOperPacketMIA"
              ]
            ],
            [
              [
                "LatestJitterOperOWSum2SD"
              ]
            ],
            [
              [
                "LatestJitterOperSense"
              ]
            ],
            [
              [
                "LatestJitterOperOWSumSD"
              ]
            ],
            [
              [
                "LatestJitterOperOWSum2DS"
              ]
            ],
            [
              [
                "LatestJitterOperOWSumDS"
              ]
            ],
            [
              [
                "LatestJitterOperPacketLossSD"
              ]
            ],
            [
              [
                "LatestJitterOperPacketLossDS"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMinOfPositivesDS"
              ]
            ],
            [
              [
                "LatestJitterOperPacketOutOfSequence"
              ]
            ],
            [
              [
                "LatestJitterOperNumOfRTT"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMin"
              ]
            ],
            [
              [
                "LatestJitterOperMOS"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMinOfPositivesSD"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMaxOfNegativesSD"
              ]
            ],
            [
              [
                "LatestJitterOperRTTMaxOfNegativesDS"
              ]
            ],
            [
              [
                "LatestJitterOperRTTSum"
              ]
            ],
            [
              [
                "LatestJitterOperICPIF"
              ]
            ],
            [
              [
                "LatestJitterOperPacketLateArrival"
              ]
            ],
            [
              [
                "LatestJitterOperNumOfOW"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-5a126a3a",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-IPSLA-JITTER-STATSTABLE",
        "name": "CISCO-IPSLA-JITTER-STATSTABLE",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "JitterStatsOWSumDS"
              ]
            ],
            [
              [
                "JitterStatsNumOfNegativesDS"
              ]
            ],
            [
              [
                "JitterStatsSumOfNegativesDS"
              ]
            ],
            [
              [
                "EchoAdminCodecNumPackets"
              ]
            ],
            [
              [
                "EchoAdminCodecInterval"
              ]
            ],
            [
              [
                "EchoAdminInterval"
              ]
            ],
            [
              [
                "JitterStatsPacketMIA"
              ]
            ],
            [
              [
                "JitterStatsMaxOfNegativesDS"
              ]
            ],
            [
              [
                "JitterStatsNumOfPositivesSD"
              ]
            ],
            [
              [
                "JitterStatsOWMaxSD"
              ]
            ],
            [
              [
                "JitterStatsPacketLateArrival"
              ]
            ],
            [
              [
                "JitterStatsMinOfMOS"
              ]
            ],
            [
              [
                "JitterStatsBusies"
              ]
            ],
            [
              [
                "JitterStatsMaxOfPositivesDS"
              ]
            ],
            [
              [
                "JitterStatsPacketLossDS"
              ]
            ],
            [
              [
                "EchoAdminNumPackets"
              ]
            ],
            [
              [
                "JitterStatsOWMinDS"
              ]
            ],
            [
              [
                "SlaAveragePacketLoss"
              ]
            ],
            [
              [
                "SlaAverageJitter"
              ]
            ],
            [
              [
                "JitterStatsSumOfPositivesDS"
              ]
            ],
            [
              [
                "SlaAverageLatency"
              ]
            ],
            [
              [
                "JitterStatsNumOfRTT"
              ]
            ],
            [
              [
                "JitterStatsOWSumSD"
              ]
            ],
            [
              [
                "JitterStatsOverThresholds"
              ]
            ],
            [
              [
                "JitterStatsPacketOutOfSequence"
              ]
            ],
            [
              [
                "JitterStatsSumOfNegativesSD"
              ]
            ],
            [
              [
                "JitterStatsNumOfNegativesSD"
              ]
            ],
            [
              [
                "SlaPacketsLossA1"
              ]
            ],
            [
              [
                "JitterStatsMinOfCPIF"
              ]
            ],
            [
              [
                "SlaPacketsLossA2"
              ]
            ],
            [
              [
                "EchoAdminPktDataRequestSize"
              ]
            ],
            [
              [
                "JitterStatsMaxOfNegativesSD"
              ]
            ],
            [
              [
                "JitterStatsOWMaxDS"
              ]
            ],
            [
              [
                "JitterStatsMaxOfMOS"
              ]
            ],
            [
              [
                "JitterStatsNumOfPositivesDS"
              ]
            ],
            [
              [
                "SlaLatencyTotalPacketsA"
              ]
            ],
            [
              [
                "JitterStatsError"
              ]
            ],
            [
              [
                "EchoAdminCodecPayload"
              ]
            ],
            [
              [
                "JitterStatsOWMinSD"
              ]
            ],
            [
              [
                "JitterStatsPacketLossSD"
              ]
            ],
            [
              [
                "JitterStatsRTTMin"
              ]
            ],
            [
              [
                "SlaNumberPacketLoss"
              ]
            ],
            [
              [
                "JitterStatsRTTSum"
              ]
            ],
            [
              [
                "JitterStatsMaxOfPositivesSD"
              ]
            ],
            [
              [
                "JitterStatsSumOfPositivesSD"
              ]
            ],
            [
              [
                "JitterStatsCompletions"
              ]
            ],
            [
              [
                "JitterStatsRTTMax"
              ]
            ],
            [
              [
                "SlaLossTotalPacketsA"
              ]
            ],
            [
              [
                "JitterStatsMaxOfCPIF"
              ]
            ],
            [
              [
                "JitterStatsNumOfOW"
              ]
            ],
            [
              [
                "SlaJitterTotalPacketsA"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-56520f94",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-IPSLA-STATSTABLE",
        "name": "CISCO-IPSLA-STATSTABLE",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "StatsCollectTimeouts"
              ]
            ],
            [
              [
                "StatsCollectSequenceErrors"
              ]
            ],
            [
              [
                "SlaPacketsLossA1"
              ]
            ],
            [
              [
                "SlaPacketsLossA2"
              ]
            ],
            [
              [
                "StatsCaptureCompletionTimeMax"
              ]
            ],
            [
              [
                "StatsCaptureSumCompletionTime"
              ]
            ],
            [
              [
                "SlaLatencyTotalPacketsA"
              ]
            ],
            [
              [
                "StatsCollectBusies"
              ]
            ],
            [
              [
                "SlaLossMonitorValueB"
              ]
            ],
            [
              [
                "StatsCollectDrops"
              ]
            ],
            [
              [
                "StatsCollectVerifyErrors"
              ]
            ],
            [
              [
                "SlaNumberPacketLoss"
              ]
            ],
            [
              [
                "SlaAveragePacketLoss"
              ]
            ],
            [
              [
                "StatsCollectNoConnections"
              ]
            ],
            [
              [
                "StatsCaptureOverThresholds"
              ]
            ],
            [
              [
                "SlaLossTotalPacketsC"
              ]
            ],
            [
              [
                "StatsCaptureCompletionTimeMin"
              ]
            ],
            [
              [
                "SlaLossTotalPacketsA"
              ]
            ],
            [
              [
                "SlaLossTotalPacketsB"
              ]
            ],
            [
              [
                "SlaAverageLatency"
              ]
            ],
            [
              [
                "StatsCollectNumDisconnects"
              ]
            ],
            [
              [
                "SlaLatencyMonitorValueC"
              ]
            ],
            [
              [
                "SlaLatencyMonitorValueB"
              ]
            ],
            [
              [
                "SlaLossTotalPacketsA1"
              ]
            ],
            [
              [
                "StatsCaptureCompletions"
              ]
            ],
            [
              [
                "SlaLatencyAdjustedValueB"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-7c1099c5",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-LISTOF-IF-ATT",
        "name": "CISCO-LISTOF-IF-ATT",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "locIfCollisions"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-c575d28c",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-LWAPP-AP-ATT",
        "name": "CISCO-LWAPP-AP-ATT",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "cLApLinkLatencyStatsCurrent"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-74fb1f95",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-MEMORY",
        "name": "CISCO-MEMORY",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "FreeMemory"
              ]
            ],
            [
              [
                "LargestFreeBuffer"
              ]
            ],
            [
              [
                "TotalMemory"
              ]
            ],
            [
              [
                "UsedMemory"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-c362c6b6",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-MEMORY-BUFFER-POOL-ATT",
        "name": "CISCO-MEMORY-BUFFER-POOL-ATT",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "cempMemBufferFailures"
              ]
            ],
            [
              [
                "cempMemBufferNoStorage"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-34217367",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-MEMORY-OLD",
        "name": "CISCO-MEMORY-OLD",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "bufferHgTotal"
              ]
            ],
            [
              [
                "bufferLgMiss"
              ]
            ],
            [
              [
                "bufferMdFree"
              ]
            ],
            [
              [
                "avgBusy5"
              ]
            ],
            [
              [
                "bufferSmFree"
              ]
            ],
            [
              [
                "bufferSmHit"
              ]
            ],
            [
              [
                "bufferElMiss"
              ]
            ],
            [
              [
                "bufferNoMem"
              ]
            ],
            [
              [
                "bufferLgSize"
              ]
            ],
            [
              [
                "bufferSmSize"
              ]
            ],
            [
              [
                "bufferBgSize"
              ]
            ],
            [
              [
                "bufferSmTotal"
              ]
            ],
            [
              [
                "bufferBgTotal"
              ]
            ],
            [
              [
                "bufferHgMiss"
              ]
            ],
            [
              [
                "bufferBgHit"
              ]
            ],
            [
              [
                "bufferFail"
              ]
            ],
            [
              [
                "bufferBgMiss"
              ]
            ],
            [
              [
                "bufferHgSize"
              ]
            ],
            [
              [
                "bufferLgTotal"
              ]
            ],
            [
              [
                "bufferMdHit"
              ]
            ],
            [
              [
                "bufferElHit"
              ]
            ],
            [
              [
                "bufferHgHit"
              ]
            ],
            [
              [
                "bufferLgFree"
              ]
            ],
            [
              [
                "bufferMdSize"
              ]
            ],
            [
              [
                "bufferHgFree"
              ]
            ],
            [
              [
                "bufferLgHit"
              ]
            ],
            [
              [
                "bufferSmMiss"
              ]
            ],
            [
              [
                "bufferMdTotal"
              ]
            ],
            [
              [
                "bufferBgFree"
              ]
            ],
            [
              [
                "bufferMdMiss"
              ]
            ]
          ]
        }
      },
      {
        "id": "0-t62ce62219462574b-b61d21d3-c52ef1bc",
        "path": "All/Report Library/Masks and Metrics Overview/CISCO-QOS-CM-QUEUEING-POLICE-SET",
        "name": "CISCO-QOS-CM-QUEUEING-POLICE-SET",
        "type": "table",
        "properties": {},
        "time-range": {
          "start": 1741132800,
          "end": 1741219200
        },
        "content": {
          "columns": [
            "Metric name"
          ],
          "rows": [
            [
              [
                "cbQosSetL2CosPkt64"
              ]
            ],
            [
              [
                "cbQosSetDiscardClassPkt64"
              ]
            ],
            [
              [
                "cbQosSetQosGroupPkt64"
              ]
            ],
            [
              [
                "cbQosCMPrePolicyPkt"
              ]
            ],
            [
              [
                "cbQosSetDscpTunnelPkt64"
              ]
            ],
            [
              [
                "cbQosCMPrePolicyByte64"
              ]
            ],
            [
              [
                "cbQosCMDropByte64"
              ]
            ],
            [
              [
                "cbQosPoliceConformedByte64"
              ]
            ],
            [
              [
                "cbQosCMPostPolicyByte64"
              ]
            ],
            [
              [
                "cbQosQueueingMaxQDepth"
              ]
            ],
            [
              [
                "cbQosPoliceConformedPkt64"
              ]
            ],
            [
              [
                "cbQosQueueingDiscardByte64"
              ]
            ],
            [
              [
                "cbQosSetAtmClpPkt64"
              ]
            ],
            [
              [
                "cbQosSetFrDePkt64"
              ]
            ],
            [
              [
                "cbQosPoliceViolatedPkt64"
              ]
            ],
            [
              [
                "cbQosQueueingCurrentQDepth"
              ]
            ],
            [
              [
                "cbQosSetPrecedenceTunnelPkt64"
              ]
            ],
            [
              [
                "cbQosPoliceExceededByte64"
              ]
            ],
            [
              [
                "cbQosPoliceExceededPkt64"
              ]
            ],
            [
              [
                "cbQosSetMplsExpImpositionPkt64"
              ]
            ],
            [
              [
                "cbQosSetSrpPriorityPkt64"
              ]
            ],
            [
              [
                "cbQosSetDscpPkt64"
              ]
            ],
            [
              [
                "cbQosSetPrecedencePkt64"
              ]
            ],
            [
              [
                "cbQosCMPrePolicyPkt64"
              ]
            ],
            [
              [
                "cbQosCMNoBufDropPkt64"
              ]
            ],
            [
              [
                "cbQosQueueingDiscardPkt64"
              ]
            ],
            [
              [
                "cbQosSetFrFecnBecnPkt64"
              ]
            ],
            [
              [
                "cbQosPoliceViolatedByte64"
              ]
            ],
            [
              [
                "cbQosSetMplsExpTopMostPkt64"
              ]
            ],
            [
              [
                "cbQosCMDropPkt64"
              ]
            ]
          ]
        }
      }
      ]
  }
}

see this and run with it

https://try.jsonata.org/Z17Zt_C-W

:fish: :fishing_pole:

Hi,

I can see this works in jsonata.org which is very good and gives me exactly what I need but It doesn’t work in Grafana. Assuming that I use this in parsing options.

Whhere do I place the jsonata in Grafana to get this to work.

the docu should help you sort out how to do jsonata

also search throughout this forum on how to impement jsonata