This library is a tentative mapping of emerging JSONPath standard to SQL/JSONPath. Currently, the library follows closely the tests reported in jsonpath-compliance-test-suite and it verifies the mapping using PostgresSQL as a db service.
The JSONPath to SQL/JSONPath is a small TypeScript library that can be used in any NodeJS or Browser application.
npm i @vaimee/jsonpath-to-sqljsonpath
TBD
The library exports two main component: the parser object, and the transform function. When you just one to
translate a JSONPath query you can use the transform method:
import { transform } from '@vaimee/jsonpath-to-sqljsonpath'
const queries: string[] = transform("$.hello.world[?@.test > 3]")The result of the transformation process is a list of SQL/JSONPath queries that should be issued to the database. Here is an example of how to use the queries returned to interogate a DataBase complaint with the standard:
import { transform } from '@vaimee/jsonpath-to-sqljsonpath'
const queries: string[] = transform("$.hello.world[?@.test > 3]")
const queryResult = await knex.unionAll(
queries.map((query) => {
return knex.table(table).select(knex.raw('jsonb_path_query(document, ?) as result', query)).where({ id });
}));
const JSONPathResult = queryResult.map((result) => {
return result.result;
});The variable JSONPathResult will contain the JSONPath complaint query result.
The library uses a best effort approach, but we found that some of JSONPath feature cannot be translated to SQL/JSONPath. Currently the known limitations are:
- Negative steps are not supported (
"$.array[1:5:-1]). SQL/JSONPath is strict about the ordering of the elements selected from an array and those are always in a ascending order. Therefore, we cannot simulate a negative step that would reverse the natural order of the returned elements.
Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.
If you have a suggestion that would make this better, please fork the repo and create a pull request. You can also simply open an issue with the tag "enhancement". Don't forget to give the project a star! Thanks again!
- Fork the Project
- Create your Feature Branch (git checkout -b feature/AmazingFeature)
- Commit your Changes (git commit -m 'Add some AmazingFeature')
- Push to the Branch (git push origin feature/AmazingFeature)
- Open a Pull Request
Cristiano Aguzzi - cristiano.aguzzi@vaimee.com
Lorenzo Gigli - lorenzo.gigli@vaimee.com
This library is part of Zion a Thing Description Directory developed inside the Ontochain European programme.