“Where” clause inside array not working

SQL++
1

Hello All,

I am new in CB DB and trying to query below array Please help.

Below are my CB DB bucket format

{
    "Lang" : "en_US",
        "DocType" : "User Setting Document",
        "Role" : [{
            "RoleID" : 1,
            "Name" : "FO Emp",
            "Params" : {
                "PassMin" : 8,
                "PassMax" : 12,
                "PassUCase" : 1,
                "PassLCase" : 1,
                "PassNum" : 1
            }
        },
        {
            "RoleID" : 2,
            "Name" : "BO Emp",
            "Params" : {
                "PassMin" : 8,
                "PassMax" : 12,
                "PassUCase" : 1,
                "PassLCase" : 1,
                "PassNum" : 1
            }
        }
    ]
}

I have to get the record of RoleID =1. I have tried many ways but it is not working. Please suggest the best way to get the result with RoleID wise.

2

It would be good to know what queries you tried as this will allow us to better understand what you are trying to do.

This query should work:

SELECT * FROM default  UNNEST `Role` r  WHERE r.`RoleID` == 1;

Note that ROLE is a reserved word and therefore the back-ticks are important.

1 Like
3

If you want whole document when array contains RoleID =1 document.

SELECT d.* 
FROM default AS d 
WHERE d.DocType = "User Setting Document" AND ANY r IN d,`Role`  SATISFIES r.RoleID = 1 END;

If you want individual objects when array contains RoleID =1 , You can use @pvarley example.

SELECT r.*
FROM default AS d 
UNNEST d.`Role` AS r
WHERE d.DocType = "User Setting Document" AND r.RoleID = 1 ;

https://blog.couchbase.com/working-json-arrays-n1ql/

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

2 Likes