JOIN one document with another by nested keys

SQL++
,
1

Hi, I have a document with type ‘user’ which have an id. In another document which is of type ‘log’ have that user’s id in a format like this -

“push”: {

“failure”: 1,
“success”: 2,
“done”: 3,
“stats”: [
{

    "someId": "fzfkq8yIqT0",
    "error": "0",
   "memberId": "sdyg5345634y54yhdgfhdfsgregh"

  },
  {

    "someId": "3456joi",
    "error": "1",
    "memberId": "978945934832cf2a7d4a518fcd63"

  },
  {

    "someId": "regsdfg34534",
    "error": "0",
    "memberId": "sdyr57e5rygehjfd546456785678"

  }
],

},

I need to get data from these two documents. I need to join user.id with push.stats[*].memberId. What is the way to achieve this since * isn’t the way?

2

sample data:

insert into default  (key, value) values ("log_123", {
"type":"log",
"push": {
"failure": 1,
"success": 2,
"done": 3,
"stats": [
{

    "someId": "fzfkq8yIqT0",
    "error": "0",
   "memberId": "sdyg5345634y54yhdgfhdfsgregh"
  },
  {
    "someId": "3456joi",
    "error": "1",
    "memberId": "978945934832cf2a7d4a518fcd63"
  },
  {
    "someId": "regsdfg34534",
    "error": "0",
    "memberId": "sdyr57e5rygehjfd546456785678"
  }
]
}  
}), 
("sdyg5345634y54yhdgfhdfsgregh", {
"type": "user",
"name": "user_1"
}), 
("978945934832cf2a7d4a518fcd63", {
"type": "user",
"name": "user_2"
}), 
("sdyr57e5rygehjfd546456785678", {
"type": "user",
"name": "user_3"
});

N1QL:

SELECT l.*,u.* 
  FROM default l JOIN default u ON KEYS l.push.stats[*].memberId
 WHERE l.type == "log"
3

This doesn’t work for me, I don’t know why.

4

Post the query that is not working with sample user document and corresponding document key

5

Sorry for the late reply. This is my query -

SELECT push.memberId, u.email, u.first_name, u.last_name, u.phone, push.message_id msgId
FROM default ml UNNEST ml.push.status push
LEFT JOIN default u ON KEYS ml.push.status[*].memberId
WHERE ml.type = ‘message_log’
AND ml.src_type = ‘0’
AND ml.src_id = ‘040ecddbf2319a3b5183347cd9f1ff5b’

And my user document looks like this-

{

“account_active_type”: 2,
“created_at”: “2017-05-10T05:08:47”,
“created_by”: “bce541f098561c019676059e06127fe8”,
“email”: “email1@mail.com”,
“first_name”: “Jewel”,
“id”: “8b640a13b41dac3a35d898f739702339”,
“last_name”: “Siam”,
“passkey”: null,
“phone”: “+8800000000”,
“type”: “user”,
“updated_at”: “2017-05-10T05:08:47”,
“user_type”: 4
}

I’ve got the solution now, am not sure if its good or bad. I’ve used this following query-

SELECT push.memberId, u.email, u.first_name, u.last_name, u.phone, push.message_id pushStatus
FROM default ml UNNEST ml.push.status push
LEFT JOIN default u ON KEYS ‘user::’ || push.memberId
WHERE ml.type = ‘message_log’
AND (ml.src_type = ‘0’ OR ml.src_type = 0)
AND ml.src_id = ‘040ecddbf2319a3b5183347cd9f1ff5b’

I did the UNNEST and then I did the join with inner key.

thanks!

6

The query looks fine.

7

Then I’ll stick to the second version of my query. Thanks a lot for the help :slight_smile: