How to check if sub documents contains value using an unknown path

SQL++
1

I guess it’s a simple question but somehow I couldn’t figure out how to do it.

Let’s say we have many different shops and each shop has its users. Each user has a document that holds the user orders activity. For a given shop id, I need to get a list of user ids which at list one of its orders in pending status.

The question is how can I query the orders sub documents when each one of its elements has an unknown key(the guid which represent the order id)?

I’m working with golang.

Here as a document example:

{
      "shopId": "1000",
      "userId": 1,
      "orders": {
        "b2864cbd-bb21-459f-89c6-bddacd064c0d": {
          "total": 26,
          "items": {
            "itemId": "1",
            "Name": "Bluetooth Headset"
          },
          "status": "delivered"
        },
        "cb43f97d-934f-438f-9a2e-a7b1e6e3b0ee": {
          "createAt": 121212121,
          "total": 43,
         "items": {
            "itemId": "2",
            "Name": "Single Wireless Earbud,"
          },
          "status": "pending"
        }
      },
      "type": "order_tracking"
    }
1 Like
2

Convert the Object that has dynamic field-names into ARRAY of value objects using Object functions and use ANY syntax iterate over it.

SELECT t.userId 
FROM default AS t
WHERE t.type = "order_tracking" AND t.shopId = "1000" AND 
                ANY o IN OBJECT_VALUES(t.orders) SATISFIES o.status = "pending" END;

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/objectfun.html

1 Like
3

Thanks, it was easy as I though :slight_smile: