how do you pull data from json using jsonpath syntax

Question

I have a json file like this and I need to pick: timestamp, values and group it by dimensions.

the json file looks like this:

{
"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
]
},

I need to be able to extract timestamp and values group by dimensions from this json using json path.

I have tried this:

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

not returning any data, any ideas

gregsdennis · Accepted Answer · 2022-01-20 06:37:48Z

3

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.

edited Jan 20, 2022 at 6:37

answered Jan 19, 2022 at 18:50

gregsdennis

8,5206 gold badges43 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Akshay G Over a year ago

Why * at the end?? data is itself an array so the query should be something like $.data[ *]['timestamps'] or $.data[ *]['values'] or $.data[ *]['timestamps','values']

gregsdennis Over a year ago

The [*] returns the values inside the arrays. Basically it deconstructs them. What you posted would give the arrays themselves. JSON Path doesn't do grouping. It just gives values. Getting them as arrays could be used as that grouping, but it may have undesired impacts as well depending on the data, like getting multiple timestamps arrays.

gregsdennis Over a year ago

To be clear, the result in JSON would be nested arrays without the [*]: [[ <timestamp strings> ], [ <value strings> ]]

Akshay G Over a year ago

OP has clearly stated that $.data['timestamps'] $.data['values'] is not returning any data. So I doubt appending * at the end will make any difference. I tested your query and it did not work. If there is any online tester which gives results for your query do share

gregsdennis Over a year ago

Edited answer. The original JSON data was faulty, and OP neglected to add the path up to the data they were searching for. I'm an implementor of JSON Path, and I'm a member of the group working to standardize the syntax.

|

Collectives™ on Stack Overflow

how do you pull data from json using jsonpath syntax

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related