Is it possible to use array indexing and querying with object collections/dictionaries, not arrays?

SQL++
,
1

Say I have a document as follows:

{
	"items" :
	{
		"item1" :
		{
			"value" : "hello"
		},
		"item2" :
		{
			"value" : "world"
		}
	}
}

Is it possible to index and query the “value” properties in this structure the same as how arrays are represented?

In this case, I will not know ahead of time the names of “item1” and “item2”, so I would need to query for any “value” property contained within this structure. I have read through the Collection Operators, but all of the examples are arrays.

2 
CREATE INDEX ix1 ON default (DISTINCT ARRAY v.`value` FOR v IN OBJECT_VALUES(items) END);
SELECT META(d).id FROM default d WHERE ANY v IN OBJECT_VALUES(d.items) SATISFIES v.`value` = "hello" END;

If you don’t know the level also use WITHIN instead of IN both index and query.

3

Thanks, I was sort of close.

Now I am having problems selecting the actual items themselves… so in your example, I want to select the full content of ‘v’.

4 
SELECT META(d).id, nval FROM default d 
LET nval = ARRAY v FOR v IN OBJECT_VALUES(d.items) WHEN  v.`value` = "hello" END;
WHERE  ARRAY_LENGTH(nval) > 0;

Do EXPLAIN and check if the index is used. Also checkout OBJECT_PAIRS()

5

That produces a syntax error.

select val from default d
let val = array p in object_values(d.procedures) satisfies p.descriptions.en = 'something' end
where tenantKey='a' and type='t' and array_length(val) > 0
6

SELECT val FROM default d
LET val = ARRAY p FOR p IN OBJECT_VALUES(d.procedures) WHEN p.descriptions.en = ‘something’ END
WHERE tenantKey=‘a’ AND type=‘t’ AND array_length(val) > 0;

1 Like
7

That’s the ticket, thanks!