N1QL

1 bucket with multiple objects

We will create a bucket with the multiple objects shown below.

MYB1:

{
  "k": [
    {
      "a": 1,
      "b": [
        {
          "c": 2,
          "d": 3
        }
      ]
    },
    {
      "a": 4,
      "b": [
        {
          "c": 5,
          "d": 6
        }
      ]
    }
  ]
},
{
  "k": [
    {
      "a": 7,
      "b": [
        {
          "c": 8,
          "d": 9
        }
      ]
    },
    {
      "a": 10,
      "b": [
        {
          "c": 11,
          "d": 12
        }
      ]
    }
  ]
},
{
  "k": [
    {
      "a": 13,
      "b": [
        {
          "c": 14,
          "d": 15
        }
      ]
    },
    {
      "a": 16,
      "b": [
        {
          "c": 17,
          "d": 18
        }
      ]
    }
  ]
}
To do this, upload the three my_bucket files to a new bucket in couchbase. Upload one file after another.

Queries:
1. select * from my_bucket;

2. select x from my_bucket x;

--result table

Result(x)
x
___
{k: [...]}
{k: [...]}
{k: [...]}

3. select x.k from my_bucket x;

--result table

Result(k)
k
___
[...]
[...]
[...]

4. select x.k.a from my_bucket x;

--won't work; the dot implies that a must be a key directly inside k. A list is inside k, not a key.

5. select y.a from my_bucket x unnest x.k y;

--result table

Result(a)
a
___
#
#
#
#
#
#

6. select y.a, y.b from my_bucket x unnest x.k y;

--result table

Result(a,b)
a  |  b
___  ___
#    #
#    #
#    #
#    #
#    #
#    #

In general, for queries that look like

SELECT *
FROM my_bucket x UNNEST x.? y UNNEST y.? z UNNEST z.? u

the resulting table will be

x | y | z | u
_____________
x1 | y1 | z1 | u1
x1 | y1 | z1 | u2
x1 | y1 | z2 | u1
x1 | y1 | z2 | u2

For this query, where we have two unnestings with z.?

SELECT *
FROM my_bucket x UNNEST x.? y UNNEST y.? z UNNEST z.? u UNNEST z.? v

The table will do a mini-cross product on u and v.

x | y | z | u | v
______________________
x1 | y1 | z1 | u1 | v1
x1 | y1 | z1 | u2 | v1
x1 | y1 | z2 | u1 | v1
x1 | y1 | z2 | u2 | v1
x1 | y1 | z1 | u1 | v2
x1 | y1 | z1 | u2 | v2
x1 | y1 | z2 | u1 | v2
x1 | y1 | z2 | u2 | v2
....
x2 | y2 | z10 | u10 | v10

Now try a slightly more complicated bucket.

MYB2:

[
  {
    "k": {
      "c": [
        {
          "h": 3,
          "g": 5,
          "f": 6
        },
        {
          "h": 19,
          "g": 20,
          "f": 14
        },
        {
          "h": 13,
          "g": 1,
          "f": 2
        }
      ],
      "b": [
        {
          "e": 5,
          "d": 7
        },
        {
          "e": 0,
          "d": 2
        }
      ],
      "a": 8
    }
  },
  {
    "k": {
      "c": [
        {
          "h": 3,
          "g": 2,
          "f": 10
        },
        {
          "h": 16,
          "g": 10,
          "f": 0
        },
        {
          "h": 9,
          "g": 17,
          "f": 17
        }
      ],
      "b": [
        {
          "e": 12,
          "d": 8
        },
        {
          "e": 17,
          "d": 13
        }
      ],
      "a": 17
    }
  },
  {
    "k": {
      "c": [
        {
          "h": 10,
          "g": 20,
          "f": 5
        },
        {
          "h": 13,
          "g": 17,
          "f": 6
        },
        {
          "h": 9,
          "g": 16,
          "f": 6
        }
      ],
      "b": [
        {
          "e": 8,
          "d": 3
        },
        {
          "e": 2,
          "d": 5
        }
      ],
      "a": 12
    }
  }
]

Queries:

--Return b.3 and c.g where c.f and b.d are the same
--Similar to the mountain and country problem on homework (5-7)

SELECT b.e, c.g
FROM my_bucket2.k x UNNEST x.b b UNNEST x.c c
WHERE c.f = b.d;

--Difference between ARRAY_LENGTH and COUNT

SELECT x.a, ARRAY_LENGTH(x.c) as cnt
FROM my_bucket2.k x;

SELECT x.a, COUNT(y) as cnt
FROM my_bucket2.k x UNNEST x.c y
GROUP BY x;