UPDATE Array Value

[Michael MacFadden]

We have a data structure that looks something like this (the class name is "model", and there is presently only one document in the class):

{

    "data": [

        {"value": 0},

        {"value": 1},

        {"value": 2}

    ]

}

I would like to update the value of one of the elements in the array.  I was trying to use the following SQL Query:

UPDATE model SET data[0] = "some other value"

The expected result was a document that looks like this:

{

    "data": [

        "some other value",

        {"value": 1},

        {"value": 2}

    ]

}

However, the query succeeds, but the update does not happen.

Not that doing this query:   

UPDATE model SET data[0].value = "some other value"

Correctly updates the document to look like this:

{

    "data": [

        {"value": "some other value"},

        {"value": 1},

        {"value": 2}

    ]

}

I also tried to simplify this:

{

    "data": ["a", "b", "c"]

}

UPDATE model SET data[0] = "x"

I was hoping to replace the "a" with the "x", but nothing happens.

I am using 2.0.13.

Any ideas?

[alessand...@gmail.com]

Hi Michael,

I have the same problem

I created a function javascript or java code to solve the problem

JavaScript function :  parameter = "i" and "newValue"

var g=orient.getGraph();

var b=g.command('sql','select from model');

var values="[";

for(j=0;j<b.length;j++){

  var data=b[j].getProperty('data');

  var size=data.size();

  if(i<size && i>=0){

    data.set(i,newValue);

  for(k=0;k<size;k++){

      if(k==0)

          values=values + "'"+data.get(k)+ "'";

      else

          values=values + ",'" + data.get(k) + "'";

    }

    values=values + "]";

    var query="update model set data = " + values;

    g.command('sql',query);

  }

}

Java

OrientGraph g=new OrientGraph(currentPath);

int i=1;

String newValue="qwerty";

Iterable<OrientVertex> result=g.command(new OSQLSynchQuery<OrientVertex>("select from Model")).execute();

for(OrientVertex v:result){

OTrackedList<String> track=v.getProperty("data");

if(i<track.size() && i>=0){

track.set(i,newValue);

String values="[";

for(int k=0;k<track.size();k++){

if(k==0)

values+="'"+track.get(k)+ "'";

else

values+=",'" + track.get(k) + "'";

}

values+="]";

String query="update Model set data = " + values;

g.command(new OCommandSQL(query)).execute();

}

}

g.shutdown();

Regards,

Alessandro

[Michael MacFadden]

Alessandro,

Thanks for the information.  I think this work around will help us for now.  The only issue I see is that it looks like the entire array / list must be loaded. I think for relatively small arrays (lists) this would work well.  We may have large lists, with the values in the list themselves being complex objects.  In the long term loading all of that data may not be feasible to simply replace one value in the list.

Again, thanks for the work around.

~Michael

[alessand...@gmail.com]

Hi Michael,

try this code:

Javascript:

var g=orient.getGraph();

var b=g.command('sql','select from model');

var values="[";

for(j=0;j<b.length;j++){

  var data=b[j].getProperty('data');

  var size=data.size();

  if(i<size && i>=0){

    data.set(i,newValue);

    var newData=data.toString().replace("[","['").replace(", ", "', '").replace("]","']");

    var query="update model set data = " + newData;

    g.command('sql',query);

  }

}

Java:

int i=2;

String newValue="querty";

Iterable<OrientVertex> result=g.command(new OSQLSynchQuery<OrientVertex>("select from Model")).execute();

for(OrientVertex v:result){

OTrackedList<String> track=v.getProperty("data");

if(i<track.size() && i>=0){

track.set(i,newValue);

String newData=track.toString().replace("[", "['").replace(", ", "', '").replace("]","']");

String query2="update Model set data = " + newData;

g.command(new OCommandSQL(query2)).execute();

}

}

Regards,

Alessandro