Join of data in same bucket...?

SQL++
1

Hi

I am trying to move an existing system over to Couchbase - and the data is structured in a traditional relational manner right now. I am making some changes but on the other hand try to limit the number of data changes to avoid too many side effects :innocent:

So I have these two different types of documents CoastArea and a number of CoastLocalArea underneath the areas. All data is in the same bucket - just a different _Type. How can I join the CoastArea.Name into the list of CoastLocalArea documents?

Ideally, I want to JOIN the data to add an AreaName to the CoastLocalArea in the resultset.

Sample data:

  {
    "data": {
      "Name": "Fanø",
      "_Key": "179",
      "_Type": "CoastArea",
      "_Unid": "012389FEDDFE3E82C12582A4002F714F"
    }
  },
  {
    "data": {
      "AreaKey": "179",
      "Name": "Rindby Strand",
      "Points": [
        {
          "Lat": 55.4219856355,
          "Lon": 8.3677502154
        },
        {
          "Lat": 55.3959022179,
          "Lon": 8.3928679228
        },
        {
          "Lat": 55.4194003476,
          "Lon": 8.3704811983
        }
      ],
      "_Key": "1787",
      "_Type": "CoastLocalArea",
      "_Unid": "0216519C9A524EF5C12582A4002F715D"
    }
  },
  {
    "data": {
      "AreaKey": "179",
      "Name": "Skideneng",
      "Points": [
        {
          "Lat": 55.4275494328,
          "Lon": 8.4144364255
        },
        {
          "Lat": 55.4261875559,
          "Lon": 8.4183043138
        }
      ],
      "_Key": "1808",
      "_Type": "CoastLocalArea",
      "_Unid": "0BD53EAF6DB345B7C12582A4002F719C"
    }
  }, ...

All of the examples I have seen on joins use the FROM clause to specify the two entities - as coming from different buckets…

The sample data above is returned from this query:

SELECT *
FROM data
WHERE (_Type ="CoastLocalArea" and AreaKey = "179") or (_Type ="CoastArea" and _Key = "179")

Any suggestions appreciated :wink:

/John

2

In 5.5 you can use ANSI JOINS https://blog.couchbase.com/ansi-join-support-n1ql/
Pre 5.5 you need to have relation ship through document key

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/from.html

3

Hi

Thanks for your reply. I’m sorry I didn’t mention I’m on latest community edition (i.e. 5.0).

I have the key in my CoastLocalArea (the AreaKey) that corresponds to the _Key in the CoastArea type (the “parent”). I have tried various syntax “guess”-s - but none have given me what I’m looking for. I have something like this:

SELECT coast.*,area.Name as AreaName
FROM data coast join data area on keys coast.AreaKey
WHERE (coast._Type ="CoastLocalArea" and area._Type = "CoastArea" and coast.AreaKey = "126")

But I see that I have not specified the relation between coast.AreaKey and area._Key - and I don’t know how to do that…

For this to work on the current version of Couchbase server that I am using will I then need the id of the document to be what I have in the _Key attribute? If so, then I suppose I could still create a unique id by prefixing the _Key value with the _Type value…

Nothing set in concrete yet - as I am trying to solve the challenges I see coming prior to moving everything over :wink:

4

Do you have relation ship between document key and field of the document. Yes you can do that,

5

Hmmm… so far I have the _Unid field as the id of the document - if that was what you mean?

6 
INSERT INTO default VALUES ("CoastArea179",{ "Name": "Fanø", "_Type": "CoastArea", "_Unid": "012389FEDDFE3E82C12582A4002F714F" }),
                            ("CoastLocalArea01", {"AreaKey": "179", "Name": "Rindby Strand", "Points": [ { "Lat": 55.4219856355, "Lon": 8.3677502154 }, { "Lat": 55.3959022179, "Lon": 8.3928679228 }, { "Lat": 55.4194003476, "Lon": 8.3704811983 } ], "_Key": "1787", "_Type": "CoastLocalArea", "_Unid": "0216519C9A524EF5C12582A4002F715D" });

SELECT cl, c
FROM default AS c
JOIN default AS cl ON KEYS "CoastArea"||c.AreaKey
WHERE c._Type = "CoastLocalArea" AND cl._Type = "CoastArea";

In above Example _Type = “CoastArea” document key is “CoastArea179” and Type = “CoastLocalArea” document key is “CoastLocalArea01”
There is relation ship between two i.e “CoastArea”||c.AreaKey can matched document key of _Type = “CoastArea”

In SQL term ON KEYS “CoastArea”||c.AreaKey is same as ON “CoastArea”||c.AreaKey = META(cl).id

https://blog.couchbase.com/ansi-join-support-n1ql/ Look example 15 for explanation

Also checkout https://dzone.com/articles/join-faster-with-couchbase-index-joins

While inserting “CoastArea” document make document key as Prefix “CoastArea” to _Key and make it as document key then you can Join

In Pre 5.5 the Joins are based on field in the one side of document and document key on otherside. In 5.5 You can join between through fields of the documents

7

Vauw! This is great. By adjusting a little bit in your example I think I can get this working (using an id of CoastArea: and _Key, e.g. CoastArea:126) and using a query like this:

SELECT cl.Name as AreaName, c.*
FROM data AS c
JOIN data AS cl ON KEYS "CoastArea:"||c.AreaKey
WHERE c._Type = "CoastLocalArea" AND cl._Type = "CoastArea";

It results in something like this.

  {
    "AreaKey": "126",
    "AreaName": "Anholt",
    "Name": "Ørehage",
    "Points": [
      {
        "Lat": 56.6870130165,
        "Lon": 11.5501791836
      },
      {
        "Lat": 56.6870141895,
        "Lon": 11.5503210122
      }
    ],
    "_Key": "2604",
    "_Type": "CoastLocalArea",
    "_Unid": "0C741E8DD1DDFFB0C12582A4002F60D5"
  },
  {
    "AreaKey": "126",
    "AreaName": "Anholt",
    "Name": "Pikkehusknold",
    "Points": [
      {
        "Lat": 56.722690876,
        "Lon": 11.5595383169
      },
      {
        "Lat": 56.7231175605,
        "Lon": 11.5693574928
      }
    ],
    "_Key": "2615",
    "_Type": "CoastLocalArea",
    "_Unid": "1445C8940359B21AC12582A4002F60F8"
  },

And that was what I was trying to obtain. So I need to redefine the ids of my CoastArea documents for this to work!

Thanks!