CODEWITHSUNDEEP
couchbasesql++nosql
Ev Conrad
Ev Conrad

Reputation: 333

Very basic N1QL grouping data operation

So I am learning N1QL in order to write some queries to see usage data, and I am trying to look at data that looks like the following:

   "data": {
      "datapoints": [
        {
          "point1": "data 1",
          "point2": "data 2",
          "point3": "data 3"
        },
        {
          "point1": "more data 1",
          "point2": "more data 2",
          "point3": "more data 3"
        }
     ]
    }

If I select like this:

SELECT data.datapoints[].point1, data.datapoints[].point2 FROM datatable

I get the data back grouped by the name of the column, like this:

[
  {
    "point1": [
        "data 1",
        "more data 1"
    ]
  },
  {
    "point2": [
        "data 2",
        "more data 2"
    ]
  },
]

Is it possible to set up the query so that it returns data grouped by object, as opposed to grouped by the data point(s), but just with the fields I want? I would like to look at by document, instead of by data point across documents, just with a subset of the data points in the documents. Thanks in advance!

EDIT: I notice if I set up my query like this:

SELECT data.datapoints[0].point1, data.datapoints[0].point2 FROM datatable

My data is formatted properly, like so:

  "datapoints": [
    {
      "point1": "data 1",
      "point2": "data 2",
    }
   ]

but of course only the first datapoints record is returned.

Upvotes: 0

Views: 45

Answers (1)

vsr
vsr

Reputation: 7414

SELECT ud AS datapoints FROM datatable d UNNEST d.datapoints AS ud;

UNNEST works same as JOIN. UNNEST works on arrays.
datatable d UNNEST d.datapoints AS ud makes Cartesian JOIN of d and each element of d.datapoints. Finally we project ud.

Checkout UNNEST Clause for details.

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/from.html

Upvotes: 1

Related Questions