Add an array to an existing one and retrieve the index it was inserted into


#1

okay so i have a 2D array i need to insert into another 2D array and retrieve the index it was inserted into.
I’m aware that the insert operator itself overwrites the values at the ‘0’ index in the target array.

I successfully got this to work on a 1D source array using cross_join and other operators but I can’t figure out how to do it if the the source array was 2D

here’s the query for a single dimension source array:
insert(redimension(apply(cross_join(SOURCE,filter(aggregate(TARGET,count(*),dim),TARGET.dim=0)),face,int64(count)),TARGET),TARGET)

I need a similar one of my SOURCE was 1D and also to print out the index it was inserted at. Any help?


#2

Hello,

Sorry about the difficulty. I think it’s important to clarify a few important concepts. First, there are many different ways to combine two 2D arrays: add rows, add columns, place one “on top of” the other, or something more esoteric. In that regard, you have to tell SciDB what to do. Some examples are described in this thread: Analyze and concat operators no longer exist in 15.12?

Secondly, you can use dimensions() to get the current lower and upper bound of an array along a particular axis. You can join that to your inserted data to specify that you want to insert “after the last row” or “after the last column.” Those are two common cases.

For example, let’s create an empty target and two sources:

$ iquery -aq "create array TARGET <val:double> [x=1:*; y=1:*]"
Query was executed successfully

$ iquery -aq "store(build(<val:double>[x=1:3; y=1:3], 1), SOURCE1)"
{x,y} val
{1,1} 1
{1,2} 1
{1,3} 1
{2,1} 1
{2,2} 1
{2,3} 1
{3,1} 1
{3,2} 1
{3,3} 1

$ iquery -aq "store(build(<val:double>[x=1:3; y=1:3], 2), SOURCE2)"
{x,y} val
{1,1} 2
{1,2} 2
{1,3} 2
{2,1} 2
{2,2} 2
{2,3} 2
{3,1} 2
{3,2} 2
{3,3} 2

Note that dimensions() of an empty array will return a large negative value for high and a large positive value for low:

$ iquery -aq "dimensions(TARGET)"
{No} name,start,length,chunk_interval,chunk_overlap,low,high,type
{0} 'x',1,4611686018427387903,-1,0,4611686018427387903,-4611686018427387903,'int64'
{1} 'y',1,4611686018427387903,-1,0,4611686018427387903,-4611686018427387903,'int64'

Using that fact, a query to insert a source along the x dimension is as follows:

$ iquery -anq "
insert(
  redimension(
    apply(
      cross_join(
        SOURCE1, 
        project(filter(dimensions(TARGET), name='x'), low, high)
      ), 
      new_x, iif(high<low, x, x+high)
    ), 
   <val:double>[new_x=1:*; y=1:*]
  ), 
  TARGET
)"

Same for the other source:

$ iquery -aq "insert(redimension(apply(cross_join(SOURCE2, project(filter(dimensions(TARGET), name='x'), low, high)), new_x, iif(high<low, x, x+high)), <val:double>[new_x=1:*; y=1:*]), TARGET)"

We can’t quite return just the position(s) of the new data in the same query. But re-running dimensions() again easily shows that x has now grown to 6 after 2 insertions while y ranges from 1 to 3. Previous versions of TARGET remain accessible until remove_versions is executed:

$ iquery -aq "dimensions(TARGET)"
{No} name,start,length,chunk_interval,chunk_overlap,low,high,type
{0} 'x',1,4611686018427387903,4,0,1,6,'int64'
{1} 'y',1,4611686018427387903,4,0,1,3,'int64'

$ iquery -aq "op_count(TARGET@1)"
{i} count
{0} 9

$ iquery -aq "op_count(TARGET@2)"
{i} count
{0} 18

Does this help?