OBJECT_VALUES question

n1ql

#1

I am using Couchbase Server 4.1.0-5005 Enterprise Edition (build-5005)
My doc looks like the following:

{"name":"name1","relationship":[{"id-1":"value-1"},{"id-2":"value-2"},{"id-3":"value-3"}]}

the result that I want to query is:

"results": [
{"name":"name1","ids":["id-1","id-2","id-3"],"values":["value-1","value-2",'value-3']}
]

I can get ids by using

OBJECT_NAMES(relationship[*])

but I can not get values,
when I using

OBJECT_VALUES(relationship[*])

I got the following data:

"$1":[
[value-1,
 null,
 null
],
[null,
 value-2,
 null
],
[null,
 null,
 value-3
],
]

How should I correct the query?


#2

Your previous structure, where each object had 2 fields, one for id and another for value, is probably better. I understand the optimization here, but it will lead to less flexibility later. Anyway, you can do:

ARRAY TO_ATOM(x) FOR x IN relationship END


#3

It works! Thank you very mech.


#4

How about If I want to query data:

"results": [
{"name":"name1","values":["value-1","value-2"]}
]

by

key = ["id-1","id-2"]

or query data

"results": [
{"name":"name1","values":["value-2"]}
]

by

key = ["id-2"]

or

"results": [
{"name":"name1","values":["value-1","value-2","value-3"]}
]

by

key = ["id-1","id-2","id-3"]

#5

Hi @atom_yang,

You can use a WHERE clause in your query of the form:

WHERE key IN $mykeys

and then pass an array of keys into $mykeys.

Or, you can directly write:

WHERE key IN [ "id-1", ... ]

You should try this out first. If the data set is large, then we will need to discuss how to index this query. That will require our next release. Developer Preview of that is coming out shortly.


#6

Thank you, I want to query data

"results": [
{"name":"name1","values":["value-1","value-2"]}
]

by

key = ["id-1","id-2"]

I can use IN to pass array of keys,but I don’t know how to query values by keys.
I am try to use:

SELECT ARRAY (ARRAY TO_ATOM(y) FOR y IN a.relationship[*].x END) FOR x IN ["id-1","id-2"] END FROM mydb a;

but it return empty result.


#7

Please try this:

SELECT mydb.name, ARRAY_AGG ( relation.`value` ) AS `values`
FROM mydb UNNEST mydb.relationship AS relation
WHERE OBJECT_KEYS(relation)[0] IN $mykeys
GROUP BY mydb.name

#8

Thank you, Your N1QL doesn’t work , The following works:

SELECT ARRAY TO_ATOM(X) FOR X IN ARRAY_AGG(relation) END AS `values` FROM mydb a  UNNEST a.relationship AS relation WHERE OBJECT_NAMES(relation)[0] IN $mykeys;

post here,hope this can help other one.