SELECT F.properties.mpObjectId as bBlockId, C_BB.bBlockNo, C_COU.descript
FROM OBI_MPWEB AS B
UNNEST B.bimJson.featureCollection.features AS F
INNER JOIN OBI_MPWEB_CACHE AS C_BB ON F.properties.mpObjectId = meta(C_BB).id
LEFT OUTER JOIN OBI_MPWEB_CACHE AS C_COU ON C_BB.countryId = meta(C_COU).id
WHERE B._type = "market::bim" AND B.marketId = "b05282c6-c3d3-4bdd-a91f-a19d34e21be2" AND F.properties.featureType = "Component"
But I don’t want the first join to be a INNER JOIN, because the desired RIGHT side values might not be cached, but the LEFT side of the join should always be returned (exact the way this works in MSSQL, give me what you have, regardless of JOIN success).
So I tried the obvious thing
SELECT F.properties.mpObjectId as bBlockId, C_BB.bBlockNo, C_COU.descript
FROM OBI_MPWEB AS B
UNNEST B.bimJson.featureCollection.features AS F
LEFT OUTER JOIN OBI_MPWEB_CACHE AS C_BB ON F.properties.mpObjectId = meta(C_BB).id
LEFT OUTER JOIN OBI_MPWEB_CACHE AS C_COU ON C_BB.countryId = meta(C_COU).id
WHERE B._type = "market::bim" AND B.marketId = "b05282c6-c3d3-4bdd-a91f-a19d34e21be2" AND F.properties.featureType = "Component"
Did you check results. You are doing LEFT OUTER JOIN. If there is no matching entry for right side it is projected as MISSING, Any expression on MISSING result in MISING that is why you don’t have fields bBlockNo, descript
But the entries for the join match are there, and they show up when doing a Inner Join. They just don’t show up when doing it with a Left Outer Join. And that’s the point. Right side values should be there but are not shown. So the Left Outer Join isn’t working correctly or not as expected for a ANSI Join.
INSERT INTO default VALUES ("f01", {"_type":"market::bim", "marketId":"b05282c6-c3d3-4bdd-a91f-a19d34e21be2",
"bimJson":{"featureCollection":{"features": [{"properties":{"mpObjectId":"ea93e4c4-95bf-494b-bbc7-e469bb9d5de1","featureType":"Component"}},
{"properties":{"mpObjectId":"42dcfa98-2f0f-49fd-9a21-4195627110f3","featureType":"Component"}},
{"properties":{"mpObjectId":"08d49fee-0311-43bb-8870-fff1a08404c2","featureType":"Component"}},
{"properties":{"mpObjectId":"ba21bf18-06e8-43d1-b7b3-62a8f3b8cbad","featureType":"Component"}}
]}}});
INSERT INTO default VALUES ("ea93e4c4-95bf-494b-bbc7-e469bb9d5de1", {"countryId":"c100","bBlockNo":"44A500"});
INSERT INTO default VALUES ("42dcfa98-2f0f-49fd-9a21-4195627110f3", {"countryId":"c100","bBlockNo":"44D405"});
INSERT INTO default VALUES ("ba21bf18-06e8-43d1-b7b3-62a8f3b8cbad", {"countryId":"c200","bBlockNo":"44F600"});
INSERT INTO default VALUES ("c100", {"descript":"Ungarn"});
SELECT F.properties.mpObjectId as bBlockId, C_BB.bBlockNo, C_COU.descript
FROM default AS B
UNNEST B.bimJson.featureCollection.features AS F
LEFT OUTER JOIN default AS C_BB ON F.properties.mpObjectId = meta(C_BB).id
LEFT OUTER JOIN default AS C_COU ON C_BB.countryId = meta(C_COU).id
WHERE B._type = "market::bim" AND B.marketId = "b05282c6-c3d3-4bdd-a91f-a19d34e21be2" AND F.properties.featureType = "Component";
"results": [
{
"bBlockId": "ea93e4c4-95bf-494b-bbc7-e469bb9d5de1",
"bBlockNo": "44A500",
"descript": "Ungarn"
},
{
"bBlockId": "42dcfa98-2f0f-49fd-9a21-4195627110f3",
"bBlockNo": "44D405",
"descript": "Ungarn"
},
{
"bBlockId": "08d49fee-0311-43bb-8870-fff1a08404c2"
},
{
"bBlockId": "ba21bf18-06e8-43d1-b7b3-62a8f3b8cbad",
"bBlockNo": "44F600"
}
]
Could you post EXPLAIN and corresponding indexes. Also If you can post sample document (only referenced fields) of OBI_MPWEB AS B and corresponding matching documents. What is CB version.
It turned you this isn’t a reproducable problem, and now most times it works. I’m really not sure what was wrong, better what I did wrong. I guess it was a combination of several sideeffects.
For now I’d “close” this topic. If I’ll again discover this issue, I might come back here to reopen it.