Question: multilevel subquery nest

SQL++
1

hi:
I have four document, mark with red box:

image
image1093×807 77.7 KB

RoleGroup JION Admin and WechatUser,
the N1QL statements as follow:

SELECT roles.name,
ARRAY { a.name,
        a.mobile,
        a.username,
        a.isSuper,
        (select dep.name FROM admins JOIN guoxue dep on keys admins.departmentId) as dep,
        a.createdTime} FOR a IN adm END as admins,
ARRAY { meta(wechat).id,
        b.nikename,
        b.openId,
        b.joinMethod,
        b.groupName,
        b.member,
        b.valid,
        b.createdTime} FOR b IN wechat END as wechatUsers
FROM guoxue roles 
NEST guoxue adm ON KEYS roles.userids
NEST guoxue wechat ON KEYS roles.wechatUserIds

the result is:

> [
>   {
>     "admins": [
>       {
>         "createdTime": 1534333674040,
>         "departmentId": "Department::1534333631008::japq",
>         "isSuper": true,
>         "mobile": "139188888888",
>         "name": "test Admin",
>         "username": "test Admin"
>       }
>     ],
>     "name": "测试权限组",
>     "wechatUsers": [
>       {
>         "createdTime": 1534775772894,
>         "groupName": "天天开心",
>         "joinMethod": "扫码",
>         "member": {
>           "createdTime": 1534776128127,
>           "mobile": "13988888888888",
>           "name": "十三姨",
>           "recharges": [
>             {
>               "createdTime": 1534776184645,
>               "recharge": 500
>             },
>             {
>               "createdTime": 1534778192057,
>               "recharge": 500
>             },
>             {
>               "createdTime": 1534820204118,
>               "recharge": 800
>             }
>           ]
>         },
>         "nikename": "神奇四侠",
>         "openId": "你看过吗？",
>         "valid": true
>       }
>     ]
>   }
> ]

now I want JOIN Department with Admin.departmentId base on above statements, how can I make it?

thanks!

2

Are u want to replace departmentId in admins with name?

Try following query.

SELECT roles.name,
        (SELECT adm.name, adm.mobile, adm.username, adm.isSuper, adm.createdTime,
                (SELECT RAW dep.name FROM guoxue dep USE KEYS adm.departmentId)[0] AS dep,
         FORM guoxue AS adm USE KEYS roles.userids) AS admins,
        (SELECT META(wechat).id, wechat.nikename, wechat.openId, wechat.joinMethod, wechat.groupName,
                wechat.member, wechat.valid, wechat.createdTime
         FORM guoxue AS wechat USE KEYS roles.wechatUserIds) AS wechatUsers
FROM guoxue roles
WHERE .....;

OR

SELECT roles.name, admins, wechatUsers
FROM guoxue roles
LET admins = (SELECT adm.name, adm.mobile, adm.username, adm.isSuper, adm.createdTime, deps[0] AS dep,
              FORM guoxue AS adm USE KEYS roles.userids
              LET deps = (SELECT RAW dep.name FROM guoxue dep USE KEYS adm.departmentId) ),
    wechatUsers = (SELECT META(wechat).id, wechat.nikename, wechat.openId, wechat.joinMethod, wechat.groupName,
                          wechat.member, wechat.valid, wechat.createdTime
                   FORM guoxue AS wechat USE KEYS roles.wechatUserIds)
WHERE .....;
1 Like
3

thank you! this work for me.

1 Like