Parse JSON inside JSON attribute using JSONPath

Question

I have a JSON list where one of the attributes of each element happens to be a JSON itself. It comes from a poor design upfront, but here it is.

I want to query the distinct attributes inside the JSON string contained in the elements.

Here is an example, just one item. I hand-wrote the code, but believe me that is valid JSON in production by the way it's generated

[{
  "extraData": "{\"foo\":\"bar\"}"
}]

I would like to query for something like $.*.extraData.foo, but obviously such syntax does not work. I am using IntelliJ IDEA Jsonpath evaluator.

The syntax should be something like parse($.*.extraData).*.foo

This article suggests me that no such operator is available to parse a JSON inside a JSON

It has to be JSONPath only for data analysis purposes. In Java, I use Jackson to parse the extraData object as a JsonNode, but my goal is to explore the large data set, and perhaps obtain some distinct values I want to use for enumeration purposes.

Answer 1

To JSON Path, the embedded JSON is just a string. The best you could do with a single operation is use a RegEx in the expression selector, but getting RegEx to identify JSON is really tricky, and that's if your implementation even supports RegEx.

I think the best option would be to use two paths:

1. The first path gets the embedded JSON. $.*.extraData
2. Parse that value
3. The second part gets the data you need. $.foo

This requires some extra code, but I think it's your only realistic option.