JSONPath query on MongoDB?

Question

We have User data stored into MongoDB. The client gives a JSONPath query to ask for filtered set of users.

Eg:

$.users[@.salary > 10000]

is a JSONPath query given to retrieve users with salary greater than 10000.

Data:

   "users": [ {
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "salary"   : 5000, 
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        },
        ........
      ]
    }

Whereas, a MongoQuery for the same would be:

db.inventory.find( { salary: { $gt: 10000 } })

We have our Application code in Java which connects to MongoDB.

Is there any way I can run this JSONPath query to MongoDB directly ??

Using Mongo query would mean translating the JSONPath query to MongoQuery which would be cumbersome translating in code. A very brute force method would be to get all Users first on AS (application) and then convert List<Document> to Json string and then run the JSONPath query.

Any help would be appreciated.

I would suggest you go for the translation option. You can rewrite the following code in Java and that's it - hopefully. ;) https://github.com/gabrielelana/mongodb-shell-extensions/blob/master/src/jsonpath.js — dnickless, Jul 09 '18 at 21:04

score 2 · Accepted Answer · answered Jul 09 '18 at 21:17

This cannot be done out of the box at this stage - see this open request: https://jira.mongodb.org/browse/SERVER-736

I would personally recommend you to go with some kind of translation approach - it's the only viable option I see apart from the brute-force client side evaluation which simply doesn't scale (but would probably be very ok for small data sets). Plus, the code you need to translate your queries can be nicely unit tested so it should be a reasonably safe bet.

Here are some ideas for where to source/base the required translation code from/on.

Regex and parsing based (specifically for MongoDB): https://github.com/gabrielelana/mongodb-shell-extensions/blob/master/src/jsonpath.js

Alternatively, this could perhaps be used to parse JSONPath strings into an object model which you could then translate into MongoDB queries: https://github.com/json-path/JsonPath

JSONPath query on MongoDB?

1 Answers1