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 <aging...@vmware.com> wrote: > Ankit, > > Here is how to query col2. > "SELECT d.col2 FROM /JsonRegion v, v.data d, d.col2 c where c.k21 = > '222222'"; > > 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" <ankit.soni.ge...@gmail.com> 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 = '222222') 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=1111, > k14=2020-12-31T00..} > Col2=[PDX[25385544, __GEMFIRE_JSON]{k21=22222222, 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 <aging...@vmware.com> > 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" <ankit.soni.ge...@gmail.com> > 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\": 1111," + > > "\"k14\": > \"2020-12-31:00:00:00\"" + > > "}," + > > "\"col2\":[{" + > > "\"k21\": \"222222\"," + > > "\"k22\": true" + > > "}]" + > > "}]" + > > "}"; > > > > * //NOTE: Col1....col30 are mix of JSONObject ({}) and > JSONArray > > ([]) as shown above in jsonDoc_2;* > > > > public static void main(String[] args){ > > > > //create client-cache > > ClientCache cache = new > > ClientCacheFactory().addPoolLocator(LOCATOR_HOST, PORT).create(); > > Region<String, PdxInstance> region = cache.<String, > > > > > PdxInstance>createClientRegionFactory(ClientRegionShortcut.CACHING_PROXY) > > .create(REGION_NAME); > > > > //store json document > > region.put("key", JSONFormatter.fromJSON(jsonDoc_2)); > > > > //How to query json document like, > > > > // 1. select col2.k21, col1, col20 from /REGION_NAME > where > > data.col2.k21 = '222222' OR data.col2.k21 = '333333' > > > > // 2. select col2.k21, col1.k11, col1 from /REGION_NAME > where > > data.col1.k11 in ('aaa', 'xxx', 'yyy') > > } > > } > > > > *Server: Region-creation* > > > > gfsh> create region --name=REGION_NAME --type=PARTITION > > --redundant-copies=1 --total-num-buckets=61 > > > > > > *Setup: Distributed cluster of 3 nodes > > * > > > > *My Observations/Problems* > > - Put operation takes excessive time: region.put("key", > > JSONFormatter.fromJSON(jsonDoc_2)); - Fetching a single record > from > > () a > > file and Storing in geode approx. takes . 3 secs > > Is there any suggestions/configuration related to > JSONFormatter API > > or > > other to optimize this...? > > > > *Looking forward to guidance on querying this JOSN for above > sample > > queries.* > > > > *Thanks* > > *Ankit.* > > > > > >
