Joining items in two arrays into one result

SQL++
1

Hey everyone,

currently I’m struggling with understand the possibilities but also restrictions of N1QL.

The case I don’t see a solution for is the following. I’ve two arrays in two different documents like this:

Doc with ID “features:12345”:

{
	"features": [
		{
			"id": 1,
			"name": "feature1"
		},
		{
			"id": 2,
			"name": "feature2"
		}
	]
}

Doc with ID “attributes:12345”

{
	"attributes": [
		{
			"id": 1,
			"value": "value describing feature1"
		},
		{
			"id": 2,
			"value": "value describing feature2"
		}
	]
}

Now I want to join/merge these two arrays into one result, so that ever feature is joined with it’s corresponding attribute. The result should look like:

[
	{
		"id": 1,
		"name": "feature1",
		"value": "value describing feature1"
	},
	{
		"id": 2,
		"name": "feature2",
		"value": "value describing feature2"
	}
]

I’ve seen and learn several ways to join a array with other documents (one document for each item of the attribute), but not joining two arrays which have corresponding items.

Is that even possible?

Regards,
Michael

2 
SELECT f.id, f.id, a.`value`
FROM default AS d USE KEYS "features:12345"
UNNEST d.features AS f
UNNEST ARRAY_FLATTEN ((SELECT RAW a.attributes FROM default AS a USE KEYS "attributes:12345"),2) AS a
WHERE f.id = a.id;

OR

SELECT ARRAY (FIRST {fv.id, fv.name, av.`value`} FOR av IN a WHEN a.id = fv.id END) fv FOR fv IN f.features END  AS features
FROM default AS d USE KEYS "features:12345"
LET a = ARRAY_FLATTEN ((SELECT RAW a.attributes FROM default AS a USE KEYS "attributes:12345"),2);
3

Hey vsr1,

thank you very much, I picked the first approach and it works fine and solves my problem on the “is possible to do” level!

Now my follow up question is: is this kind of query expected to be very slow because of the kind of operations it has to perform?

The two arrays I’m joining for example have a length of 170 and 380 items. The query needs avg. 3.2 seconds to execute. Is that expected (from your point of experience with Couchbase and N1QL), or is that unusual long execution time?

Also the execution time varies from 2.1 seconds up to 3.6 seconds. My testing instance of Couchbase has no other operations to perform, so I’m a little surprised about this great difference in execution time for the exact same query on the exact same data. Is there a possible explanation for this too?

Regards,
Michael

4

Try this and see any better. It uses Nested Loop and also N1QL uses golang and GC might causing variation in time.

SELECT f.id, f.id, a.`value`
FROM  ARRAY_FLATTEN ((SELECT RAW f.features FROM default AS f USE KEYS "features:12345"),2) AS f 
UNNEST ARRAY_FLATTEN ((SELECT RAW a.attributes FROM default AS a USE KEYS "attributes:12345"),2) AS a
WHERE f.id = a.id;