UNION between two types of documents, and join information on one of them

SQL++
,
1

Hello!

The objective is to get a list of user created groups and a list of his followers. In an usual query, I would do some customization using PHP, but if possible, I would like to achieve the most with N1ql, as it should be much more performant - I hope.

For that, I am trying to join two different types of documents, so I could get an output similar to the following (so it can be pageable):

[
  {
    "type": "group",
    "name": "Friends",
    "thumbnail": null
  },
  {
    "type": "group",
    "name": "Colleagues",
    "thumbnail": null
  },
  {
    "id": 1,
    "type": "person",
    "name": "Anibal Rodrigues"
  },
  {
    "id": 2,
    "type": "person",
    "name": "John Perez"
  },
  {
    "id": 3,
    "type": "person",
    "name": "Richard Daemon"
  }
]

Having documents like:
user_account::1

{
  "_id": 1,
  "_type": "user_account",
  "name": "Pedro Rodrigues",
  "settings": {
    "groups": {
      "Friends": [
        "user_account::5",
        "user_account::8"
      ],
      "Colleagues": [
        "user_account::16",
        "user_account::35"
      ]
    }
  }
}

And user_account::1::followers:

{
  "_id": 1,
  "_type": "user_account_followers",
  "followers": {
    "user_account::17": {
      "extra_attribute": "xxx",
      "created_at": 1474989380
    },
    "user_account::57": {
      "extra_attribute": "xxx",
      "created_at": 1474989380
    }
  }
}

Currently, I am just trying to build a single list of values as a first-step. For that I am using the following query:

SELECT OBJECT_NAMES(default.settings.groups) AS results FROM default
USE KEYS "user_account::1"
UNION ALL (
    SELECT OBJECT_NAMES(followers) AS results FROM default
    USE KEYS "user_account::1::followers"
)

However, the output instead of joining both results into one with UNION, is separating them into different collections.

In an usual situation I would do a GET for each value, however I guess the query would be much more optimized doing the joins directly with USE KEYS possibly.

Any ideas on what would be the best approach here?

Thanks!

2

Can you post the output.

3

@geraldss the output on my current query?

If so, here it goes:

[
  {
    "results": [
      "user_account::17",
      "user_account::57"
    ]
  },
  {
    "results": [
      "Friends",
      "Colleagues"
    ]
  }
]

Thanks!

4

OBJECT_NAMES() returns an array for each result. So you can use UNNEST.

SELECT name
FROM default AS d
UNNEST OBJECT_NAMES(d.settings.groups) AS name
USE KEYS "user_account::1"
UNION ALL
SELECT name
FROM default AS d
UNNEST OBJECT_NAMES(d.followers) AS name
USE KEYS "user_account::1::followers";
1 Like
5

Thanks a lot @geraldss! This really puts me on the right track!

Tomorrow I will keep up solving the query :slight_smile:

1 Like