Count objects in array where key value match

SQL++
1

HI I have document like:

  "name": "Test",
  "items": [{
    "id": "1427",
    "sellers": 12,
    "active": "yes",
  }, {
    "id": "132",
    "sellers": 12,
    "active": "no",
  }, {
    "id": "1711",
    "sellers": 12,
    "active": "yes",
  }],

lets say I want to select all documents like this from some namespace

    SELECT * FROM bucket_name WHERE `_ns` = "items"

but also I would like to return additional counter which will reflect how many items are active something like this:

SELECT *, COUNT(SELECT * FROM ANY i IN items WHERE i.active = "yes") as counter  FROM bucket_name WHERE `_ns` = "items"

but I can’t find solution can someone help me thanks

2

FOR each document how many active items are there.

SELECT *,  
      ARRAY_COUNT(ARRAY true FOR v IN d.items WHEN v.active = "yes" END)  AS itemscount 
FROM bucket_name AS d WHERE  d.`_ns` = "items";

You can do subquery Expression on ARRAY as follows. This allows you can do full query syntax and source of input from parent expression.

SELECT *,  
      (SELECT RAW COUNT(1) FROM d.items AS item WHERE item.active = "yes")[0] AS itemscount
FROM bucket_name AS d WHERE  d.`_ns` = "items";
3 Likes