I have to skip “/emv3dsecure/v1/devicedatacollection” and search for nested fields in parameters array. The level “/emv3dsecure/v1/devicedatacollection” contains information that i am not able to know. In the same way i need to skip “/emv3dsecure/v1/stepup” and “/emv3dsecure/v1/merchantreturnurl” fields.
I tried
OBJECT_VALUES(paths)[0].post path in WHERE Clause but it returns only the data below the first post field. Why OBJECT_VALUES(paths)[*].post is not working? Is there any other way to solve that problem?
I need to be able to retrieve data below all post fields
It will work . post becomes ARRAY. I would recommend first do in projection and see how the structure then try use in WHERE clause.
SELECT m.*
FROM mybucket AS m
WHERE ANY v IN ARRAY_FLATTEN(OBJECT_VALUES(paths)[*].post[*].parameters,2) SATISFIES v.name = "TransactionId" END;
SELECT m.*
FROM mybucket AS m
WHERE ANY v IN OBJECT_VALUES(m.paths) SATISFIES (ANY p IN v.post.parameters SATISFIES p.name = "TransactionId" END) END;
SELECT m.*
FROM mybucket AS m
WHERE ANY v WITHIN m.paths SATISFIES (ANY p IN v.post.parameters SATISFIES p.name = "TransactionId" END) END;
You can skip as many levels as you want (using WITHIN) as long as you can identify the field uniquely
SELECT m.*
FROM mybucket AS m
WHERE ANY v WITHIN {m} SATISFIES v.name = "TransactionId" END ;
Any where in the document name = “TransactionId” qualifies the document
SELECT m.*
FROM mybucket AS m
WHERE ANY v WITHIN m.paths SATISFIES IS_ARRAY(v.parameters) AND (ANY p IN v.parameters SATISFIES p.name = "TransactionId" END) END;
I tried to skip levels of nesting using your last queries, but it didn’t work! I created some indexes about that, but i can’t extract info about parameters without using the ‘post’ field in my query
SELECT m.*
FROM mybucket AS m
WHERE ANY v WITHIN m.paths SATISFIES IS_ARRAY(v.parameters) AND (ANY p IN v.parameters SATISFIES p.name = “TransactionId” END) END;
Warning message: Some fields not found (They may be misspelled)
I got the same warning message while executing
SELECT ARRAY v FOR v WITHIN m WHEN v.parameters IS NOT NULL END AS parametrs
FROM OASBucket AS m
UNNEST parametrs.parameters as par
I think it cannot deal with the fields we are skiping
INSERT INTO default VALUES ("f01",{ "paths": { "/emv3dsecure/v1/devicedatacollection": { "post": { "summary": "EMV 3D SEcure Device Data Collection API", "parameters": [ { "name": "ProfileId", "in": "query", "description": "ProfileId for Cors Authentication", "required": true, "style": "form", "explode": true, "schema": { "type": "string" } } ] }}, "/emv3dsecure/v1/stepup": { "post": { "summary": "EMV 3D Secure StepUp API", "parameters": [ { "name": "ProfileId", "in": "query", "description": "ProfileId for Cors Authentication", "required": true, "style": "form", "explode": true, "schema": { "type": "string" } } ] }}, "/emv3dsecure/v1/merchantreturnurl": { "post": { "parameters": [ { "name": "TransactionId", "in": "query", "description": "Transaction Id included in Step Up JWT", "required": true, "style": "form", "explode": true, "schema": { "type": "string" } }] }} } });
SELECT m.*
FROM default AS m
WHERE ANY v WITHIN m.paths SATISFIES IS_ARRAY(v.parameters) AND (ANY p IN v.parameters SATISFIES p.name = "TransactionId" END) END;
SELECT ARRAY_FLATTEN(ARRAY v.parameters FOR v WITHIN m.paths WHEN v.parameters IS NOT NULL END, 1) AS parametrs
FROM default AS m
WHERE ANY v WITHIN m.paths SATISFIES IS_ARRAY(v.parameters) AND (ANY p IN v.parameters SATISFIES p.name = "TransactionId" END) END;
Above query searching any where in the document, It required primary index unless if there are are any other predicates you can create index on those predicates.
