N1QL Limitation and DOC design Considerations

#1

I am in process of moving some DATA of our SQL back end and on to Couchbase. While doing so i came across a stiuation where i wonder about N1QL limitations. My Scenario is the following, i have Grids in my app which have columns. I allow each user to change order, name sort etc of the column in each grid, so when he comes back it is as he left it last time no matter which computer he uses. To do that each grid has a master table which stores a list of all columns of grid , its name position sort able etc. Then i have another table which stores for each user for each column his personal preferences. When i User opens grid i query for all column based on user and grid.
This is where it gets interesting, i am wondering if i could store all data per grid in a singe doc and give each columns in grid a unique id. Then have another doc for each user were i store personal preferences based on column id. Now the question is, can i go and match(join) the items in the grid doc with the user column doc ? or will i have to create a doc for each column in grid as well as preference in user. If i cant i might still consider the 2 Doc approach and load first the GridDoc and then the User Column doc and in my code merge the 2 based on guid of column.

#2

What Limitations you came across. If you need JOINs you can use https://blog.couchbase.com/ansi-join-support-n1ql/

#3

I am not saying i cam across a limitation yet, i was wondering if there was one which would not allow joining data based on Data in Array’s in Docs. here is what 2 sample docs would look like which i would like to merge based on a grid_id and userID

{
    	"id" :"D0842C42-D43F-426D-A12E-D0141FE58337",
    	"_type" : "grid_info",
    	
    	"columns" :[
    			{
    				"colId": "A3C031BA-C157-42D8-8208-9019B8B87061",
    				"headerName":"ID",
    				"field": "id",
    				"width" : 100,
    				"sortable": false				
    			},
    			{
    				"colId": "671B1BAA-2D2E-419D-9C74-182D9E87A502",
    				"headerName":"Name",
    				"field": "name",
    				"width" : 150,
    				"sortable": true				
    			},
    			{
    				"colId": "CCB26780-6474-4606-B9EA-750F80354599",
    				"headerName":"Zip Code",
    				"field": "zip",
    				"width" : 75,
    				"sortable": true				
    			}
    		
    		
    	]
    }


    {
    	"id" :"A40D39F1-0849-4C68-A506-6EC89DBD7163",
    	"_type" : "user_grid_info",
    	"grid_key" : "grid_info::D0842C42-D43F-426D-A12E-D0141FE58337",
    	"user_id" : "3D10D638-39E1-40CD-A3A5-2B0D9AB51F1F",
    	
    	
    	"columns" :[
    			{
    				"colId": "A3C031BA-C157-42D8-8208-9019B8B87061",
    				"hide" : true,
    				
    			},
    			{
    				"colId": "671B1BAA-2D2E-419D-9C74-182D9E87A502",
    				"position":1,

    				"width" : 200				
    			},
    			{
    				"colId": "CCB26780-6474-4606-B9EA-750F80354599",
    				"headerName":"Zip Code +4",
    				"position":2,
    				"width" : 100			
    			}
    		
    		
    	]
    }
#4

If you have in arrays want to JOIN you can UNNEST arrays and then do JOIN.

#5

Here is what i tried but it gives me the msg": “Ambiguous reference to field u.”, which i asume is due to fact that i unnest u.columns before it is declared in join. But i cnt go and put the UNNEST after that join either. So how would i go to take 2 docs with arrays, unnest and then join on its keys ?

select c, b.hide from mls as a
UNNEST a.columns as c
UNNEST u.columns as b
LEFT JOIN mls AS u
ON c.colId = b.colId
where a._id = "D0842C42-D43F-426D-A12E-D0141FE58337" and  a._type = "grid_info" and u.user_id = "3D10D638-39E1-40CD-A3A5-2B0D9AB51F1F" and  u._type = "user_grid_info"
#6
SELECT c, b.hide
FROM mls AS a
LEFT UNNEST (SELECT RAW b
        FROM mls AS d
        UNNEST d.columns AS b
        WHERE d.user_id = "3D10D638-39E1-40CD-A3A5-2B0D9AB51F1F" AND  d._type = "user_grid_info") AS b
UNNEST a.columns AS c
WHERE WHERE a._id = "D0842C42-D43F-426D-A12E-D0141FE58337" AND  a._type = "grid_info" AND  (b IS MISSING OR b.colId = c.colId);
#7

Thanks that works like a charm, just for records you have to where back to back in last row.
Let me ask you this, is it possible with in N1QL to do something like this on the Join.
Lets say the grid_info table has a value of sort : false for a column , but if the user_grid_info has also a value but in this case its tru i would want to return true.
So the logic i am looking for return doc a key value if none is present in doc b, if present in B return b value and if there is a key / value pair in doc B which is not present then return key and pair

#8

You can use CASE statement or IFMISSING() or IFMISSINGORNULL()

CASE WHEN a.sortable THEN a.sortable ELSE b.sortable END
IFMISSINGORNULL(b,META(a).id)
If that is not what you looking Please explain more details.

#9

Thanks that’s what i thought, i ended up doing 2 Query’s to get the general config and then with second one i get the user config if it exists, if it does i merge general with user config and return the merged doc, if there is no user config i just return the general config.