How do I select all documents containing a certain pattern in the fields?

user_15981852 0
Viewed 42

If I have a set of documents of the type:

{
    "contactId": "V1234",
    "accessMap": {
        "130134:24": ["RG1234", "RG456"],
        "12345:45": [".....", "....", ..],
        ...
    },
    "documentType"="CONTACT_ACCESS_MAP"
}

How can I write a query to select all meta().id of the documents where a certain value, in this case 130134:24 exists in the access map?

I'm thinking something along the lines of:

SELECT n for n in OBJECT_NAMES(accessMap) from access-services where n like "130134:24%" and documentType="CONTACT_ACCESS_MAP

n1ql couchbase
1 Answers

I'm going to assume your JSON document is actually valid JSON like below:

{
  "contactId": "V1234",
  "accessMap": {
    "130134:24": [
      "RG1234",
      "RG456"
    ],
    "12345:45": [
      ".....",
      "....",
      ".."
    ]
  },
  "documentType": "CONTACT_ACCESS_MAP"
}

You're on the right track with OBJECT_NAMES. Use ANY n IN expression SATISFIES condition END syntax in the WHERE clause, like so:

SELECT META(s).id
FROM accessservices s
WHERE ANY n IN OBJECT_NAMES(accessMap) SATISFIES n LIKE "130134:%" END;
user_400150
Related