Query syntax for arrays

SQL++
1

Hello,
I am new to Couchbase N1QL and querying an array doesn’t work like I expect. The example document:
{
“type”: “product”,
“sku”: “B2B_SUBSCRIPTION”,
“items”: [
{
“status”: “Active”,
“name”: “B2B Services v1”,
“OrderItem__type”: “SUBSCRIPTION”,
“test”: 1
},
{
“status”: “Active”,
“name”: “B2B Unlimited v1”,
“OrderItem__type”: “UNLIMITED”,
“test”: 2
}
]
}

I want only the item that matches the WHERE condition, but all three of these queries return both items. Can somebody explain what I am doing wrong? (I tried with both == and = .)

SELECT t.items FROM test t UNNEST t.items p WHERE p.OrderItem__type==“SUBSCRIPTION”

SELECT t.items
FROM test t
WHERE ANY item IN t.items SATISFIES
item.OrderItem__type == "SUBSCRIPTION"
END

SELECT t.items
FROM test t UNNEST t.items i
WHERE i.OrderItem__type != “SUBSCRIPTION”

2

for example, do you want to get the data

[
  {
    "OrderItem__type": "SUBSCRIPTION",
    "name": "B2B Services v1",
    "status": "Active",
    "test": 1
  }
]

by

p.OrderItem__type=="SUBSCRIPTION"

and get data

[
  {
    "OrderItem__type": "UNLIMITED",
    "name": "B2B Unlimited v1",
    "status": "Active",
    "test": 2
  }
]

by

WHERE i.OrderItem__type != "SUBSCRIPTION"

?
If this, try this N1QL

SELECT  p.*
FROM test t UNNEST t.items p WHERE p.OrderItem__type =="SUBSCRIPTION"

And

SELECT  p.*
FROM test t UNNEST t.items p WHERE p.OrderItem__type !="SUBSCRIPTION"
1 Like
3

Yes. That is what I was looking for. Thank you.

4

I wrote a little something on the Couchbase blog based on this forum inquiry in case you needed an added explanation:

https://blog.couchbase.com/flattening-querying-nosql-array-data-couchbase-n1ql/

Best,

2 Likes