Hi Anil, Thanks a lot for your guidance. This has really helped me to
proceed.
My intended queries are working and returning projected data (as a struct).
Just wondering is there any api that does a struct --> JSON string
conversation..
-Ankit.
On Wed, Nov 25, 2020, 12:21 AM Anilkumar Gingade
wrote:
> Ankit,
>
> Here is how to query col2.
> "SELECT d.col2 FROM /JsonRegion v, v.data d, d.col2 c where c.k21 =
> '22'";
>
> You can find example on how to query nested collections:
>
> https://geode.apache.org/docs/guide/18/getting_started/querying_quick_reference.html
>
> When you want to select a nested collection and inspect its value; you
> need to create iterator in the from clause (E.g. d.col2 in the above query)
>
> You can find other ways to query arrays in the above sample.
>
> -Anil.
>
>
>
> On 11/23/20, 10:02 PM, "ankit Soni" wrote:
>
> Hi Anil,
>
> Thanks a lot for your reply. This really helps to proceed. The query
> shared
> by you worked but I need a slight variation of it, i.e where clause
> contains col2 (data.col2.k21 = '22') which is array unlike col1
> (object).
>
> FYI: value is stored in cache.
> PDX[28847624, __GEMFIRE_JSON]{
> data=[PDX[28847624, __GEMFIRE_JSON] {
> col1=PDX[28626794, __GEMFIRE_JSON] {k11=aaa, k12=true, k13=,
> k14=2020-12-31T00..}
> Col2=[PDX[25385544, __GEMFIRE_JSON]{k21=, k22=true}]}]}
> Based on OQL querying doc shared, tried few ways but no luck on
> querying
> based on Col2.
>
> It will be really helpful if you share updated query.
>
> Thanks
> Ankit.
>
> On Tue, Nov 24, 2020, 2:42 AM Anilkumar Gingade
> wrote:
>
> > Ankit,
> >
> > Here is how you can query your JSON object.
> >
> > String queryStr = "SELECT d.col1 FROM /JsonRegion v, v.data d where
> > d.col1.k11 = 'aaa'";
> >
> > As replied earlier; the data is stored as PdxInstance type in the
> cache.
> > In the PdxInstance, the data is stored as top level or nested
> collection of
> > objects/values based on input JSON object structure.
> > The query engine queries on the PdxInstance type and returns the
> value.
> >
> > To see, how the PdxInstance data looks like in the cache, you can
> print
> > the returned value from querying the region values:
> > E.g.:
> > String queryStr = "SELECT v FROM /JsonRegion v";
> > SelectResults results = (SelectResults)
> > QueryService().newQuery(queryStr).execute();
> > Object[] value = results.asList().toArray();
> > System.out.println(" Projected value: " + value[0]);
> >
> > You can find sample queries on different type of objects
> (collections,
> > etc) at:
> >
> >
> https://geode.apache.org/docs/guide/18/getting_started/querying_quick_reference.html
> >
> > Also in order to determine where the time is getting spent, can you
> > separate out object creation through JSONFormatter from put
> operation.
> > E.g.:
> > PdxInstance pdxInstance = JSONFormatter.fromJSON(jsonDoc_2);
> > // Time taken to format:
> > region.put("1", pdxInstance);
> > // Time taken to add to cache:
> >
> > And measure the time separately. It will help to see if the time is
> spent
> > in getting the PdxInstance or in doing puts. Also, can you measure
> the time
> > in avg.
> > E.g. Say time measured for puts from 1000 to 2000 and avg time for
> those
> > puts.
> >
> > -Anil.
> >
> >
> > On 11/23/20, 11:27 AM, "ankit Soni"
> wrote:
> >
> > Hello geode-dev,
> >
> > I am *evaluating usage of Geode (1.12) with storing JSON
> documents and
> > querying the same*. I am able to store the json records
> successfully in
> > geode but seeking guidance on how to query them.
> > More details on code and sample json is,
> >
> >
> > *Sample client-code*
> >
> > import org.apache.geode.cache.client.ClientCache;
> > import org.apache.geode.cache.client.ClientCacheFactory;
> > import org.apache.geode.cache.client.ClientRegionShortcut;
> > import org.apache.geode.pdx.JSONFormatter;
> > import org.apache.geode.pdx.PdxInstance;
> >
> > public class MyTest {
> >
> > *//NOTE: Below is truncated json, single json document can
> max
> > contain an array of col1...col30 (30 diff attributes) within
> data. *
> > public final static String jsonDoc_2 = "{" +
> > "\"data\":[{" +
> > "\"col1\": {" +
> > "\"k11\": \"aaa\"," +
> > "\"k12\":true," +
> > "\"k13\": ," +
> > "\"k14\":
> \"2020-12-31:00:00:00\"" +
> > "}," +
> >
