I need help again...

factor200x · 2017-10-23 14:43:48

Hello everyone,

I have the following understanding problem.

In my database field I store a JSON array:

Field names:

Filter, Type: RawUtf8
TestData, Type: Variant;

Field content:

Filter: Filter = Test
TestData: {"Contents":[{ID: 1, Data:"Test"}, {ID: 2, Data:"Help"}]}}

The line with the required data set is filtered via "Filter = Test" and gets the JSON value.

I hope by then I have been able to explain this clearly. :-)

Well, my problem:

How can I filter within the JSON array after ID = 2 filters?

In the documentary. I read about the function JsonGet (). But unfortunately I can't find an example of how to understand it.

Can someone give me a little example?

Many thanks in advance
Olaf

ab · 2017-10-23 16:35:32

Currently, JsonGet() could only retrieve one item in the array list by index, not search within the array...

So you will probably have to retrieve the values in the TSQLRecord, and search by hand using TDocVariant.

edwinsn · 2017-10-24 12:14:22

use SQLITE's JSON1 syntax.

ab · 2017-10-24 14:14:59

Yes, I forgot about it, IIRC we compiled SynSQLite3Static.pas including JSON1.
See https://sqlite.org/json1.html

But it will work with standard JSON, not our "extended" JSON standard.

factor200x · 2017-10-24 16:47:07

Oh thank you very much for the helpful hint! That helps me a lot!

esmondb · 2017-10-24 19:21:24

If you use JSON1 remember to call TSQLDataBase.CacheFlush() after edits to keep the ORM cache in sync. I got caught out by that!

edwinsn · 2017-10-25 01:46:14

@esmondb, that's a good hint!

mORMot Open Source