How to select documents which have specific substring in its one of key?

SQL++
,
1 

Hi Team,

if my document structure is looking like below

{
id:"uuid1",
items : [
{
imageAssetMeta: {
  path: "pathToimage"
},
Status:"appStatus"
}
]
}

{
id:"uuid2",
items : [
{
imageMeta: {
  path: "pathToimage"
},
Status:"appStatus"
}
]
}

How I can get document with id = uuid1 in the result-set, because it contains a key which has substring "AssetMeta"

Is there any way to check specific substring inside key and return those documents only ?
2

Use LIKE. As there is leading wildcard it is very intesive.

SELECT ....
FROM  mybucket AS b
WHERE b.id LIKE "%AssetMeta%"

If queries always use same thing you can add that in index WHERE clause

3

I don’t have an answer, but I can rephrase the question more accurately.

The document contains an array named ‘items’, and one of the elements of items has a property named ’ imageAssetMeta’.

4

You might try:

SELECT ...
FROM myBucket b
WHERE ANY v IN OBJECT_NAMES(b.items[*]) SATISFIES v LIKE '%AssetMeta%' END;
1 Like
5

I thought key means document key. What you looking is in field name

SELECT .....
FROM myBucket AS b
WHERE ANY item IN b.items SATISFIES (ANY n:v IN item SATISFIES n LIKE "%AssetMeta%" END) END;

If you need AssetMeta Any where in the document field try this

WITH docs AS ([{ "id":"uuid1", "items" : [ { "imageAssetMeta": { "path": "pathToimage" }, "Status":"appStatus" } ] }, 
               { "id":"uuid3", "items" : [ {"a":{ "imageAssetMeta": { "path": "pathToimage" }, "Status":"appStatus" }} ] },
              { "id":"uuid2", "items" : [ { "imageMeta": { "path": "pathToimage" }, "Status":"appStatus" } ] }
             ])
SELECT d.*
FROM docs AS d
WHERE ANY n:v WITHIN d SATISFIES n LIKE "%AssetMeta%" END;

FOR n:v IN item END
   if item is OBJECT iterates each field of item (n holds name, v holds value)
   if item is ARRAY  iterates each  entry (n holds position in the array, v holds value)

you can also use CONTAINS(n,“AssetMeta”) vs n LIKE “%AssetMeta%”

If always looking (constant) for “AssetMeta”

SELECT .......
FROM myBucket AS d
WHERE ANY item IN d.items SATISFIES CONTAINS(CONCAT2(",",OBJECT_NAMES(item)), "AssetMeta") = true END;

CREATE INDEX ix1 ON myBucket (DISTINCT ARRAY CONTAINS(CONCAT2(",",OBJECT_NAMES(item)), "AssetMeta") FOR item IN items END);
1 Like
6

Thanks for the reply. I am looking for documents in which at least one field name with substring “AssetMeta” exists, no matter how nested the field name is.

7 
SELECT d.*
FROM mybucket AS d
WHERE ANY n:v WITHIN d SATISFIES n LIKE "%AssetMeta%" END;
1 Like
8

Thanks for the detailed information here.

9

Thank you…This is exactly what I was looking for.

10

Thanks for reply. OBJECT_NAMES(b.items[*]) will search field names within b.items only…but I need the documents in which field name can be exists anywhere, no matter how nested it is.

11

OK, you could use OBJECT_PATHS then instead, but @vsr1’s approach is neater and more efficient.

12

If you need index follow @dh OBJECT_PATHS()

CREATE INDEX ix1 ON myBucket (DISTINCT ARRAY CONTAINS(v, "AssetMeta") FOR v IN OBJECT_PATHS(self) END);

SELECT .......
FROM myBucket AS d
WHERE ANY v IN OBJECT_PATHS(d) SATISFIES CONTAINS(v, "AssetMeta") = true END;