Multi-Level NEST Statements

SQL++
1

I’m working on helping a user out with doing some NEST statements via LINQ, issue here:

In simple terms, they have a 3-tier document hierarchy, and want to use a single query to nest documents from the 3rd tier into the 2nd tier, which are then nested in the 1st tier.

In trying to figure out how to make the LINQ statement work, I started with trying to make the N1QL statement work. So far, I’ve been unable to find a route to perform multiple NEST statements where the second statement is nesting within the documents returned by the first NEST statement. I can only perform more NEST statements into the root document.

I have been able to emulate the behavior using sub-queries. However, I’m not sure of the performance impact compared to NEST statements. Also, NEST statements are much cleaner if that’s an option.

I just wanted to check and make sure I’m not missing something here.

Thanks,
Brant

1 Like
2

Hi Brant,

You would have to use subqueries in the projection. The following should work.

SELECT comp.*,
    (SELECT cust.*,
        (SELECT order.* FROM order WHERE ... ) AS orders
      FROM customer AS cust WHERE ... ) AS customers
FROM company AS comp WHERE ... ;
3

Hi,

I really appreciate the interest from both of you helping us resolve this issue.

I see the solution is creating sub-queries, which is fine, but I think that Linq2Couchbase does not support them yet. Maybe I am wrong…

Following the comments on the NEST keyword, I found on the documentation a comment where several NESTs could be chained, but no practical examples on how this could be done.

My question would be… What is the purpose of chained NESTs ? They don’t seem to do what one would expect. How are they supposed to work ?

Please remember that we a new to Couchbase and more than likely I am wrong with my tests… :wink:

TIA,
David

4

Hi David, as we speak we are enhancing the N1QL documentation and adding more examples (for example, you can see INSERT, and some of SELECT pages). Will make sure to add more examples chaining multiple NESTs etc.,

simply put, NEST helps you collect required/matching data from multiple chained/related-documents (with PK-FK), and organizes that in a single result-object. Let me know specifics of what you are expecting… For a example:

insert into default  (key, value) values ("c1", {"name" : "c1", "orders" : ["o11", "o12"]}), 
("c2", {"name" : "c2", "orders" : ["o21", "o22"]});

insert into default  (key, value) values ("o11", {"oname" : "o11", "oitems" : ["o11_i1", "o11_i2"]}), 
("o12", {“oname" : "o12", "oitems" : ["o12_i3"]}), 
("o22", {“oname" : "o22", "oitems" : ["o22_i4"]});

insert into default  (key, value) values ("o11_i1", {"oiname" : "o11_item1"}), 
("o11_i2", {"oiname" : "o11_item2"}), 
("o12_i3", {"oiname" : "o12_item3"}), 
("o22_i4", {"oiname" : "o22_item4"});

Following query gives all customers and the items they ordered:

SELECT cust.name AS cust_name, ARRAY i.oiname FOR i IN nested_items END AS cust_items
FROM default cust
     NEST default nested_orders ON KEYS cust.orders
          NEST default nested_items ON KEYS array_flatten(ARRAY x.oitems FOR x IN nested_orders END, 2);
[
  {
    "cust_items": [
      "o12_item3",
      "o11_item1",
      "o11_item2"
    ],
    "custname": "c1"
  },
  {
    "cust_items": [
      "o22_item4"
    ],
    "custname": "c2"
  }
]

hth,
-Prasad

1 Like