Help for N1QL query

SQL++
1

I have 2 document type user and session

| Type | User |
|------|------|
| id   | 1    |
| name | Jack |

| Type | User    |
|------|---------|
| id   | 2       |
| name | William |

| Type   | Session |
|--------|---------|
| id     | 10      |
| userId | 1       |
| foo    | bar     |

| Type   | Session |
|--------|---------|
| id     | 11      |
| userId | 1       |
| foo    | baz     |

| Type   | Session |
|--------|---------|
| id     | 12      |
| userId | 2       |
| foo    | foobar  |

I want to select all session id and user’s name and foo , But I want remove duplicate userIds in sessions , I want to get on of the following results


| sessionId | name    | foo    |
|-----------|---------|--------|
| 10        | Jack    | bar    |
| 12        | William | foobar |


| sessionId | name    | foo    |
|-----------|---------|--------|
| 11        | Jack    | baz    |
| 12        | William | foobar |

As you can see , session id 10 and 11 is for user id 1 , I want to keep one of them in result

2 
CREATE INDEX ix1 ON default(id) WHERE type = "User";


SELECT u.name, ag[0].*
FROM default AS s
JOIN default AS u ON s.userId = u.id
WHERE s.type = "Session" AND u.type = "User"
GROUP BY u.name
LETTING ag = ARRAY_AGG({s.sessionId, s.foo});

You can project ag will give all the values.

SELECT u.name, MIN({s.sessionId, s.foo}).*
FROM default AS s
JOIN default AS u ON s.userId = u.id
WHERE s.type = "Session" AND u.type = "User"
GROUP BY u.name;

 You can use MAX also.


SELECT u.name, s.sessionId, s.foo
FROM (SELECT RAW MIN(s)
      FROM default AS s
      WHERE s.type = "Session"
      GROUP BY s.userId) AS s
JOIN default AS u ON s.userId = u.id
WHERE u.type = "User";
1 Like
3

@vsr1 Is it possible that I set an alias to foo in MIN({s.sessionId, s.foo}).* statement? foo as bar

4

You can use something like this, but you’ll get bar as an array of objects.

SELECT (SELECT MIN({s.sessionId, s.foo}).*) as bar FROM s

5

In SELECT MIN , I want to select MIN based of sessionId so If I set alias for it as _sessionId it works

6

MIN({s.sessionId, “bar”:s.foo}).*

If you want min based on sessionId value only

MIN([s.sessionId, {s.sessionId, s.foo}])[1].*

1 Like