Complex query question

SQL++
1

I am learning N1QL and have a question about a data structure/query. Given the following document how would I form a query that returned only the data element “fs” within the nested element that is keyed by “12”? i.e.
“fs”: “0.885379982E+01”,

It seems like a simple thing to do but I keep trying different things that aren’t working.
thanks

{
	"id": "DD::V01::SAL1L2::GFS::G2/NHX::HGT::GFS::P1000::2018010100",
	"type": "DataDocument",
	"dataType": "V01_type1",
	"subset": "mv",
	"dataFile_id": "DF_id",
	"datasource_id": "DS_id",
	"version": "V01",
	"model": "ford",
	"geoLocation_id": "123",
	"data": {
		"12": {
			"fl": "12",
			"total": "3600.",
			"fs": "0.885379982E+01",
			"o1": "0.815554852E+01",
			"m1": "0.473878674E+04",
			"ff": "0.472373419E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		"18": {
			"fl": "18",
			"total": "3600.",
			"f5": "0.920275537E+01",
			"o1": "0.815554852E+01",
			"m1": "0.476554197E+04",
			"ff": "0.478906976E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		"24": {
			"fl": "24",
			"total": "3600.",
			"f5": "0.924388284E+01",
			"o1": "0.815554852E+01",
			"m1": "0.477780374E+04",
			"ff": "0.482988206E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		"30": {
			"fl": "30",
			"total": "3600.",
			"f5": "0.969193197E+01",
			"o1": "0.815554852E+01",
			"m1": "0.479433046E+04",
			"ff": "0.488857497E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		}
	}
}
2 
SELECT b.data.`12`.fs
FROM  bucket1 AS b

Each nested object reference by dot , if the field as special character (in this case all numbers) escape with back-ticks (https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/identifiers.html)

Useful info

https://query-tutorial.couchbase.com/tutorial
https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/
https://blog.couchbase.com/category/n1ql-query/
https://index-advisor.couchbase.com/indexadvisor

3

Thank you! I didn’t realize all numbers are treated as special chars.

4

Filed name/identifier must start with alphabetic. In above case start with alpha numeric.

5

Thank you, this is great!
SELECT b.data.12.fs FROM bucket1 AS b

I wonder, is there a way to make the 12 sort of like a wildcard such that it would return anything that matches a pattern, like b.data.12 AND b.data.18 etc. ?
I realize that I could do …
SELECT b.data.12.fs as fa, b.data.18.fs as fb FROM bucket1 AS b
but what if I do not know what the 12 and 18 keys are? I am not thinking “like” works on keys. Perhaps there is a way using meta?
Thanks again,
(I’ve already started the suggested books, thanks!)

6

I am not sure about your question.

This gives ARRAY of values when fl is “12” or “18” https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html

SELECT  ARRAY {v.fl, v.fs} FOR v IN OBJECT_VALUES(b.data) WHEN v.fl IN ["12", "18"] END
FROM bucket1 AS b;

SELECT bu.val.*
FROM bucket1 AS b
UNNEST OBJECT_PAIRS(b.data) AS bu
WHERE bu.name LIKE "1%";

returns all objects f5 IS present

SELECT bu.val.*
FROM bucket1 AS b
UNNEST OBJECT_PAIRS(b.data) AS bu
WHERE bu.val.f5  IS NOT MISSING

Even you can index and query
If you search forum OBJECT_NAMES(), OBJECT_PAIRS(), OBJECT_VALUES() you will find many examples.

These converts dynamic fields, values, both into ARRAY and then you can use ARRAY indexing

7

Thank you very much. That was exactly what I needed. I was struggling with the ARRAY syntax. It’s getting much clearer!

8

ARRAY, FIRST, and OBJECT

Range transforms (ARRAY, FIRST, OBJECT) allow you to map and filter the elements or attributes of a collection or object(s). ARRAY evaluates to an array of the operand expression, while FIRST evaluates to a single element based on the operand expression. OBJECT evaluates to an object whose name : value attributes are name-expr : expr .

Name-expr must evaluate to a string. If not, that attribute is omitted from the result object.

range-xform:

9

Thanks, wonderful. Nice blog entry!

10

I have a choice of loading this data in two ways. First with the data portion nested objects, and second with the data portion an array of objects. Could someone please advise me if there are advantages or disadvantages to either way considering ease of querying and performance? There will potentially be hundreds of elements in the data section and they will most likely be ordered by the “fl” field. Here are the two options…
first data as nested objects…

{
	"id": "DD::V01::SAL1L2::GFS::G2/NHX::HGT::GFS::P1000::2018010100",
	"type": "DataDocument",
	"dataType": "V01_type1",
	"subset": "mv",
	"dataFile_id": "DF_id",
	"datasource_id": "DS_id",
	"version": "V01",
	"model": "ford",
	"geoLocation_id": "123",
	"data": {
		"12": {
			"fl": "12",
			"total": "3600.",
			"fs": "0.885379982E+01",
			"o1": "0.815554852E+01",
			"m1": "0.473878674E+04",
			"ff": "0.472373419E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		"18": {
			"fl": "18",
			"total": "3600.",
			"f5": "0.920275537E+01",
			"o1": "0.815554852E+01",
			"m1": "0.476554197E+04",
			"ff": "0.478906976E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		"24": {
			"fl": "24",
			"total": "3600.",
			"f5": "0.924388284E+01",
			"o1": "0.815554852E+01",
			"m1": "0.477780374E+04",
			"ff": "0.482988206E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		"30": {
			"fl": "30",
			"total": "3600.",
			"f5": "0.969193197E+01",
			"o1": "0.815554852E+01",
			"m1": "0.479433046E+04",
			"ff": "0.488857497E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		}
	}
}

second data as an array …

{
	"id": "DD::V01::SAL1L2::GFS::G2/NHX::HGT::GFS::P1000::2018010100",
	"type": "DataDocument",
	"dataType": "V01_type1",
	"subset": "mv",
	"dataFile_id": "DF_id",
	"datasource_id": "DS_id",
	"version": "V01",
	"model": "ford",
	"geoLocation_id": "123",
	"data": [
		 {
			"fl": "12",
			"total": "3600.",
			"fs": "0.885379982E+01",
			"o1": "0.815554852E+01",
			"m1": "0.473878674E+04",
			"ff": "0.472373419E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		 {
			"fl": "18",
			"total": "3600.",
			"f5": "0.920275537E+01",
			"o1": "0.815554852E+01",
			"m1": "0.476554197E+04",
			"ff": "0.478906976E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		 {
			"fl": "24",
			"total": "3600.",
			"f5": "0.924388284E+01",
			"o1": "0.815554852E+01",
			"m1": "0.477780374E+04",
			"ff": "0.482988206E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		},
		 {
			"fl": "30",
			"total": "3600.",
			"f5": "0.969193197E+01",
			"o1": "0.815554852E+01",
			"m1": "0.479433046E+04",
			"ff": "0.488857497E+04",
			"fa": "0.477779363E+04",
			"oo1": null
		}
	]
}