how do you pull data from json using jsonpath syntax

Solution 1:

Update (original answer below)

First, let's correct your data so that it's actually valid JSON

{
  "totalCount": 10,
  "nextPageKey": null,
  "result": [
    {
      "metricId": "builtin:host.cpu.usage",
      "data": [
        {
          "dimensions": [
            "HOST-1234"
          ],
          "dimensionMap": {
            "dt.entity.host": "HOST-1234"
          },
          "timestamps": [
            1642606200000,
            1642606800000,
            1642607400000,
            1642608000000,
            1642608600000,
            1642609200000,
            1642609800000
          ],
          "values": [
            40.86490249633789,
            38.45122528076172,
            38.24514389038086,
            52.10111999511719,
            51.01322937011719,
            40.43871307373047,
            40.392024993896484
          ]
        }
      ]
    }
  ]
}

Now that I can properly see the structure, my suggestion would be

$.result[*].data[*]['timestamps','values'][*]

Breaking this down:

$.result                                         get `result` property
        [*]                                      get all array elements
           .data                                 get `data` property
                [*]                              get all array elements
                   ['timestamps','values']       get `timestamps` and `values`
                                          [*]    get all array elements

This will give the result set:

[
  1642606200000,
  1642606800000,
  1642607400000,
  1642608000000,
  1642608600000,
  1642609200000,
  1642609800000,
  40.86490249633789,
  38.45122528076172,
  38.24514389038086,
  52.10111999511719,
  51.01322937011719,
  40.43871307373047,
  40.392024993896484
]

The [*] at the end is what returns all of the values themselves.

Since you want them grouped, you could remove the [*] and get this

[
  [
    1642606200000,
    1642606800000,
    1642607400000,
    1642608000000,
    1642608600000,
    1642609200000,
    1642609800000
  ],
  [
    40.86490249633789,
    38.45122528076172,
    38.24514389038086,
    52.10111999511719,
    51.01322937011719,
    40.43871307373047,
    40.392024993896484
  ]
]

Where each sub-array is the value at timestamps and values respectively.

However, that only works when you have a single array element at result and data. If you add more elements, you'll get separate sub-arrays for their contents.

You can try all of this out at my playground, https://json-everything.net/json-path.

(original response, retained for posterity)

$.data['timestamps'] or simply $.data.timestamps is just going to return the array under timestamps. To get the values in the array you need to add [*] to the end.

$.data.timestamps[*]

Many implementations also support multiple keys in the [ ] selector, so you could get both timestamps and values values at the same time with

$.data['timestamps','values'][*]

but be aware that you'll get a single list with all of the values, possibly without any context as to where those values originated. To resolve this, your implementation may support returning the path to each value as well.

how do you pull data from json using jsonpath syntax

Solution 1:

Related

Recent Posts