Simple JOINS with Couchbase

SQL++
1

I am trying to figure out how to perform a simple JOIN between two buckets. The first one contains songs. They have the field artist, which contains the id of each artist. The second on is a simple list of artists.

    [
    	{
    		"songs": {
          	"artist": 1,
          	"title": "Title 1"
          	}
      	},
      	{
      		"songs": {
      		"artist": 2,
      		"title": "Title 2"
      		}
    	},
    	{
    		"songs": {
    		"artist": 4,
    		"title": "Title 3"
    		}
    	}
    ]

    [
      {
        "artist": {
          "name": "Artist 1"
        }
      },
      {
        "artist": {
          "name": "Artist 2"
        }
      },
      {
        "artist": {
          "name": "Artist 3"
        }
      },
      {
        "artist": {
          "name": "Artist 4"
        }
      },
      {
        "artist": {
          "name": "Artist 5"
        }
      }
    ]

I tried following:

SELECT a.title, b.name
FROM songs a
JOIN artists b
ON KEY b.id = a.artist

I read that it must be done with one id, but how can I tell it to use the field artist on songs with the artist id?

Second. This was just a simple case. There is also the case on which a song can contain many artists. The bucket would look like this

[
	{
		"songs": {
      	"artists": [1,3],
      	"title": "Title 1"
      	}
  	},
  	{
  		"songs": {
  		"artists": [2,3],
  		"title": "Title 2"
  		}
	},
	{
		"songs": {
		"artists": [1,4],
		"title": "Title 3"
		}
	}
]

How would a query look like, when I need to display all songs with the name of each artist involved in that song?

2

In the current version of N1QL, the JOIN can only be on record id. You need to design your data so that each song contains the record key of the artists who performed it.

SELECT s.title, a.name
FROM songs s
JOIN artists a
ON KEY s.artist_ids

The field used in ON KEY can be either a single id or an array of ids.

More sophisticated joins are possible, but not in 5.0.

1 Like