N1QL paths for deeply nested data

[iwanna_ster]

iwanna_ster

1m

I have a problem with some data that i have to skip in N1QL paths. For example

> "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"
>             }
>           }

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

[vsr1]

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;

[vsr1]

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;

[iwanna_ster]

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

[vsr1]

post the exact query you tried.

SELECT ARRAY v FOR v WITHIN m WHEN v.parameters IS NOT NULL END AS parametrs
FROM mybucket AS m
WHERE …

[iwanna_ster]

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

[vsr1]

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;

[iwanna_ster]

@vsr1

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;

I am getting this message all the time and i have created all the recommended indexes

[vsr1]

@iwanna_ster,

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.