Iterate objects inside array

SQL++
1

I have array:

{
user:‘roy’,
images:[
{img:‘imgUrl1’,date=“today”}
{img:‘imgUrl2’,date=“tommorrow”}
]
}

I want to match the right item in the array when if found.

For example:
user send url: imgUrl1
I need to make query to find this element inside the array.

I tried:
select * from db
where every item in images satisfies item.image=‘imgUrl1’;

But nothing found

2

SELECT * FROM db WHERE ANY item IN images SATISFIES item.img = 'imgUrl1' END;

ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY

Range predicates (ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY) allow you to test a boolean condition over the elements or attributes of a collection or object(s). They each evaluate to a boolean value.

ANY or SOME is TRUE if the collection is non-empty and at least one element matches.

EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.

ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.

1 Like
3

Thanks @vsr1,
The next steps for me are:

  1. select just the value of the image in case of match.
  2. update just the value for the match key.

Exmaples:
For case 1:
if we iterate img and it match to ‘imgUrl1’ ,
then i need as result the value : ‘imgUrl1’

For case 2:
if we iterate img and it match to ‘imgUrl1’ ,
then i need to update the new value with the value ‘imgUrl1’

Is there any way to do that?
(for now i am doing it in the server, but i think that it waste of usage of cpu and memory in this way)

4 
INSERT INTO default VALUES("k01",{"user":"roy","images":[{"img":"imgUrl1","date":"today"},{"img":"imgUrl2","date":"tommorrow"}]});

SELECT item.img 
      FROM default AS d 
      UNNEST d.images AS item
      WHERE item.img = 'imgUrl1';

UPDATE default AS d 
        SET v.img = "ABC" FOR v IN d.images WHEN v.img = "imgUrl1" END 
        WHERE ANY item IN d.images SATISFIES item.img = "imgUrl1" END;
1 Like
5

Wow this is Awesome!
Thanks.

6

I am trying to understand, why do we need the line:
“WHERE ANY v IN d.images SATISFIES v.img = “imgUrl1” END;”

We already defined it in the condition "WHEN item.img = “imgUrl1”, No?

7

WHERE clause decides which documents qualify for update. If you don’t have WHERE clause every document is qualified for update, document actually modified or not decided by SET clause.

WHEN clause is inside the SET. WHEN decides which portion of the array object needs to update.

If bucket has 100 documents and 50 of the have have array images and 10 of those has img = “imgUrl1”. Without the WHERE clause all 100 will qualify for update and 10 of them modifies img all others may be updated same document.

If there is WHERE clause 10 of them will qualify for update.

Example: with WHERE clause and following index only images that has item.img = ‘imgUrl1’ will be scanned and fetched and updated. All others will not even Scanned by indexer and query will much faster. NOTE: variable used here item in UNNEST/ANY needs to be matched with Index.

CREATE INDEX ix1 ON default( DISTINCT ARRAY item.img FOR item IN images END);

1 Like
8

Thanks, very useful to know that.