Examples of JSON Select Queries

[TheOutlander]

Hello - Can anyone please point me to a document or share an example of how to select records matching a certain criteria within the JSON? 

Thanks,

Nick

[Luca Garulli]

Hi,

OrientDB splits the original JSON in a tree of records so you can query directly the tree. Example:

{ @class : 'Account',

  name : 'Luca',

  vehicles : [ {

     @class : 'Vehicle',

     type : 'Car',

     model : 'Maserati',

     isItTrue: false

  } ]

 }

This is stored in 2 linked records where the first one has a collections of vehicles. Now you can do something like:

> select from Account where vehicles contains ( model = 'Maserati' )

or again:

> select vehicles.model from Account

Lvc@

[TheOutlander]

Thanks for the quick response. This doesn't work for me as I must be doing something wrong.

Is this the right way to insert a JSON doc??

insert into test (json) VALUES { @class : 'Account',  name : 'Luca',  vehicles : [ {     @class : 'Vehicle',     type : 'Car',     model : 'Maserati',     isItTrue: false  } ] }

When I query the table, I get

orientdb> select json from test

---+---------+--------------------

  #| RID     |json

---+---------+--------------------

  0|    #-2:0|{TEST=1}

  1|    #-2:1|asd

  2|    #-2:2|{_NOT_PARSED_=[{_NOT_PARSED_=false}]}

Why does the record show as NOT_PARSED....? I think I'm missing some fundamental concepts about how OrientDB treats documents, queries, etc.  

Thanks,

Nick

[TheOutlander]

I guess one part of the problem was the quotes for the keys which I fixed by using the following statement:

insert into j1 (json) VALUES ({ "@class" : 'test',  "name" : 'Luca',  "vehicles" : [ {     "@class" : 'Vehicle',     "type" : 'Car',     "model" : 'Maserati',     "isItTrue": false  } ] })

Is this the right way to insert JSON (ie: it should have a column name)?? Or is this storing a string and not JSON??

How will I query this? The one you mentioned doesn't work....I'm doing something wrong.

Thanks,

Nick

[TheOutlander]

I'm assuming that it's storing JSON objects. Here is what the table looks like:

orientdb> select from j1

---+---------+--------------------

  #| RID     |json

---+---------+--------------------

  0|    #12:0|{@class=test, name=Luca, vehicles=[{model=Maserati, @class=Vehicle, isItTrue=false, type=Car}]}

But only one query returns data....select from j1 where json.vehicles.LENGTH() > 1

The following query doesn't return anything: select from j1 where json.vehicles CONTAINS ( model = 'Maserati' )

What is the problem here?

Thanks,

Nick

[Luca Garulli]

Hi,

I've fixed some bug against nested JSON. Can you retry it with last SVN trunk please?

Lvc@

[Shishya]

Hi Luca,

JSON fields come as an object and even toArray() function converts it to some object only.

I have this json in a field and seclection/conversion is doing a flip/flop of formats which is making me crazy

So First query gives this

var c = db.command("sql","select from mynode");
var cit = c[0].getRecord().field('field1');
cit >Result:

[{"@type":"d","@version":0,"value":[{"kind":"A","fb":"found","inme":"Kato1","id":"4497057"},{"kind":"A","fb":"found","inme":"Kato2","id":"4497067"},{"kind":"A","fb":"found","inme":"Kato3","id":"4497037"}]}]

var cit1 = c[0].getRecord().field('field1').toArray();
cit1 or cit1[0]>Result:

[{"@type":"d","@version":0,"value":[{"kind":"A","fb":"found","inme":"Kato","id":"4497057"}]}]

Now the craziest part. I have to access the field values.
cit[0].kind returns null
cit[0] gives error
cit[0]+'' returns this
[{"@type":"d","@version":0,"value":[{"kind"="A","fb"="found","inme"="Kato","id"="4497057"}]}]

I am using 1.7rc2.SNAPSHOT and same results in 1.7rc1

Please help

regards

[Kishy Kumar]

Here is the solution: 

http://stackoverflow.com/questions/10735203/does-anyone-have-a-working-example-of-json-insert-select-in-orientdb/29093705#29093705

Please check.