N1QL - object manipulation

SQL++
1

dear all

I’m using the dataset of the interactive tutorial at http://query.pub.couchbase.com/tutorial

I would like to find a way with N1QL to remove from an object all key value attributes that do not satisfy a given condition.
for instance, let us consider this doc

{
“tutorial”: {
“age”: 46,
“children”: [
{
“age”: 17,
“fname”: “Aiden”,
“gender”: “m”
},
{
“age”: 2,
“fname”: “Bill”,
“gender”: “f”
}
],
“email”: "dave@gmail.com",
“fname”: “Dave”,
“hobbies”: [
“golf”,
“surfing”
],
“lname”: “Smith”,
“relation”: “friend”,
“title”: “Mr.”,
“type”: “contact”
}
}

i would like to prune out from tutorial all fields whose value is set to “Dave”

i’m expecting to get :

{
“tutorial”: {
“age”: 46,
“children”: [
{
“age”: 17,
“fname”: “Aiden”,
“gender”: “m”
},
{
“age”: 2,
“fname”: “Bill”,
“gender”: “f”
}
],
“email”: "dave@gmail.com",
“hobbies”: [
“golf”,
“surfing”
],
“lname”: “Smith”,
“relation”: “friend”,
“title”: “Mr.”,
“type”: “contact”
}
}

which is an object from which the pair “fname”: “Dave” has been removed.

I have found that using the operator ARRAY - FOR - IN allows me to derive all attributes of document tutorial that satisfy the condition
in the form of name-value pairs.

Executing the query

SELECT
array f FOR f in OBJECT_pairs(tutorial) when f.value<>“Dave” END as f1
FROM tutorial

I get the document:

{
“f1”: [
{
“name”: “age”,
“value”: 46
},
{
“name”: “children”,
“value”: [
{
“age”: 17,
“fname”: “Aiden”,
“gender”: “m”
},
{
“age”: 2,
“fname”: “Bill”,
“gender”: “f”
}
]
},
{
“name”: “email”,
“value”: "dave@gmail.com"
},
{
“name”: “hobbies”,
“value”: [
“golf”,
“surfing”
]
},
{
“name”: “lname”,
“value”: “Smith”
},
{
“name”: “relation”,
“value”: “friend”
},
{
“name”: “title”,
“value”: “Mr.”
},
{
“name”: “type”,
“value”: “contact”
}
]
},

but my goal is returning an object like this:

{
“tutorial”: {
“age”: 46,
“children”: [
{
“age”: 17,
“fname”: “Aiden”,
“gender”: “m”
},
{
“age”: 2,
“fname”: “Bill”,
“gender”: “f”
}
],
“email”: "dave@gmail.com",
“hobbies”: [
“golf”,
“surfing”
],
“lname”: “Smith”,
“relation”: “friend”,
“title”: “Mr.”,
“type”: “contact”
}
}

is this possible?

I have seen that https://github.com/couchbaselabs/query/blob/master/docs/n1ql-select.md
describes an operator : object name-expression : “expression for var in collection …” and i guess that such an operator could introduce the functionality that I need. I have tried it but it seems it is not supported. Is there any plan to support this or similar object manipulation operators in future versions of Couchbase / N1QL ? or do you know any way to handle this type of query with N1QL with current operators?

thank you,

Pietro

2

Pietro,
in Watson we have added new OBJECT_xxx functions.
In particular what would be helpful to you is OBJECT_REMOVE, which takes an object and a name, and returns an object with that name / value pair removed.

If you get yourself the 4.5 developer preview, you can try it out.

HTH,
Marco

3

Thank you for the answer, I’ll try Couchbase 4.5 and OBJECT_REMOVE

best regards

Pietro

4

I’m trying to use OBJECT_REMOVE and I’ve found that it allows removing a single name-value pair,
whereas I should remove multiple fields satisfying a given condition.

for instance, let us consider the beer-sample dataset provided as benchmark with Couchbase 4.5
Let’s suppose that my filtering condition consists in removing from each beer-sample document all the numeric fields.

I derive the list of numeric fields of a document with this query:

select array fields for fields in object_pairs(beer-sample) when isnumber(fields.value) end as to_remove
from beer-sample

but I don’t know how to remove all the returned fields from the same document using a unique query

I’ve tried this:

select object_remove(beer-sample, to_remove.name) as beer-sample, to_remove.name as removed
from beer-sample unnest (array fields for fields in object_pairs(beer-sample) when isnumber(fields.value) end) as to_remove

but it creates multiple copies of the document removing a single element of the list to_remove from each copy, in contrast, I would need to remove all elements in the list from the document.

for instance,
let’s consider the following document of the beer-sample dataset:

{
“beer-sample”: {
“abv”: 7.2,
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep golden color. Citrus and piney hop aromas. Assertive malt backbone supporting the overwhelming bitterness. Dry hopped in the fermenter with four types of hops giving an explosive hop aroma. Many refer to this IPA as Nectar of the Gods. Judge for yourself. Now Available in Cans!”,
“ibu”: 0,
“name”: “21A IPA”,
“srm”: 0,
“style”: “American-Style India Pale Ale”,
“type”: “beer”,
“upc”: 0,
“updated”: “2010-07-22 20:00:20”
}
}

by executing the query

select object_remove(beer-sample, to_remove.name) as beer-sample, to_remove.name as removed
from beer-sample unnest (array fields for fields in object_pairs(beer-sample) when isnumber(fields.value) end) as to_remove

i get these documents:

{
“beer-sample”: {
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep golden color. Citrus and piney hop aromas. Assertive malt backbone supporting the overwhelming bitterness. Dry hopped in the fermenter with four types of hops giving an explosive hop aroma. Many refer to this IPA as Nectar of the Gods. Judge for yourself. Now Available in Cans!”,
“ibu”: 0,
“name”: “21A IPA”,
“srm”: 0,
“style”: “American-Style India Pale Ale”,
“type”: “beer”,
“upc”: 0,
“updated”: “2010-07-22 20:00:20”
},
“removed”: “abv”
},
{
“beer-sample”: {
“abv”: 7.2,
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep golden color. Citrus and piney hop aromas. Assertive malt backbone supporting the overwhelming bitterness. Dry hopped in the fermenter with four types of hops giving an explosive hop aroma. Many refer to this IPA as Nectar of the Gods. Judge for yourself. Now Available in Cans!”,
“name”: “21A IPA”,
“srm”: 0,
“style”: “American-Style India Pale Ale”,
“type”: “beer”,
“upc”: 0,
“updated”: “2010-07-22 20:00:20”
},
“removed”: “ibu”
},
{
“beer-sample”: {
“abv”: 7.2,
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep golden color. Citrus and piney hop aromas. Assertive malt backbone supporting the overwhelming bitterness. Dry hopped in the fermenter with four types of hops giving an explosive hop aroma. Many refer to this IPA as Nectar of the Gods. Judge for yourself. Now Available in Cans!”,
“ibu”: 0,
“name”: “21A IPA”,
“style”: “American-Style India Pale Ale”,
“type”: “beer”,
“upc”: 0,
“updated”: “2010-07-22 20:00:20”
},
“removed”: “srm”
},
{
“beer-sample”: {
“abv”: 7.2,
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep golden color. Citrus and piney hop aromas. Assertive malt backbone supporting the overwhelming bitterness. Dry hopped in the fermenter with four types of hops giving an explosive hop aroma. Many refer to this IPA as Nectar of the Gods. Judge for yourself. Now Available in Cans!”,
“ibu”: 0,
“name”: “21A IPA”,
“srm”: 0,
“style”: “American-Style India Pale Ale”,
“type”: “beer”,
“updated”: “2010-07-22 20:00:20”
},
“removed”: “upc”
}

in contrast, I would like to get the following document, from which all the numeric fields have been removed:

{
“beer-sample”: {
“brewery_id”: “21st_amendment_brewery_cafe”,
“category”: “North American Ale”,
“description”: “Deep golden color. Citrus and piney hop aromas. Assertive malt backbone supporting the overwhelming bitterness. Dry hopped in the fermenter with four types of hops giving an explosive hop aroma. Many refer to this IPA as Nectar of the Gods. Judge for yourself. Now Available in Cans!”,
“name”: “21A IPA”,
“style”: “American-Style India Pale Ale”,
“type”: “beer”,
“updated”: “2010-07-22 20:00:20”
}
}

I’m wondering how could I get this with a N1QL query… could you please help me with this?
thank you

Pietro

5

Hi @pietro.colombo,

We will implement the syntax you saw in the spec. How urgent is this for you?

6

Hi,
Do you mean the syntax Object name-exp: exp for var in exp that i was mentioning in my first post?
It’s not very urgent, but I would need it, do you know how long could it take to develop this feature?
thanks

Pietro

7

Yes, that syntax. It’s a question of when to prioritize it. We have releases every few months.

8

oh great! that operator should solve my issue.
I hope this could be included in the next version.

9

Will do. Please track https://issues.couchbase.com/browse/MB-18581