Create new array from nesting docs

SQL++
,
1

I am trying to attach doc into all object of array as following and i am using CB4.1

{
  "_type": "AsgList",
  "assignedUsers": [
    {
      "reason": "Assigned By Prof. Batra",
      "userId": "B1PHCzHn57d79e3f"
    },
    {
      "reason": "Recommened by ES.Ratnakar",
      "userId": "S1XeYBh57d8006b"
    },
    {
      "reason": "Compulsory assignment",
      "userId": "ByC8ltrh57d80055"
    }
  ],
  "id": "TA112",
  "title": "New Physics Lab Work"
}

and this is user Doc

{
          "_type": "User",
          "email": "rk@nauni.edu",
          "role": "STUDENT",
          "userName": "Raul Poelo"
          .
          .
          .
}

I am using this query for getting this result

select list.__type, list._id,userList from default list left nest default nestedUser on keys list.assignedUsers[*].userId let userList = array {“user”:u,“reason”:first child.reason for child in list.assignedUsers when child.userId = u.id} for u in nestedUser end where list. _type = “AsgList”;

and getting this :
[{“code”:3000,“msg”:“syntax error - at }”},{“original_query”:“select cbq_query_workbench_limit.* from (select list._type, list._id,userList from default list left nest default nestedUser on keys list.assignedUsers[*].userId let userList = array {"user":u,"reason":first child.reason for child in list.assignedUsers when child.userId = u._id} for u in nestedUser end where list._type = "AsgList") cbq_query_workbench_limit limit 1000;”}]

whereas i supposed that i will get something like following :slight_smile:

[
  {
    "_type": "AsgList",
    "id": "TA112",
    "title": "New Physics Lab Work"
    "userList": [
      {
        "reason": "Recommened by ES.Ratnakar",
        "user": {
          "_type": "User",
          "email": "rk@nauni.edu",
          "role": "STUDENT",
          "userName": "Raul Poelo"
          .
          .
          .
        }
      },
      {
        "reason": "Assigned By Prof. Batra",
        "user": "user": {
          "_type": "User",
          "email": "rk@nauni.edu",
          "role": "STUDENT",
          "userName": "Raul Poelo"
          .
          .
          .
        }
      },
      .
      .
      .
    ]
  }
]

If i am giving static value in reason field in array then getting proper result so i thing theres an error with first clause
So can anyone help me with this query
thanks in advance

2

Try this

SELECT list._type, list.title,list.id,ARRAY_AGG(OBJECT_REMOVE(OBJECT_ADD(assignedUser,"user",nestedUser[0]),"userId"))  AS userList
  FROM default list UNNEST list.assignedUsers AS assignedUser
LEFT NEST default nestedUser ON KEYS assignedUser.userId 
 WHERE list. _type = "AsgList"
 GROUP BY list._type, list.title,list.id;
4

using CB4.1 this won’t work thanks for help

5

How about this one

SELECT list._type, list.title,list.id,
       ARRAY_AGG({
            "reason":assignedUser.reason,
            "user":nestedUser[0]
       }) AS userList
  FROM default list UNNEST list.assignedUsers AS assignedUser
LEFT NEST default nestedUser ON KEYS assignedUser.userId 
 WHERE list. _type = "AsgList"
GROUP BY list._type, list.title,list.id;
6

Perhaps a simpler version.

SELECT
    list.title, list.id,
    ARRAY_AGG( { "reason":assignedUsers.reason, "user": assignedUser } ) AS userList
FROM default list
LEFT UNNEST list.assignedUsers
LEFT JOIN default assignedUser ON KEYS assignedUsers.userId
GROUP BY list;
1 Like
7

With JOIN there will be lost the reason data that not match userId.

1 Like
8

I changed it to LEFT UNNEST and LEFT JOIN.

9

yes, it works. and more simpler.

10

Yeah thanx @geraldss @atom_yang Both queries are working but i have little question here if i want to limit the result can i do that.I mean there are 500 entries in AsgList doc and if I run query on that doc its resonse time and output size will be around 150ms and 0.5 MB so can i limit the data to 20-30 user data here

11

@ashvinipatelap1 Already tried it and i ll implement it you can now go for limit query

12

add limit 20 to the end of N1QL can limit the result to 20.

13

@atom_yang @geraldss Limit will not work as result count will always be 1 of this query i want to limit the join data

[
  {
    "_type": "AsgList",
    "id": "TA112",
    "title": "New Physics Lab Work"
    "userList": [
      {
        "reason": "Recommened by ES.Ratnakar",
        "user": {
          "_type": "User",
          .
          .
          .
        }
      },
      {
        "reason": "Assigned By Prof. Batra",
        "user": "user": {
          "_type": "User",
          .
          .
          .
        }
      },
      .
      .
      .
    ]
  }
]

I want to limit userList array size basically want to implement paging on this is this possible?

14

try this

SELECT l.title,l.id,
       ARRAY_AGG(l.uList ) AS userList
FROM (
            SELECT
                list.title, list.id,
                { "reason":assignedUsers.reason, "user": assignedUser } as uList
            FROM default list
            LEFT UNNEST list.assignedUsers
            LEFT JOIN default assignedUser ON KEYS assignedUsers.userId
            WHERE list. _type = "AsgList"
            LIMIT 1 OFFSET 0
) AS l
GROUP BY l.title,l.id;
2 Likes
15

Hi @atom_yang, you are the man.