You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@geode.apache.org by Dharam Thacker <dh...@gmail.com> on 2017/01/01 13:56:13 UTC
OQL on nested collections
Hi Team,
I have a JsonRegion which looks like Region<String,PdxInstance>.
Example : jsonRegionTemplate.put("1", JSONFormatter.fromJSON(s));
There is a simple requirement where my query say, "Give me all books whose
category is fiction"
DBMS : select * from Book where catrgory = 'fiction' [Simple as there is
only 1 store]
DBMS : select b.* from Store s,Book b where s.id = b.storeId and b.category
= 'fiction' [If there are multiple stores -- But simple and clean]
OQL : select j.store.book from /jsonRegion j where j.store.book[0].category
= 'fiction'
*Above OQL is not all worth I guess as you can never know index :)
[Trivial]. *
*Is there any way to extract the same?*
*Probably : select j from /jsonRegion j where j.store.book[*].category =
'fiction'*
Contents as below, which I extracted using below,
System.out.println("In region [" + regionName + "] created key [" + key+ "]
--> value [" + JSONFormatter.toJSON((PdxInstance) newValue) + "]");
key [1] --> value [{
"store" : {
"book" : [ {
"category" : "reference",
"author" : "Nigel Rees",
"title" : "Sayings of the Century",
"price" : 8.95
}, {
"category" : "fiction",
"author" : "Evelyn Waugh",
"title" : "Sword of Honour",
"price" : 12.99
}, {
"category" : "fiction",
"author" : "Herman Melville",
"title" : "Moby Dick",
"isbn" : "0-553-21311-3",
"price" : 8.99
}, {
"category" : "fiction",
"author" : "J. R. R. Tolkien",
"title" : "The Lord of the Rings",
"isbn" : "0-395-19395-8",
"price" : 22.99
} ],
"bicycle" : {
"color" : "red",
"price" : 19.95
}
}
}]
Thanks,
- Dharam Thacker
Re: OQL on nested collections
Posted by Anilkumar Gingade <ag...@pivotal.io>.
Can you try this and see if it works....
*select distinct j from /jsonRegion j, j.store.books b where b.category =
'fiction'*
-Anil.
On Sun, Jan 1, 2017 at 5:56 AM, Dharam Thacker <dh...@gmail.com>
wrote:
> Hi Team,
>
> I have a JsonRegion which looks like Region<String,PdxInstance>.
>
> Example : jsonRegionTemplate.put("1", JSONFormatter.fromJSON(s));
>
> There is a simple requirement where my query say, "Give me all books whose
> category is fiction"
>
> DBMS : select * from Book where catrgory = 'fiction' [Simple as there is
> only 1 store]
>
> DBMS : select b.* from Store s,Book b where s.id = b.storeId and
> b.category = 'fiction' [If there are multiple stores -- But simple and
> clean]
>
> OQL : select j.store.book from /jsonRegion j where
> j.store.book[0].category = 'fiction'
>
> *Above OQL is not all worth I guess as you can never know index :)
> [Trivial]. *
>
>
>
> *Is there any way to extract the same?*
>
> *Probably : select j from /jsonRegion j where j.store.book[*].category =
> 'fiction'*
>
> Contents as below, which I extracted using below,
>
> System.out.println("In region [" + regionName + "] created key [" + key+
> "] --> value [" + JSONFormatter.toJSON((PdxInstance) newValue) + "]");
>
> key [1] --> value [{
> "store" : {
> "book" : [ {
> "category" : "reference",
> "author" : "Nigel Rees",
> "title" : "Sayings of the Century",
> "price" : 8.95
> }, {
> "category" : "fiction",
> "author" : "Evelyn Waugh",
> "title" : "Sword of Honour",
> "price" : 12.99
> }, {
> "category" : "fiction",
> "author" : "Herman Melville",
> "title" : "Moby Dick",
> "isbn" : "0-553-21311-3",
> "price" : 8.99
> }, {
> "category" : "fiction",
> "author" : "J. R. R. Tolkien",
> "title" : "The Lord of the Rings",
> "isbn" : "0-395-19395-8",
> "price" : 22.99
> } ],
> "bicycle" : {
> "color" : "red",
> "price" : 19.95
> }
> }
> }]
>
> Thanks,
> - Dharam Thacker
>
Re: OQL on nested collections
Posted by Dharam Thacker <dh...@gmail.com>.
Thanks Dan! Got it.
Regards,
Dharam
On Jan 2, 2017 23:33, "Dan Smith" <ds...@pivotal.io> wrote:
> HI Dharam,
>
> I think the way you would do such a query in OQL is something like this (I
> based this on the section on querying nested collections on this page
> <http://geode.apache.org/docs/guide/getting_started/querying_quick_reference.html>
> ):
>
> SELECT book FROM /jsonRegion store, store.book book WHERE book.category =
> 'fiction'
>
> -Dan
>
> On Sun, Jan 1, 2017 at 6:22 AM, Dharam Thacker <dh...@gmail.com>
> wrote:
>
>> Hi,
>>
>> Forgot to mention that not only for JSON but even if you have domain
>> model defined.
>>
>> Region<Long,Store> store;
>>
>> Class Store {
>> Long storeId;
>> Set<Book> books;
>>
>> // Other stuffs
>> }
>>
>> - Dharam Thacker
>>
>> On Sun, Jan 1, 2017 at 7:26 PM, Dharam Thacker <dharamthacker88@gmail.com
>> > wrote:
>>
>>> Hi Team,
>>>
>>> I have a JsonRegion which looks like Region<String,PdxInstance>.
>>>
>>> Example : jsonRegionTemplate.put("1", JSONFormatter.fromJSON(s));
>>>
>>> There is a simple requirement where my query say, "Give me all books
>>> whose category is fiction"
>>>
>>> DBMS : select * from Book where catrgory = 'fiction' [Simple as there is
>>> only 1 store]
>>>
>>> DBMS : select b.* from Store s,Book b where s.id = b.storeId and
>>> b.category = 'fiction' [If there are multiple stores -- But simple and
>>> clean]
>>>
>>> OQL : select j.store.book from /jsonRegion j where
>>> j.store.book[0].category = 'fiction'
>>>
>>> *Above OQL is not all worth I guess as you can never know index :)
>>> [Trivial]. *
>>>
>>>
>>>
>>> *Is there any way to extract the same?*
>>>
>>> *Probably : select j from /jsonRegion j where j.store.book[*].category =
>>> 'fiction'*
>>>
>>> Contents as below, which I extracted using below,
>>>
>>> System.out.println("In region [" + regionName + "] created key [" + key+
>>> "] --> value [" + JSONFormatter.toJSON((PdxInstance) newValue) + "]");
>>>
>>> key [1] --> value [{
>>> "store" : {
>>> "book" : [ {
>>> "category" : "reference",
>>> "author" : "Nigel Rees",
>>> "title" : "Sayings of the Century",
>>> "price" : 8.95
>>> }, {
>>> "category" : "fiction",
>>> "author" : "Evelyn Waugh",
>>> "title" : "Sword of Honour",
>>> "price" : 12.99
>>> }, {
>>> "category" : "fiction",
>>> "author" : "Herman Melville",
>>> "title" : "Moby Dick",
>>> "isbn" : "0-553-21311-3",
>>> "price" : 8.99
>>> }, {
>>> "category" : "fiction",
>>> "author" : "J. R. R. Tolkien",
>>> "title" : "The Lord of the Rings",
>>> "isbn" : "0-395-19395-8",
>>> "price" : 22.99
>>> } ],
>>> "bicycle" : {
>>> "color" : "red",
>>> "price" : 19.95
>>> }
>>> }
>>> }]
>>>
>>> Thanks,
>>> - Dharam Thacker
>>>
>>
>>
>
Re: OQL on nested collections
Posted by Dan Smith <ds...@pivotal.io>.
HI Dharam,
I think the way you would do such a query in OQL is something like this (I
based this on the section on querying nested collections on this page
<http://geode.apache.org/docs/guide/getting_started/querying_quick_reference.html>
):
SELECT book FROM /jsonRegion store, store.book book WHERE book.category =
'fiction'
-Dan
On Sun, Jan 1, 2017 at 6:22 AM, Dharam Thacker <dh...@gmail.com>
wrote:
> Hi,
>
> Forgot to mention that not only for JSON but even if you have domain model
> defined.
>
> Region<Long,Store> store;
>
> Class Store {
> Long storeId;
> Set<Book> books;
>
> // Other stuffs
> }
>
> - Dharam Thacker
>
> On Sun, Jan 1, 2017 at 7:26 PM, Dharam Thacker <dh...@gmail.com>
> wrote:
>
>> Hi Team,
>>
>> I have a JsonRegion which looks like Region<String,PdxInstance>.
>>
>> Example : jsonRegionTemplate.put("1", JSONFormatter.fromJSON(s));
>>
>> There is a simple requirement where my query say, "Give me all books
>> whose category is fiction"
>>
>> DBMS : select * from Book where catrgory = 'fiction' [Simple as there is
>> only 1 store]
>>
>> DBMS : select b.* from Store s,Book b where s.id = b.storeId and
>> b.category = 'fiction' [If there are multiple stores -- But simple and
>> clean]
>>
>> OQL : select j.store.book from /jsonRegion j where
>> j.store.book[0].category = 'fiction'
>>
>> *Above OQL is not all worth I guess as you can never know index :)
>> [Trivial]. *
>>
>>
>>
>> *Is there any way to extract the same?*
>>
>> *Probably : select j from /jsonRegion j where j.store.book[*].category =
>> 'fiction'*
>>
>> Contents as below, which I extracted using below,
>>
>> System.out.println("In region [" + regionName + "] created key [" + key+
>> "] --> value [" + JSONFormatter.toJSON((PdxInstance) newValue) + "]");
>>
>> key [1] --> value [{
>> "store" : {
>> "book" : [ {
>> "category" : "reference",
>> "author" : "Nigel Rees",
>> "title" : "Sayings of the Century",
>> "price" : 8.95
>> }, {
>> "category" : "fiction",
>> "author" : "Evelyn Waugh",
>> "title" : "Sword of Honour",
>> "price" : 12.99
>> }, {
>> "category" : "fiction",
>> "author" : "Herman Melville",
>> "title" : "Moby Dick",
>> "isbn" : "0-553-21311-3",
>> "price" : 8.99
>> }, {
>> "category" : "fiction",
>> "author" : "J. R. R. Tolkien",
>> "title" : "The Lord of the Rings",
>> "isbn" : "0-395-19395-8",
>> "price" : 22.99
>> } ],
>> "bicycle" : {
>> "color" : "red",
>> "price" : 19.95
>> }
>> }
>> }]
>>
>> Thanks,
>> - Dharam Thacker
>>
>
>
Re: OQL on nested collections
Posted by Dharam Thacker <dh...@gmail.com>.
Hi,
Forgot to mention that not only for JSON but even if you have domain model
defined.
Region<Long,Store> store;
Class Store {
Long storeId;
Set<Book> books;
// Other stuffs
}
- Dharam Thacker
On Sun, Jan 1, 2017 at 7:26 PM, Dharam Thacker <dh...@gmail.com>
wrote:
> Hi Team,
>
> I have a JsonRegion which looks like Region<String,PdxInstance>.
>
> Example : jsonRegionTemplate.put("1", JSONFormatter.fromJSON(s));
>
> There is a simple requirement where my query say, "Give me all books whose
> category is fiction"
>
> DBMS : select * from Book where catrgory = 'fiction' [Simple as there is
> only 1 store]
>
> DBMS : select b.* from Store s,Book b where s.id = b.storeId and
> b.category = 'fiction' [If there are multiple stores -- But simple and
> clean]
>
> OQL : select j.store.book from /jsonRegion j where
> j.store.book[0].category = 'fiction'
>
> *Above OQL is not all worth I guess as you can never know index :)
> [Trivial]. *
>
>
>
> *Is there any way to extract the same?*
>
> *Probably : select j from /jsonRegion j where j.store.book[*].category =
> 'fiction'*
>
> Contents as below, which I extracted using below,
>
> System.out.println("In region [" + regionName + "] created key [" + key+
> "] --> value [" + JSONFormatter.toJSON((PdxInstance) newValue) + "]");
>
> key [1] --> value [{
> "store" : {
> "book" : [ {
> "category" : "reference",
> "author" : "Nigel Rees",
> "title" : "Sayings of the Century",
> "price" : 8.95
> }, {
> "category" : "fiction",
> "author" : "Evelyn Waugh",
> "title" : "Sword of Honour",
> "price" : 12.99
> }, {
> "category" : "fiction",
> "author" : "Herman Melville",
> "title" : "Moby Dick",
> "isbn" : "0-553-21311-3",
> "price" : 8.99
> }, {
> "category" : "fiction",
> "author" : "J. R. R. Tolkien",
> "title" : "The Lord of the Rings",
> "isbn" : "0-395-19395-8",
> "price" : 22.99
> } ],
> "bicycle" : {
> "color" : "red",
> "price" : 19.95
> }
> }
> }]
>
> Thanks,
> - Dharam Thacker
>