String search with jsonpath

Question

I have huge JSON file with more than 20000 objects in it. I would like to query a string in this json file. However, using jsonpath, I could only find exact matches?

Let's say my file is like:

{ "store": {
    "book": [ 
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      { "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      { "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}

What I would like to do is:

jsonPath(data, "$..book[?(@.title like 'ord')]")

and get books having "ord" in their title.

{ "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
{ "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
}

is it possible?

score 8 · Accepted Answer · edited May 23 '17 at 12:09

Update:

According to the unit tests and this answer, you can embed a regex literal in the expression and it will be evaled. The following is the example from the unit test.

$.menu.items[?(@ && @.label && /SVG/.test(@.label))].id

So, for your example, it would be:

$.store.book[ ?( @ && @.title && /ord/.test(@.title) ) ].isbn

I've used jsonPath for some pretty large json files (2-3MB) and they were the cause of our application slow down. If you're concerned at all about performance, I'd suggest creating a supplementary index for each field the you'd like to search on. Doing this also allows you to weight the relevance value of each field, which is pretty important for getting good search results.

Or better yet, if you anticipate that your application may grow, you may want to entertain a server-side search engine (http://sphinxsearch.com/ or the SAAS version http://indexden.com/).

As for you specific problem, the like operator is supposed to be supported. However, I've been able to achieve pretty good performance with just an array of strings (eg. the titles in your data structure) and using plain javascript regex (http://www.w3schools.com/jsref/jsref_obj_regexp.asp).

thanks for your reply. But I could not find a "like" operator in jsonpath? Dis you mean it is not supported? — huzeyfe, Aug 07 '13 at 16:00
I was wrong. Apparently the LIKE operator wasn't supported. I did, however, find the usage of a regex literal in the jsonPath unit tests. I've updated the answer to link to that usage. — Homer6, Aug 07 '13 at 18:41
Please be aware that this is using eval to process this solution. So, if you're using user-submitted data for search, which would likely be the case, you're going to have to worry about properly filtering and escaping the input so that malicious users couldn't execute code on other users behalf (or if this jsonPath is running on the server-side via node.js or something). — Homer6, Aug 07 '13 at 18:46

String search with jsonpath

1 Answers1