mORMot Open Source

AntonE

Member

Registered: 2012-02-03

Posts: 74

JsonPath like queries from TDocVariant(Data)?

Hi, I am more impressed by mORMot1&2 everytime I use it. Respect!

TDocVariantData is very powerful. I want to use it as an in-memory 'json object List' for classes (TOrm or others) that must constantly be marshalled but seldom changed.
E.g. a (mostly) lookup table of TOrm classes loaded into a TDocVariantData-Array, perhaps sorted by TID for FastFind.

However, something like https://jsonpath.com/ for TDocVariant(Data) can make a for a very powerful and versatile tool, especially in harmony with copyOnReference/ByValue.
E.g. server-side cache of data objects, sorted by some keyin a TDocVariantData array that is already quick to serialize to Json, but now to add ability to do on-the-fly basic 'queriesq to get subset of objects/fields (like sqlWhare/Args) in nested structures.

Or any utility/tool that can help me not write every json-query out by and as code will help,
thanks!

Anton

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,686

Website

Re: JsonPath like queries from TDocVariant(Data)?

You are right.

There is basic support of path searching - but only for a hierarchy of objects, using TDocVariant.GetValueByPath/SetValueByPath/DeleteByPath...
I have always found the JSON PATH syntax a bit weird.
But it is part of the Delphi RTL: https://docwiki.embarcadero.com/Librari … PathParser
SQlite3 has another syntax for its JSON functions (which are embedded in mORMot): https://sqlite.org/json1.html

Is there any alternative to JSON PATH?
Or should we add direct OData support?

The best implementation I found was in the FPC lgenerics library:
https://wiki.freepascal.org/LGenerics#S … r_JSONPath
Pretty complete, with advanced filtering, and trying to follow the RFC draft specifications.

My guess is that filters like ?(@.Name == 'Acme Co') make sense in the query, even if they are not supported everywhere.
But we would rather use parameters with named place holders like ?(@.Name == $) for safety, to avoid values injection.
We may also be able to pre-compile the JSON path into a reusable/cachable syntax tree, then inject the values at runtime.

Perhaps a dual iterator/variant output may be possible... The iterator with a SAX approach with next/previous calls, and the variant output creating a new TDocVariant with all the matching data.
The JSON PATH engine could be made abstract enough to be reused outside of TDocVariantData content...

AntonE

Member

Registered: 2012-02-03

Posts: 74

Re: JsonPath like queries from TDocVariant(Data)?

Thank you for the reply.
I somehow missed it; But now come back to this project and stumbled upon your reply searching for something else:)

I saw the RTL support, but since I use synopse json<>TDocVariantData almost exclusively, I hoped to avoid 'heavy' RTL classes.
I suspect the SQLite/JSON support will be good (i.t.o. fast/memory efficient) for large datasets so I'll try that in future and I'll probably have a look at the FPC implementation at some stage.

A way to query similar to JsonPath, (even just in code) will be very useful, especially if you can aggregate and group as well, but for now I just write queries by hand.

Thank you
Keep up the great work